0
$st = $dbh->prepare("UPDATE items SET some_id = ? WHERE id = ?");

foreach ($items as $key => $val)
    $st->execute(array($key, $val));

Is there a more efficient solution?

good_evening
  • 21,085
  • 65
  • 193
  • 298
  • What exactly are you finding to be inefficient about that? Is there an actual issue that you're having or is this premature optimization? – Patrick Q Aug 14 '14 at 19:56
  • @PatrickQ No it works just fine, I was just guessing that calling this many times is bad when the application becomes bigger and it needs to update more rows..? But I am actually not sure how these queries work internally tbh, do they execute update queries separately or not.. – good_evening Aug 14 '14 at 20:01
  • Prepare/Execute was designed for this. You prepare a statement, then you run execute for how many times you need it. I do not see anything wrong with the above code. – Dave Chen Aug 14 '14 at 20:03

2 Answers2

1

Well it is all about size ;)

If you want to INSERT milions of rows you should:

  1. Generate CSV from php, place it in location available from database.
  2. CREATE TABLE items_copy LIKE items or similar.
  3. Remove all keys from items_copy
  4. Insert into items_copy FROM CSV file: http://dev.mysql.com/doc/refman/5.0/en/loading-tables.html
  5. UPDATE SET combined with SELECT * FROM items_copy
  6. DROP TABLE items_copy

If you want to insert just couple of thousands rows you should combine your query into one big string and do it in transaction, like:

$q = 'START TRANSACTION;'; $q .= 'UPDATE items SET some_id = ? WHERE id = ?;'; $q.= 'UPDATE items SET some_id = ? WHERE id = ?;'; .... $q .= 'COMMIT;';

And then execute $q. The reason you should do that is connecting to database for each query will ruin effectiveness.

Otherwise you foreach function will be just fine.

MacEncrypted
  • 184
  • 9
0

Hmmm maybe,

$dbh->beginTransaction();

$dbh->exec('LOCK TABLES items');

$st = $dbh->prepare("UPDATE items SET some_id = ? WHERE id = ?");

foreach ($items as $key => $val)
    $st->execute(array($key, $val)); 

$dbh->commit();
$dbh->exec('UNLOCK TABLES');
Marcos Tavares
  • 113
  • 2
  • 7