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 ... — C3roe
– C3roe, Commented Aug 22, 2017 at 9: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
– Hakerovsky, Commented Aug 22, 2017 at 9:45

DoPPeS · Accepted Answer · 2017-08-22 10:04:01Z

1

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

edited Aug 22, 2017 at 10:04

answered Aug 22, 2017 at 9:32

DoPPeS

861 silver badge5 bronze badges

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

2 Comments

Hakerovsky Over a year ago

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...

DoPPeS Over a year ago

added more info in post to run the update statement on a one by one basis in your php loop.

u_mulder · Accepted Answer · 2017-08-22 09:31:23Z

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, 2017 at 9:31

u_mulder

54.8k5 gold badges54 silver badges72 bronze badges

Comments

user10089632 · Accepted Answer · 2017-08-22 09:33:56Z

0

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

answered Aug 22, 2017 at 9:33

user10089632

5,6301 gold badge28 silver badges34 bronze badges

Comments

Chrzanek · Accepted Answer · 2017-08-22 09:34:24Z

0

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();

 }

answered Aug 22, 2017 at 9:34

Chrzanek

1973 silver badges15 bronze badges

Comments

Stefan · Accepted Answer · 2017-08-25 12:53:24Z

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, 2017 at 12:53

answered Aug 22, 2017 at 9:28

Stefan

2,0682 gold badges36 silver badges55 bronze badges

4 Comments

Noob Over a year ago

why should the table has a index ?

Stefan Over a year ago

Added explanation, please refer to dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html or stackoverflow.com/questions/3567981/how-do-mysql-indexes-work

Noob Over a year ago

I know what index is but you should not use index to use index. USing index could also slower your query.

Stefan Over a year ago

W-What? "not use index to use index"?! What do you mean?

Collectives™ on Stack Overflow

MySQL update while looping with PHP foreach

5 Answers 5

2 Comments

Comments

Comments

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related