1

here is a little code:

function setList ($to, array $array)
{    
    DELETE FROM list WHERE A = $to;
    foreach ($array as $id)
    {
        INSERT INTO LIST ($to, $id);
    }
}

setList (2, []);
setList (2, [1,2,3,4]);
setList (2, [2,3]);
setList (2, [3,4,5,6]);

this is to update a list, but to avoid duplicate entries - remove all first. Its MyIsam, so basically no way to use transactions. Is there a way to delete+insert at the same time?

Imran Ali
  • 2,223
  • 2
  • 28
  • 41
John Smith
  • 6,129
  • 12
  • 68
  • 123
  • The question is unclear please include the table structure in question and what exact output you required include a example of that – Hamza Dairywala Aug 27 '16 at 09:55
  • 1
    You don't need transactions for this. Use unique indexes and make use of `INSERT IGNORE` or `ON DUPLICATE KEY UPDATE`, whichever fits your use scenario better. – N.B. Aug 27 '16 at 10:21
  • @N.B. and for deleting? :) – John Smith Aug 27 '16 at 10:32
  • And what would you have to delete if you never have duplicates? If the goal is to have unique records in a table, then you set a `unique` key on a column. For what reason you're after a delete - I don't know, but I'd suggest that you post the whole problem you're facing, not just the part you're stuck with. This is a classic example of [XY](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) problem. – N.B. Aug 27 '16 at 11:03

1 Answers1

1

As this (and many other) SO questions discuss, the MyISAM engine does not support transactions. If you want transaction support, you should really consider using InnoDB instead.

However, you can make your current code more atomic even with MyISAM, by recognizing that the INSERT statements can all be done together in a single statement, rather than separately.

$sql = array(); 
foreach ($data as $row) {
    $sql[] = '("'.mysql_real_escape_string($row['to']).'", '.$row['id'].')';
}
mysql_query('INSERT INTO table (to, id) VALUES '.implode(',', $sql));

This also has the nice side effect that the inserts will be done more efficiently as well.

Community
  • 1
  • 1
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360