mysql query only selecting first record

Question

I am trying to execute the query to update the record in php.My php code is this:

$get_meals_info =  "Select meal_id from orders WHERE order_id ='$order_id'";
$result = mysql_query($get_meals_info) or die(mysql_error());;

if(mysql_num_rows($result)!=0)
{
    while($meal_id = mysql_fetch_assoc($result)) {
        $id=    $meal_id['meal_id'];
        //$update_status="Select meal_id from orders where order_id=" . $meal_id['order_id'] . "";

        $update_status =  "update order_main SET STATUS='$order_status' WHERE id =" . $meal_id['meal_id'] . "";
        $result = mysql_query($update_status);
        //$meal_id=null;
        echo $id;
    }

I first get all the meal ids related to one order in my first select query and then i try to update the status for each meal using while loop but what i get only first meal record updated not the other one.I get this reponse:

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\learning\service\update_order_status.php on line 13

true

I'm afraid to ask where the variables come from that you are injecting in your query, but you could probably do this in one query / without a loop. Something like `UPDATE ... WHERE id IN (SELECT meal_id FROM ...)` — jeroen, Nov 01 '15 at 11:15

score 4 · Accepted Answer · edited Nov 01 '15 at 11:12

4

You're using the same variable $result for both your data fetching and update, and the first update causes you to lost the original data and then $result only contains true instead of the resource.

edited Nov 01 '15 at 11:12

Mubin

4,325
5
33
55

answered Nov 01 '15 at 11:01

James Z

12,209
10
24
44

score 1 · Answer 2 · answered Nov 01 '15 at 12:40

Why are you using a loop for this? You can accomplish this using a single query. For instance:

update order_main
    SET STATUS = '$order_status'
    WHERE id IN (Select meal_id from orders WHERE order_id = '$order_id');

Note also that you should be using mysqli_ functions and parameterizing the query so you are not passing user input in directly.

mysql query only selecting first record

2 Answers2