循环遍历1个表并将选定的值添加到第2个表，并且“在循环迭代时执行更多操作[重复]

Question

This question already has an answer here:

• mysql_fetch_array()/mysql_fetch_assoc()/mysql_fetch_row()/mysql_num_rows etc… expects parameter 1 to be resource or result 32 answers

I have 2 tables: 'user2' and 'notes'

I would like to select the email entry in 'users2' where signin = 900 and then update a row in 'notes' (with the same email) with a PIN number.

<?php
require_once ('mysqli_connect.php'); //connect to database

$q = "SELECT email As email FROM users2 WHERE signin = 900";
$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q
<br />MySQL Error: " . mysqli_error($dbc));
$num = @mysqli_num_rows($r);
if ($num > 0) {
    while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)){         
        $email = $row['email'];

        $pin = 1234;
        $q = "UPDATE notes SET permit = '$pin' WHERE email = '$email'";
        $r = mysqli_query ($dbc, $q) or trigger_error("Query: $q
<br />MySQL Error: " . mysqli_error($dbc)); 
   }
}         
?>

An error is being returned:

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /home/fxyour/public_html/Checkin/new4.php on line 8

That is the line with 'while' on it.

I have tried various other ways but the error remains. I have the same layout in another project and it works fine.

</div>

Answer 1

Something like this should help:

$pin = 1234;
$q = "update notes set permit='$pin' WHERE email in (SELECT email FROM users2 WHERE signin = 900)";
$r = mysqli_query ($dbc, $q);

Update:

Error in currently provided code by author - $r variable used two times for different results, and used in while loop, so it will destroy workflow of script. Change one variable name.