Error trying to run a mysqli query within another query-CodePudding

I am trying to run a mysqli query within another query but it will only cycle once.

$result = mysqli_query($db,"SELECT * FROM test WHERE active = 'y' AND expiredate < $todaydate") or die("<b>Error in Query</b>.\n<br>Error: " . mysqli_error($db));
    while ($row = mysqli_fetch_array($result)) {
    extract($row);  
        $query = "UPDATE test SET active='y' WHERE (id = '$id')";
        $result = mysqli_query($db,$query) or die("<b>Error in Query 3</b>.\n<br>Error: " . mysqli_error($db));
        echo "$fname $lname ($id) active status changed to n <br>";

}

I get an error on line 10 (the while statement) on second cycle:

mysqli_fetch_array() expects parameter 1 to be mysqli_result, bool given in /home/ptb/public_html/admin/cron/test.php on line 10

Any help would be appreciated. Thanx

CodePudding user response：

You don't need 2 queries, just run the update based on the select criteria.

UPDATE test SET active='y' WHERE active = 'y' AND expiredate < ?"

and then bind $todaydate or just use CURDATE().

Although rereading this it makes no sense. SET active='y' WHERE active = 'y'? why set the column to the same value it has?

... to explain the logic issue though you redefined your iterating variable so the condition is met and the while stops. Here:

$result = mysqli_query($db,"SELECT * FROM test WHERE active = 'y' AND expiredate < $todaydate") or die("<b>Error in Query</b>.\n<br>Error: " . mysqli_error($db));

you define $result then iterate on that:

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

and then redefine it in the loop which resets the cursor:

    $result = mysqli_query($db,$query) or die("<b>Error in Query 3</b>.\n<br>Error: " . mysqli_error($db));

Since the update is either TRUE or FALSE you get boolean, if it were a different select you could end up with all sort of different rows. You could either do:

$result2 = mysqli_query($db,$query) or die("<b>Error in Query 3</b>.\n<br>Error: " . mysqli_error($db));

or just don't assign it to a variable:

mysqli_query($db,$query) or die("<b>Error in Query 3</b>.\n<br>Error: " . mysqli_error($db));

The first solution, in my opinion, is the best option.

mysqli_query($db, "UPDATE test SET active='y' WHERE active = 'y' AND expiredate < CURDATE()");

that's dependent on MySQL timing and PHP timing being the same, if not define the PHP variable but bind it in.

CodePudding user response：

The problem is that $todaydate needs to be quoted. You are not quoting it properly. Your query is failing, returning FALSE, and since you never check for errors, the mysqli_fetch_array call fails.

You should use parameter binding, but in lieu of that you at least need

"...AND expiredata < '$todaydate'..."