PHP/HTML Table populated by MySQL only showing one result

Question

I have created a table to be automatically populated by a MySQL table, it only slightly works though. It loads one result, but no more. I need it to display all results in the mysql table.

Below is my Query + Array

  $future = mysql_query("SELECT * FROM Future");
  $future_row = mysql_fetch_array($future);

This is my table Code

<div class="tg-wrap"><table class="tg" style="undefined;table-layout: fixed; width: 1000px;>

            <tr class="header">
                <th class="tg-ls31" colspan="2">Device</th>
                <th class="tg-ls31" colspan="2">Booked By</th>
                <th class="tg-ls31" colspan="2">Date Out</th>
                <th class="tg-ls31" colspan="2">Date Back</th>
            </tr>
            <?php
                {
                   echo "<tr>";
                   echo "<td class='tg-baqh' colspan='2'>".$future_row[Device]."</td>";
                   echo "<td class='tg-baqh' colspan='2'>".$future_row[Name]."</td>";
                   echo "<td class='tg-baqh' colspan='2'>".$future_row[Out]."</td>";
                   echo "<td class='tg-baqh' colspan='2'>".$future_row[In]."</td>";
                   echo "</tr>";
               }

            ?>
        </table></div>

If you could provide a fix so that I can display more than one result in this table, i'd be most appreciative.

EDIT 1:

I have updated the code, removed the first array and created a while loop

<?php while( $future_row = mysql_fetch_array($future))
                {
                   echo "<tr>";
                   echo "<td class='tg-baqh' colspan='2'>".$future_row[Device]."</td>";
                   echo "<td class='tg-baqh' colspan='2'>".$future_row[Name]."</td>";
                   echo "<td class='tg-baqh' colspan='2'>".$future_row[Out]."</td>";
                   echo "<td class='tg-baqh' colspan='2'>".$future_row[In]."</td>";
                   echo "</tr>";
               }

            ?>

I have also had a request for an image of the table, which I have provided below

Image of the MySQL DB Table

EDIT 2:

I have been requested to provide the full page code, which i will provide below.

    <head>
<?php
$hostname = "localhost";
$username = "root";
$password = "gunner98";

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password)
  or die("Unable to connect to MySQL");

//select a database to work with
$selected = mysql_select_db("laptop_pool",$dbhandle)
  or die("Could not select database");

//execute the SQL query and return records
$P1 = mysql_query("SELECT * FROM P1");
$P1_row = mysql_fetch_array($P1);

$P2 = mysql_query("SELECT * FROM P2");
$P2_row = mysql_fetch_array($P2);

$P3 = mysql_query("SELECT * FROM P3");
$P3_row = mysql_fetch_array($P3);

$P4 = mysql_query("SELECT * FROM P4");
$P4_row = mysql_fetch_array($P4);

$P5 = mysql_query("SELECT * FROM P5");
$P5_row = mysql_fetch_array($P5);

$P6 = mysql_query("SELECT * FROM P6");
$P6_row = mysql_fetch_array($P6);

$P7 = mysql_query("SELECT * FROM P7");
$P7_row = mysql_fetch_array($P7);

$future = mysql_query("SELECT * FROM Future");
$future_row4 = mysql_fetch_array($future);


//close the connection
mysql_close($dbhandle);
?>

<title>&nbsp;&nbsp;Moat Laptop Pool Table&nbsp;&nbsp;</title>

</head>

<body style="background-height: 100%;background-width: 100%;background: #141E30;background: -webkit-linear-gradient(to left, #141E30 , #243B55);background: linear-gradient(to left, #141E30 , #243B55);">

<div id="top" style="position: fixed;top: 0;left: 0;z-index: 999;width: 100%;height: 25px;">

<div id="alert_box" style="height: 100%; width: 100%; color: white; background-color: rgba(0, 0, 0, 0.5);">

<div id="Site_Alert" style="font-weight: bold; display: inline;width: 5%;float: left;">
&nbsp;Site Alert:
</div>

<div id="scroll_alert" style="display: inline; float: right; width:95%;">
<marquee behavior=scroll direction="left" scrollamount="8" onmouseover="this.stop();" onmouseout="this.start();">This is in development!</marquee>
</div>

</div>

<div id="logo" style="font-family: Tw Cen MT; font-weight: Bold; position: fixed; color: white; left: 650px;top: 35px; font-size: 80px;text-shadow: 3px 3px #c7c7c7;">
Moat Laptop Pool Table
</div>

<div id="content_box" style="position: fixed; left: 450px;top: 135px; width:60%; height: 70%; border-radius: 3px;

">

<style type="text/css">
.tg  {border-collapse:collapse;border-spacing:0;border-color:#ccc;margin:0px auto;}
.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#ccc;color:#333;background-color:#fff;}
.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#ccc;color:#333;background-color:#f0f0f0;}
.tg .tg-baqh{text-align:center;vertical-align:top}
.tg .tg-ls31{font-weight:bold;background-color:#333333;color:#f0f0f0;text-align:center;vertical-align:top}
th.tg-sort-header::-moz-selection { background:transparent; }th.tg-sort-header::selection      { background:transparent; }th.tg-sort-header { cursor:pointer; }table th.tg-sort-header:after {  content:'';  float:right;  margin-top:7px;  border-width:0 4px 4px;  border-style:solid;  border-color:#404040 transparent;  visibility:hidden;  }table th.tg-sort-header:hover:after {  visibility:visible;  }table th.tg-sort-desc:after,table th.tg-sort-asc:after,table th.tg-sort-asc:hover:after {  visibility:visible;  opacity:0.4;  }table th.tg-sort-desc:after {  border-bottom:none;  border-width:4px 4px 0;  }@media screen and (max-width: 767px) {.tg {width: auto !important;}.tg col {width: auto !important;}.tg-wrap {overflow-x: auto;-webkit-overflow-scrolling: touch;margin: auto 0px;}}</style>
<div class="tg-wrap"><table id="tg-4b5Id" class="tg" style="undefined;table-layout: fixed; width: 1000px; ">
<colgroup>
<col style="width: 29px">
<col style="width: 51px">
<col style="width: 35px">
<col style="width: 53px">
<col style="width: 28px">
<col style="width: 77px">
<col style="width: 58px">
<col style="width: 94px">
<col style="width: 44px">
<col style="width: 133px">
<col style="width: 37px">
<col style="width: 91px">
</colgroup>
  <tr>
    <th class="tg-ls31" colspan="2">Device</th>
    <th class="tg-ls31" colspan="2">Out or In</th>
    <th class="tg-ls31" colspan="2">Out on</th>
    <th class="tg-ls31" colspan="2">In Posession Of</th>
    <th class="tg-ls31" colspan="2">Department</th>
    <th class="tg-ls31" colspan="2">Due Back</th>
  </tr>
  <tr>
    <td class="tg-baqh" colspan="2">P1</td>
    <td class="tg-baqh" colspan="2"><?php
echo $P1_row['status'];
?> </td>
    <td class="tg-baqh" colspan="2"><?php
echo $P1_row['left'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P1_row['posession'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P1_row['department'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P1_row['returns'];
?></td>
  </tr>
  <tr>
    <td class="tg-baqh" colspan="2">P2</td>
    <td class="tg-baqh" colspan="2"><?php
echo $P2_row['status'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P2_row['left'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P2_row['posession'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P2_row['department'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P2_row['returns'];
?></td>
  </tr>
  <tr>
    <td class="tg-baqh" colspan="2">P3</td>
    <td class="tg-baqh" colspan="2"><?php
echo $P3_row['status'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P3_row['left'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P3_row['posession'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P3_row['department'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P3_row['returns'];
?></td>
  </tr>
  <tr>
    <td class="tg-baqh" colspan="2">P4</td>
    <td class="tg-baqh" colspan="2"><?php
echo $P4_row['status'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P4_row['left'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P4_row['posession'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P4_row['department'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P4_row['returns'];
?></td>
  </tr>
  <tr>
    <td class="tg-baqh" colspan="2">P5</td>
    <td class="tg-baqh" colspan="2"><?php
echo $P5_row['status'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P5_row['left'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P5_row['posession'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P5_row['department'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P5_row['returns'];
?></td>
  </tr>
  <tr>
    <td class="tg-baqh" colspan="2">P6</td>
    <td class="tg-baqh" colspan="2"><?php
echo $P6_row['status'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P6_row['left'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P6_row['posession'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P6_row['department'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P6_row['returns'];
?></td>
  </tr>
  <tr>
    <td class="tg-baqh" colspan="2">P7</td>
    <td class="tg-baqh" colspan="2"><?php
echo $P7_row['status'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P7_row['left'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P7_row['posession'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P7_row['department'];
?></td>
    <td class="tg-baqh" colspan="2"><?php
echo $P7_row['returns'];
?></td>
  </tr>
</table></div>
<div id="future" style="font-family: Tw Cen MT; font-weight: Bold; position: fixed; color: white; left: 750px; font-size: 80px;text-shadow: 3px 3px #c7c7c7;">
Future Bookings
</div>
<br />
<br />
<br />
<br />
<br />
<div class="tg-wrap"><table class="tg" style="undefined;table-layout: fixed; width: 1000px;>

            <tr class="header">
                <th class="tg-ls31" colspan="2">Device</th>
                <th class="tg-ls31" colspan="2">Booked By</th>
                <th class="tg-ls31" colspan="2">Date Out</th>
                <th class="tg-ls31" colspan="2">Date Back</th>
            </tr>
            <?php while( $future_row = mysql_fetch_array($future))
                {
                   echo "<tr>";
                   echo "<td class='tg-baqh' colspan='2'>".$future_row[Device]."</td>";
                   echo "<td class='tg-baqh' colspan='2'>".$future_row[Name]."</td>";
                   echo "<td class='tg-baqh' colspan='2'>".$future_row[Out]."</td>";
                   echo "<td class='tg-baqh' colspan='2'>".$future_row[In]."</td>";
                   echo "</tr>";
               }

            ?>
        </table></div>
<script type="text/javascript" charset="utf-8">var TgTableSort=window.TgTableSort||function(n,t){"use strict";function r(n,t){for(var e=[],o=n.childNodes,i=0;i<o.length;++i){var u=o[i];if("."==t.substring(0,1)){var a=t.substring(1);f(u,a)&&e.push(u)}else u.nodeName.toLowerCase()==t&&e.push(u);var c=r(u,t);e=e.concat(c)}return e}function e(n,t){var e=[],o=r(n,"tr");return o.forEach(function(n){var o=r(n,"td");t>=0&&t<o.length&&e.push(o[t])}),e}function o(n){return n.textContent||n.innerText||""}function i(n){return n.innerHTML||""}function u(n,t){var r=e(n,t);return r.map(o)}function a(n,t){var r=e(n,t);return r.map(i)}function c(n){var t=n.className||"";return t.match(/\S+/g)||[]}function f(n,t){return-1!=c(n).indexOf(t)}function s(n,t){f(n,t)||(n.className+=" "+t)}function d(n,t){if(f(n,t)){var r=c(n),e=r.indexOf(t);r.splice(e,1),n.className=r.join(" ")}}function v(n){d(n,L),d(n,E)}function l(n,t,e){r(n,"."+E).map(v),r(n,"."+L).map(v),e==T?s(t,E):s(t,L)}function g(n){return function(t,r){var e=n*t.str.localeCompare(r.str);return 0==e&&(e=t.index-r.index),e}}function h(n){return function(t,r){var e=+t.str,o=+r.str;return e==o?t.index-r.index:n*(e-o)}}function m(n,t,r){var e=u(n,t),o=e.map(function(n,t){return{str:n,index:t}}),i=e&&-1==e.map(isNaN).indexOf(!0),a=i?h(r):g(r);return o.sort(a),o.map(function(n){return n.index})}function p(n,t,r,o){for(var i=f(o,E)?N:T,u=m(n,r,i),c=0;t>c;++c){var s=e(n,c),d=a(n,c);s.forEach(function(n,t){n.innerHTML=d[u[t]]})}l(n,o,i)}function x(n,t){var r=t.length;t.forEach(function(t,e){t.addEventListener("click",function(){p(n,r,e,t)}),s(t,"tg-sort-header")})}var T=1,N=-1,E="tg-sort-asc",L="tg-sort-desc";return function(t){var e=n.getElementById(t),o=r(e,"tr"),i=o.length>0?r(o[0],"td"):[];0==i.length&&(i=r(o[0],"th"));for(var u=1;u<o.length;++u){var a=r(o[u],"td");if(a.length!=i.length)return}x(e,i)}}(document);document.addEventListener("DOMContentLoaded",function(n){TgTableSort("tg-4b5Id")});</script>
</div>




</body>

Not the cleanest of code at the moment, but I want to get it functional before I clean it up.

@JayBlanchard No, as i referenced it somewhere else, i added the 4 to make sure it didn't conflict, it was to create that loop. However, I just added another entry to the table, which showed up. Seemingly, the first row isn't being picked up in the table. All other rows are, to fix this, i've just added a blank row, and it all seems to now be working. — James Timms, Mar 15 '17 at 12:59
I don't understand, because you're not using `$future_row4` to diplay the table and I do not see it referenced anywhere else in the code. Did you go back and quote the identifiers? — Jay Blanchard, Mar 15 '17 at 13:01
@JayBlanchard I was using it to display the table, so added the 4 to avoid conflict with the loop, that was the only time used. What do you mean? — James Timms, Mar 15 '17 at 13:07
I edited my answer. Comment out `$future_row4 = mysql_fetch_array($future);` It is eating the first row of the results. — Jay Blanchard, Mar 15 '17 at 13:11
@JayBlanchard Oh that fixed that, Cheers, this is all sorted now. A quick question, i know there is a way as i've used it on one of my older projects but i've forgotten what is was. How would i remove an option from an input select (dropdown), automatically if it is already booked into the MySQL db? — James Timms, Mar 15 '17 at 13:23
To do what you want requires JavaScript - http://stackoverflow.com/q/4571602/1011527 — Jay Blanchard, Mar 15 '17 at 15:17

score 1 · Accepted Answer · edited May 23 '17 at 11:46

1

First, some warnings:

Please stop using mysql_* functions. These extensions have been removed in PHP 7. Learn about prepared statements for PDO and MySQLi and consider using PDO, it's really pretty easy.

You have to loop through your results to populate the table:

$future = mysql_query("SELECT * FROM Future");

Now, in the table, you loop (make sure to quote the array identifiers):

<div class="tg-wrap"><table class="tg" style="undefined;table-layout: fixed; width: 1000px;>

            <tr class="header">
                <th class="tg-ls31" colspan="2">Device</th>
                <th class="tg-ls31" colspan="2">Booked By</th>
                <th class="tg-ls31" colspan="2">Date Out</th>
                <th class="tg-ls31" colspan="2">Date Back</th>
            </tr>
            <?php
                while($future_row = mysql_fetch_array($future)) {
                   echo "<tr>";
                   echo "<td class='tg-baqh' colspan='2'>".$future_row['Device']."</td>";
                   echo "<td class='tg-baqh' colspan='2'>".$future_row['Name']."</td>";
                   echo "<td class='tg-baqh' colspan='2'>".$future_row['Out']."</td>";
                   echo "<td class='tg-baqh' colspan='2'>".$future_row['In']."</td>";
                   echo "</tr>";
               }

            ?>
        </table></div>

EDIT -

Now that I see your full code after the edit I see that you are assigning the first row of data here:

$future_row4 = mysql_fetch_array($future);

So when you do the while loop later on:

while($future_row = mysql_fetch_array($future)) {...

you will only get the remaining rows in the table. Remove (or comment out) the first assignment and your table will contain all of the rows.

edited May 23 '17 at 11:46

Community

1
1

answered Mar 15 '17 at 12:00

Jay Blanchard

34,243
16
77
119

Hi there, thanks for the answer. My server is currently running PHP 5 instead of 7, so they should still function efficiently. I have included this loop, and it's only populating one field, I need it to include all of the data inside the mysql table, not just one row. – James Timms Mar 15 '17 at 12:04
Can you add your MySQL table configuration to your original post? – Jay Blanchard Mar 15 '17 at 12:11
and also can u include your updated code in the question – Masivuye Cokile Mar 15 '17 at 12:14
@JayBlanchard Done – James Timms Mar 15 '17 at 12:20
@MasivuyeCokile Done – James Timms Mar 15 '17 at 12:20
Which row is being output @JamesTimms? – Jay Blanchard Mar 15 '17 at 12:22
@JayBlanchard Currently Row 2 - Sally R. – James Timms Mar 15 '17 at 12:24
Is there some other PHP code in your script? Are you showing us everything? Did you go back and quote the array identifiers? Are there any errors in the error logs? – Jay Blanchard Mar 15 '17 at 12:26
what do u get when u `var_dump()` the results? – Masivuye Cokile Mar 15 '17 at 12:32
I think the OP has disappeared @MasivuyeCokile – Jay Blanchard Mar 15 '17 at 12:39
Looks like that @JayBlanchard even on he's question before this he did the same – Masivuye Cokile Mar 15 '17 at 12:41
@JayBlanchard There are two tables on the page so to save confusion I included only thetable that it's regarding and the related array. If you want me to, i can provide the full code on the original question. – James Timms Mar 15 '17 at 12:41
Yes, please do. I just want to make sure one is not interfering with the other. – Jay Blanchard Mar 15 '17 at 12:43
@JayBlanchard I have provided the code for you. It's not the cleanest of code right now, but I always clean after the project is functional – James Timms Mar 15 '17 at 12:51
Finally located the issue @MasivuyeCokile – Jay Blanchard Mar 15 '17 at 13:15

score 0 · Answer 2 · answered Mar 15 '17 at 11:59

0

use while loop .

<?php
         while( $future_row = mysql_fetch_array($future))
                {

answered Mar 15 '17 at 11:59

stalin wesley

165
5
13

I have included the while loop, but it has not fixed the issue. – James Timms Mar 15 '17 at 12:02

PHP/HTML Table populated by MySQL only showing one result

2 Answers2