How do I display data from one mySQL table into multiple tables in php?

Question

I have on mySQL table with data and I want to display it in multiple tables. When I try to do it with the code I have now, the data doesn't show up in the second table. Is my code wrong?

$id=$_GET['id'];
$sql = "SELECT * FROM buildings WHERE room = '{$id}' AND number = '81'";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
echo "<center><h2>Room $id</h2></center> <br> <b>General Information<br>     
<table><tr>
<th>Room Type</th>
<th>Department</th>
<th>College</th>
<th>Primary Owner</th></tr>";

// output data of each row

while($row = mysqli_fetch_assoc($result)) {
echo "<tr>
<td>".$row["type"]."</td>
<td>".$row["Department"]."</td>
<td>".$row["College"]."</td>
<td>".$row["Primary"]."</td>
</tr>";
}
  echo "</table>";
}

//CONTACT INFO//
if (mysqli_num_rows($result) > 0) {
echo "<p><b>Contact Information<br><table><tr>
<th>Reservations</th>
<th>CTL Contact Name</th>
<th>CTL Contact Email</th>  
<th>CTL Contact Phone</th>
<th>Department Contact Name</th>
<th>Department Contact Email</th>
<th>Department Contact Phone</th>
<th>IT Contact Name</th>
<th>IT Contact Email</th>
<th>IT Contact Phone</th>
</tr>";

// output data of each row

while($row = mysqli_fetch_assoc($result)) {
echo "<tr>
<td>".$row["reservations"]."</td>
<td>".$row["contact"]."</td>  
<td>".$row["contactphone"]."</td>
<td>".$row["deptcontact"]."</td>
<td>".$row["deptcontactemail"]."</td>
<td>".$row["deptcontactphone"]."</td>
<td>".$row["itcontact"]."</td>
<td>".$row["itcontactemail"]."</td>
<td>".$row["itcontactphone"]."</td>
</tr>";
}
  echo "</table>";
}
else{
echo("0 results"); 
}

This is what I get: http://i57.tinypic.com/2heijyc.png

Add error reporting to the top of your file(s) right after your opening ` — Jay Blanchard, Apr 28 '15 at 18:08
[Protect against SQL injections](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Devon Bessemer, Apr 28 '15 at 18:08
@salsaverde Please review the answer and mark it as resolved if my answer solved it. — Devon Bessemer, May 05 '15 at 02:08

score 0 · Accepted Answer · answered Apr 28 '15 at 18:12

0

Read the manual of mysqli_fetch_assoc

You've already called while($row = mysqli_fetch_assoc($result))

This will loop through the entire result array and get each row. mysqli_fetch_assoc returns null when there are no more rows. So the next time you run mysqli_fetch_assoc($result) it will be null.

What you need to do is use mysqli_fetch_all at the top to get an array of the result set. Then you can loop through this array as needed.

As I stated in my comment, this query is subject to SQL injections because you use $id which is sourced by $_GET directly in the query. Read up on prepared statements or if it is an integer, use intval($id).

answered Apr 28 '15 at 18:12

Devon Bessemer

34,461
9
69
95

1

Alternatively, you could mysqli_data_seek ( $result , 0 ) to move your pointer back to the first record. – BigScar Apr 28 '15 at 18:24
@BigScar thanks for sharing. I find it easier to just loop through an array, but it all comes down to personal opinion. The OP can continue to use *while* with *mysql_fetch_assoc* by using *mysqli_data_seek*. – Devon Bessemer Apr 28 '15 at 18:31
Just a couple of ways to skin the same cat. – BigScar Apr 28 '15 at 18:33

How do I display data from one mySQL table into multiple tables in php?

1 Answers1