php query-loop does not work

Question

I have this code:

public function updateOrder($num, $ufood, $uquan) {
    $response = array();    

    mysql_query("SET NAMES 'utf8'");

    foreach ($ufood as $index => $f) {
        $result = mysql_query("SELECT food, quantity, uquantity FROM table1 WHERE food ='".$f."'") or die(mysql_error());  
        $no_of_rows = mysql_num_rows($result);

        $response['number rows'] = $no_of_rows;

        if ($no_of_rows>0) {
            while ($row = mysqli_fetch_array($result)); {
                if (!$row['uquantity']) {
                    $w = "INSERT INTO table1(uquantity) VALUES ('$uquan[$index]')";

                    mysql_query($w);
                    $e = (int)$row['quantity'];
                    $q = (int)$uquan[$index];
                    $sum = $e+$q;
                    $s = (string)$sum;
                    $d = "UPDATE table1 SET quantity = '$s' WHERE food = ".$row['$food']." ";
                    mysql_query($d);

                } else if($row['uquantity']) {
                    $c = (int)$row['uquantity'];
                    $q = (int)$uquan[$index];
                    $sumq = $c+$q;
                    $sq = (string)$sumq;
                    $d = "UPDATE table1 SET uquantity = '$sq' WHERE food = ".$row['$food']." ";
                }
            }
        } else {
            $string ="INSERT INTO table1(food,uquantity) VALUES ('".$f."','".$uquan[$index]."')";
            $z = mysql_query($string);      
        }
    }
}

Well i can not make this work, and i am trying all kinds of things put still it doesn't work. So i have some questions:

Is this structure of foreach and while valid?
Though the $result query returns some rows from the database, when i try to use $row['quantity'], as a value, i get null.

In this code i receive some data from an android app, and i try to "see", if there are already entries for the type food of my db_table(table1). If there are entries i want the db to sum the quantity entry of the android sent, data with the one that are inside my db, and update the field. This is the basically it. But as i said when i try to use the data that comes from the database, i get null values.

Please if someone could give me some hint, cause I'm really stuck..

For one thing you are using mysql_connect but then calling mysqli_fetch_array. You probably were looking for mysql_fetch_array. mysql and mysqli are separate extensions. — Slickrick12
– Slickrick12, Commented Aug 2, 2013 at 21:14
Note the semi-colon after your while statement - that shouldn't be there. — Orbling
– Orbling, Commented Aug 2, 2013 at 21:27

4 revs · Accepted Answer · 2017-04-13 12:40:36Z

3

There are many problems with your code. I'm marking this answer as Community Wiki, and I invite others to edit and add things as they find them.

You may also consider posting to https://codereview.stackexchange.com/ instead, when you have so many mistakes, until you have a more specific question.

Bad variable interpolation

This line won't do what you want it to:

$w = "INSERT INTO table1(uquantity) VALUES ('$uquan[$index]')";

This is not quite valid PHP syntax. You can either concatenate expressions:

$w = "INSERT INTO table1(uquantity) VALUES ('".$uquan[$index]."')";

Or you can embed expressions in curly braces:

$w = "INSERT INTO table1(uquantity) VALUES ('{$uquan[$index]}')";

Or you can use a query parameter placeholder:

$w = "INSERT INTO table1(uquantity) VALUES (?)";
$stmt = mysqli_prepare($w) or die(mysqli_error());
$uqi = $uquan[$index];
mysqli_stmt_bind_param($stmt, "i", $uqi);
mysqli_stmt_execute($stmt);

Mixing MySQL APIs

You can't mix mysql_query() with mysqli_fetch_array(). PHP has more than one API for MySQL, and you can't mix them. You should standardize on using the mysqli API, because the older mysql API is now deprecated.

Semicolon defeats while loop

The semicolon after the while statement makes the loop a no-op, and when it terminates, the $row contains nothing.

while ($row = mysqli_fetch_array($result)); {

Should be:

while ($row = mysqli_fetch_array($result)) {

Using variables inappropriately

Referencing a $row key with a single-quoted variable is probably not what you mean, in multiple ways:

$d = "UPDATE table1 SET quantity = '$s' WHERE food = ".$row['$food']." ";

The column name in the select-list of your earlier SELECT query is 'food', not '$food'.

Also, even if you meant to use a variable name $food as the key, putting it in single quotes would not use the value of the variable, it would be the literal string '$food'.

Failure to quote string literal?

Furthermore, you use a quoted literal for comparing to the food column in your SELECT query, which makes me think it might be a string.

So the UPDATE should be something like:

$d = "UPDATE table1 SET quantity = '$s' WHERE food = '".$row['food']."' ";

Or:

$d = "UPDATE table1 SET quantity = '$s' WHERE food = " . intval($row['food']);

Or preferably use parameters and a prepared query, then you don't need to worry about quotes or types:

$d = "UPDATE table1 SET quantity = ? WHERE food = ?";
. . .

Failure to check for errors

Every query might fail, either because you have a syntax error (e.g. a string without quoting), or because the table doesn't have a column by the name you reference, or privileges issues, etc.

Always check the return status of the query function when you run a SQL query. The function returns false if there's an error, and if that happens you must check the error message.

mysqli_query($mysqli, $d) or trigger_error(mysqli_error($mysqli), E_USER_ERROR);

Failure to execute the UPDATE

Your second update assigns a SQL query string to the variable $d, but then does not execute that update query at all!

edited Apr 13, 2017 at 12:40

community wiki

4 revs
Bill Karwin

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

4 Comments

user1732457 Over a year ago

thnx, i ve seen what you said, and i pasted a code thatm that was beiing changed many times. Still my main problem is that i dont get the $row['food'] values etc.

Orbling Over a year ago

@user1732457: Until you correct the issues mentioned above, the code won't work at all, once you have done that - any further issues may be addressed.

user1732457 Over a year ago

Actually i changed everything that was mentioned and now i do get the value of $row['food'], the problem is that the UPDATE query wont work this time

user1732457 Over a year ago

Thnx very much for the help, my row still wont wont update, but i corrected many mistakes i had

Collectives™ on Stack Overflow

php query-loop does not work

1 Answer 1

Bad variable interpolation

Mixing MySQL APIs

Semicolon defeats while loop

Using variables inappropriately

Failure to quote string literal?

Failure to check for errors

Failure to execute the UPDATE

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Bad variable interpolation

Mixing MySQL APIs

Semicolon defeats while loop

Using variables inappropriately

Failure to quote string literal?

Failure to check for errors

Failure to execute the UPDATE

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related