MySQL update while looping with PHP foreach

Question

I want to update MySQL database records in a row...

The code below is used to connect the database with PDO and select a table, use foreach loop to extract data from it and then, later I want to be able to update the status of each row based on the extracted data..

How this should be done correctly with PHP and MySQL, so that rows get extracted and updated one by one...

Code:

// Database connection
define('DBHOST','localhost');
define('DBUSER','username');
define('DBPASS','password');
define('DBNAME','database');
try {
    //create PDO connection 
    $db = new PDO("mysql:host=".DBHOST.";dbname=".DBNAME, DBUSER, DBPASS);
    $db->exec("set names utf8");
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch(PDOException $e) {
    //show error
    echo '<p>'.$e->getMessage().'</p>';
    exit;
}


$sql = 'SELECT * FROM table';
foreach ($db->query($sql) as $row) {

     $id = $row['id'];
     $status= $row['status'];
     echo $status;


     if($status==1) {

     //Update status 
     //$sql_update = "UPDATE table SET status=2 WHERE id=". $id ."";

     }
}

I want to update each selected record with a status of 1 to 2...

So you just want to update _all_ records that have status=1 to have status=2? Then you don’t need to select them first and then go over them in a loop ... all you need is _one single_ statement, `UPDATE table SET status=2 WHERE status=1`, and done ... — CBroe, Aug 22 '17 at 09:28
I want to select each row to assign variables and do stuff.. this why I use `foreach` but I also want to update each row when done doing stuff.. — Hakerovsky, Aug 22 '17 at 09:45

DoPPeS · Accepted Answer · 2017-08-22T10:04:01.193

You could just all do it in one query really:

UPDATE table SET status=2 WHERE status=1

Is there a reason why you want to do this one by one? If it is performance reasons, you can limit your query in batches like this:

UPDATE table SET status=2 WHERE status=1 LIMIT 100

If you really need to loop them one by one, putting this snippet in your if statement should help a bit:

$updateSql = "UPDATE table SET status=2 WHERE id=:id";

// Prepare statement
$stmt = $db->prepare($updateSql);
$stmt->bindParam(":id", $id); 

// execute the query
$stmt->execute();

Still, you should make your select more efficient by limiting the query to rows with status = 1 and limiting it to the data you actually need, so you don't need to do the if and just run my snippet in the loop directly:

SELECT id FROM table WHERE status = 1

Thanks but, yes, I want to get the data from each row and run some code, then update the record and run the code for the next row... — Hakerovsky, Aug 22 '17 at 09:47
added more info in post to run the update statement on a one by one basis in your php loop. — DoPPeS, Aug 22 '17 at 09:56

Stefan · Answer 2 · 2017-08-25T12:53:24.047

0

If i understand you correctly, just use the query

UPDATE table SET status=2 WHERE id = :id

and bind :id to the extracted $id using PDO prepare

Also your SELECT query should be like this:

SELECT id, status FROM table WHERE status = 1

If you don't need to use the data returned by the SELECT statement just hit an UPDATE query to your database without selecting like this:

UPDATE table SET status=2 WHERE status=1

However, if there are many records to be updated (talking about 100k+), this wouldn't be the best choice.

Please note that table should have an index on status, because the query shouldn't need to scan every row, but know which rows to update.

edited Aug 25 '17 at 12:53

answered Aug 22 '17 at 09:28

Stefan

2,028
2
36
53

1

why should the table has a index ? – Noob Aug 22 '17 at 09:31
Added explanation, please refer to https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html or https://stackoverflow.com/questions/3567981/how-do-mysql-indexes-work – Stefan Aug 25 '17 at 12:53
I know what index is but you should not use index to use index. USing index could also slower your query. – Noob Aug 25 '17 at 12:56
W-What? "not use index to use index"?! What do you mean? – Stefan Aug 25 '17 at 12:58

score 0 · Answer 3 · answered Aug 22 '17 at 09:31

0

Your query can be simplified to:

UPDATE `table` SET status=2 WHERE status=1

In php:

$sql = 'UPDATE `table` SET status=2 WHERE status=1';
$db->query($sql);

answered Aug 22 '17 at 09:31

u_mulder

54,101
5
48
64

score 0 · Answer 4 · answered Aug 22 '17 at 09:33

I want to update each selected record with a status of 1 to 2...

If you want to update records you don't need to make the select statement first. In fact, it doesn't work this way , because you're updating single values in your script without any impact or whatsoever on your database

as a notice you may consider to add

$db->setAttribute(PDO::ATTR_EMULATE_PREPARE,false)

to make the prepared statement activated, as a matter of fact this is a security mesure

score 0 · Answer 5 · answered Aug 22 '17 at 09:34

As I good understand youcan do it this way...

 $sth = $db->prepare("INSERT INTO table VALUES(:value, :value1, :value2, ...)");

foreach ($db->query($sql) as $row) {

 $id = $row['id'];
 $status= $row['status'];


 if($status==1) {

 $sth->bindParam(':value',$someValue));
 $sth->bindParam(':value1',$someValue1));
 $sth->bindParam(':value2',$someValue2));
 //...
 $sth->execute();

 }

MySQL update while looping with PHP foreach

5 Answers5