CODEWITHSUNDEEP
phpmysqlmysqli
swapfile
swapfile

Reputation: 417

PHP MYSQL Update several records in while loop

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?

Upvotes: 1

Views: 2612

Answers (2)

swapfile
swapfile

Reputation: 417

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

Upvotes: 0

Wouter van Nifterick
Wouter van Nifterick

Reputation: 24096

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

Upvotes: 1

Related Questions