PHP MYSQL Update several records in while loop

Question

I have that simple php code:

$sql= 'SELECT ID, fb_postid, scheduled FROM `posts` WHERE clicks = "" AND fb_postid !="" AND scheduled < NOW() order by ID ASC LIMIT 10;';

$result = mysqli_query($link, $sql);
print_r($result);

while ($row = mysqli_fetch_assoc($result)) {
            $clicks=fbcall($fbtoken, $row['fb_postid']);
            $update="UPDATE `posts` SET `clicks`='".$clicks."' WHERE id='".$row['ID']."'";
            $result = mysqli_query($link, $update);     
            print("POSTID: " . $row['fb_postid'] . " - Clicks: " . $clicks ."<br>");    
 };

The MYSQL SELECT gets 10 Lines from DB loops through the line in the While Loop, gets "clicks" from function fbcall and then should update all 10 lines with the values from "clicks" to the db. if i run the code without update i get 10 results printed but if i run with mysqli_update i just get 1 row updated. Anybody any Idea why?

Your code is vulnerable to [**SQL injection attacks**](https://en.wikipedia.org/wiki/SQL_injection). You should use [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php) prepared statements with bound parameters as described in [**this post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — Alex Howansky, Apr 17 '17 at 19:48
what does `mysqli_error($link)` show on the query? If you want truthness, use `mysqli_affected_rows()` on UPDATE — Funk Forty Niner, Apr 17 '17 at 19:49

Wouter van Nifterick · Accepted Answer · 2017-04-17T19:58:39.587

You're using the $result variable to iterate through a list of rows from the first query.

But you're giving $result a new value within the loop, clearing whatever was there.

Just use two different variables, and you'll be fine. Try something like this:

$sql= 'SELECT ID, fb_postid, scheduled FROM `posts` WHERE clicks = "" AND fb_postid !="" AND scheduled < NOW() order by ID ASC LIMIT 10;';

$result = mysqli_query($link, $sql);
print_r($result);

while ($row = mysqli_fetch_assoc($result)) {
            $clicks=fbcall($fbtoken, $row['fb_postid']);
            $update="UPDATE `posts` SET `clicks`='".$clicks."' WHERE id='".$row['ID']."'";
            $result2 = mysqli_query($link, $update);     
            print("POSTID: " . $row['fb_postid'] . " - Clicks: " . $clicks ."<br>");    
 };

score 0 · Answer 2 · answered Apr 17 '17 at 20:37

Fixed it and built with prepared update statement:

$sql= 'SELECT ID, fb_postid, scheduled FROM `posts` WHERE clicks = "" AND fb_postid !="" AND scheduled < NOW() order by ID ASC LIMIT 10;';
$result = mysqli_query($link, $sql);
$qry = "UPDATE `posts` SET `clicks`=? WHERE id=?";
$stmt   = mysqli_prepare ($link, $qry);
mysqli_stmt_bind_param ($stmt, 'ss',$clicks, $id);

while ($row = mysqli_fetch_assoc($result)) {
            $clicks=fbcall($fbtoken, $row['fb_postid']);
            $id=$row['ID'];
            mysqli_stmt_execute($stmt);
            print("POSTID: " . $row['fb_postid'] . " - Clicks: " . $clicks ."<br>");    
};

PHP MYSQL Update several records in while loop

2 Answers2