MySQL Error: query not giving correct data

Question

I do a mySQL query to get some data and then (for the purpose of debugging) print it out. In this particular sample there are 5 rows of data and each room_id in the database table has a value. However the print-out only shows the room_id of the first row.

$query_rooms = "SELECT room_id FROM lh_rooms WHERE hid = '$hid'";
$rooms = mysql_query($query_rooms, $MySQL) or die(mysql_error());
$row_rooms = mysql_fetch_assoc($rooms);
$numrows = mysql_num_rows($rooms);
    $i = 0;
while ($i < $numrows) {
    $room_id=$row_rooms['room_id'][$i];
echo $i." - ".$room_id."<br><br>";
   ++$i;
}

0 - 2

1 -

2 -

3 -

4 -

Can someone explain what is happening

Please note that PHP's `mysql_xxx()` functions are obsolete. See also [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) for more info — Simba, Nov 03 '15 at 13:40
Yes Simba, I know that, but that wasn't the question. And changing over will be very time-consuming so it probably won't happen until I absolutely have to. — TrapezeArtist, Nov 03 '15 at 14:42
It wasn't meant as an answer to the question: it was an aside; that's why I posted it as a comment. Glad you know about it, but you should be thinking more seriously about making the change; the `mysql` library has been considered obsolete for over a decade already. It throws formal deprecation notices in the current supported PHP versions, and is being dropped entirely in the next version (PHP7.0, release later this month). More importantly, it many features in recent mySQL versions are unsupported, including secure communications between the DB and the client. Continue using at your own risk — Simba, Nov 03 '15 at 16:01

score 1 · Answer 1 · answered Nov 03 '15 at 13:11

You are fetching multiple rows.

So, you need to loop over the result set instead of fetching just one time.

Corrected code:

$query_rooms = "SELECT room_id FROM lh_rooms WHERE hid = '$hid'";
$rooms = mysql_query($query_rooms, $MySQL) or die(mysql_error());
$i=0;
while($row_rooms =  mysql_fetch_assoc($rooms)) {
    $room_id=$row_rooms['room_id'];
    echo $i." - ".$room_id."<br><br>";
    ++$i;
}

Note: Never use mysql_, they are deprecated and will be removed in the upcoming versions. Use mysqli_ or PDO instead.

score 0 · Accepted Answer · answered Nov 03 '15 at 13:09

0

Try like this

$query_rooms = "SELECT room_id FROM lh_rooms WHERE hid = '$hid'";
$rooms = mysql_query($query_rooms, $MySQL) or die(mysql_error());
 $i = 0;
while ($row_rooms = mysql_fetch_assoc($rooms)) {
$room_id=$row_rooms['room_id'];
 echo $i." - ".$room_id."<br><br>";
$i++;
}

You are looping $i instead of looping the $row_rooms.

answered Nov 03 '15 at 13:09

Arun Krish

2,153
1
10
15

Same answer from Pupil and Arun Krish. Arun gets the green tick for not lecturing me on mysql and mysqli ;-) Actually I was able to simplify the code further because $i was not needed at all. It goes to show how easy it is to lose sight of things when copying and adapting code from another page. – TrapezeArtist Nov 03 '15 at 14:46

MySQL Error: query not giving correct data

2 Answers2