CODEWITHSUNDEEP
phpmysql
Noel
Noel

Reputation: 21

Mysql Php select and process one record at a time using transaction and commit

What is the best way to select a range of ids from one table and then put them through a transaction loop to process a update one record at a time?

$result = mysql_query('SELECT p_code FROM replenishment ');
$ids = array();

while ($p_code = mysql_fetch_row($result)) {
    $ids[] = $p_code[0];
    foreach($ids as $p_code) {
        mysql_query('SELECT @A:=replenishment.p_code,@B:=replenishment.model
                        from replenishment
                        left join replenishment1 on replenishment1.p_code = replenishment.p_code
                        where replenishment.branch=10
                        and replenishment.p_code=$p_code
                        and replenishment.stock < min

                        and replenishment1.stock > 0

                        group by replenishment.p_code');
        mysql_query('UPDATE replenishment1
                        SET stock = (stock - @B), B5=(b5+@B)  WHERE @A = replenishment1.p_code
                        and replenishment1.stock >= @B');
        $row = mysql_fetch_assoc();
    }
}

Upvotes: 2

Views: 207

Answers (1)

Birju Shah
Birju Shah

Reputation: 1220

Start using PDO or MySQLi.

Through this you can manage your transaction and make sure that data enters in your database safely.

<?php
try {
  $dbh = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2', 
      array(PDO::ATTR_PERSISTENT => true));
} catch (Exception $e) {
  die("Unable to connect: " . $e->getMessage());
}

try {  
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $dbh->beginTransaction();

/* You can put your foreach loop over here or you can also do the entire process for single update. It's your choice */

  $dbh->commit();

} catch (Exception $e) {
  $dbh->rollBack();
  echo "Failed: " . $e->getMessage();
}
?>

It guarantees that no one else will be able to see those changes until they are complete. If something goes wrong, the catch block rolls back all changes made since the transaction was started, and then prints out an error message.

Upvotes: 1

Related Questions