php - The most efficient way to insert if doesn't exist else update -

php - The most efficient way to insert if doesn't exist else update -

similar threads on stackoverflow.com none of them explains efficient way insert if doesn't exist in table or update if , let's assume want thousands of records @ 1 time , often.

there table inventory stores id, sku, warehouseid, quantity

i communicate api list of inventory (let's in amazon) , want synchronize scheme know how many quantities have in warehouse. of course of study in cases new skus query save in db insert query ones old update one. efficient way perform operation when have sync thousands of records? 1 more thing add together combination of sku, warehouseid must unique when perform update need check first if combination doesn't exist if must utilize insert.

please maintain in mind inserting records utilize multiple insert pdo (1000 inserts @ once) can technically perform 1000 inserts within 1-2 seconds.

i thinking pulling records inventory table , saving them in array. pulling records api phone call , comparing them in code. way able specify illustration 5000 records require update , 10000 insert. think that? faster performing select first , insert or update? there way perform multiple updates 1 query similar insert records?

here multiple insert function:

function insert_multiple($table_name, $data) { $db = sitedb('','pdo'); $sub_data = array_chunk($data, 1000); ($b = 0; $b < count($sub_data); $b++) { $insert_values = array(); ($a = 0; $a < count($sub_data[$b]); $a++) { $insert_values = array_merge($insert_values, array_values($sub_data[$b][$a])); $placeholder[] = '(' . implode(', ', array_fill(0, count($sub_data[$b][$a]), '?')) . ')'; } $sql2 = "insert $table_name (" . implode(",", array_keys($sub_data[$b][0])) . ") values " . implode(',', $placeholder) . ""; $prepare = $db->prepare($sql2); seek { $prepare->execute($insert_values); } grab (pdoexception $e) { echo "<pre>"; print_r($sub_data[$b]); echo "</pre>"; echo $e->getmessage(); print_r($db->errorinfo()); } unset($insert_values); unset($placeholder); } }

replace tablename set field1 = value1, field2 = value2;

http://dev.mysql.com/doc/refman/5.0/en/replace.html

php mysql pdo