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? for more info — Simba
– Simba, Commented Nov 3, 2015 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
– TrapezeArtist, Commented Nov 3, 2015 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
– Simba, Commented Nov 3, 2015 at 16:01

Pupil · Accepted Answer · 2015-11-03 13:11:07Z

1

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.

answered Nov 3, 2015 at 13:11

Pupil

24k6 gold badges49 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Arun Krish · Accepted Answer · 2015-11-03 13:09:24Z

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 3, 2015 at 13:09

Arun Krish

2,1531 gold badge12 silver badges15 bronze badges

1 Comment

TrapezeArtist Over a year ago

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.

Collectives™ on Stack Overflow

MySQL Error: query not giving correct data

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related