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. You should use mysqli or PDO prepared statements with bound parameters as described in this post. — Alex Howansky
– Alex Howansky, Commented Apr 17, 2017 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
– Funk Forty Niner, Commented Apr 17, 2017 at 19:49

Wouter van Nifterick · Accepted Answer · 2017-04-17 19:58:39Z

1

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>");    
 };

edited Apr 17, 2017 at 19:58

answered Apr 17, 2017 at 19:56

Wouter van Nifterick

24.2k7 gold badges82 silver badges123 bronze badges

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

Comments

swapfile · Accepted Answer · 2017-04-17 20:37:20Z

0

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>");    
};

answered Apr 17, 2017 at 20:37

swapfile

4173 silver badges19 bronze badges

Collectives™ on Stack Overflow

PHP MYSQL Update several records in while loop

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related