0

I have a script in php that writes to the mysql array:

$plu_arr = $_POST["plu"];
$il_arr = $_POST["il"];
$op_arr = $_POST["op"];
$row_index = 0;
$row_arr = array();
            
foreach($plu_arr as $plu_data){
   if (isset($il_arr[$row_index]) and !empty($il_arr[$row_index]))
   {
      $row_arr[] = "('".$plu_data."','".$il_arr[$row_index]."','".$op_arr[$row_index]."')";
   }
   $row_index++;
}
        
$ins_qry = "INSERT INTO produkty_zamowienia (plu, nr_order, il, op) VALUES ".implode(", ", $row_arr);
$db_ins = $this->__db->execute($ins_qry);

$nr_order is the same for all plu in this order. Works correctly. Adds what you need to the database. I would like to do INSERT ON DUPLICATE KEY UPDATE on the same principle

I wrote something like this:

$ins_qry = "INSERT INTO produkty_zamowienia (FK_ID_produkt, plu, nr_order, il, op) VALUES ".implode(", ", $row_arr)." ON DUPLICATE KEY UPDATE ".implode(", ", $row_arr);

But this solution writes nothing to the database and stops at $ins_qry... Please help, how can I change the existing ones and add new entries to the database ... I would like $ins_qry to check if such plu + nr_order already exists than it edits $il and $op and if not, insert this row.

k_turek
  • 191
  • 9
  • (1) Obtain final `$ins_qry` value (2) Open Reference Manual, "INSERT .. ON DUPLICATE KEY UPDATE syntax" and check your query for synthactical correctness. – Akina Dec 16 '21 at 05:32
  • Check this answer: https://stackoverflow.com/questions/2714587/mysql-on-duplicate-key-update-for-multiple-rows-insert-in-single-query – masterguru Dec 16 '21 at 05:34
  • I understand this syntax but don't know how to apply it to $row_arr [] – k_turek Dec 16 '21 at 05:45
  • Way 1: $ins_qry = "INSERT INTO produkty_zamowienia (plu, nr_order, il, op) VALUES ".implode(", ", $row_arr)." AS new ON DUPLICATE KEY UPDATE il = new.il, op = new.op"; – KHIMAJI VALUKIYA Dec 16 '21 at 06:00
  • 1
    Way 2: $ins_qry = "INSERT INTO produkty_zamowienia (plu, nr_order, il, op) VALUES ".implode(", ", $row_arr)." ON DUPLICATE KEY UPDATE il = VALUES(il), op = VALUES(op)"; – KHIMAJI VALUKIYA Dec 16 '21 at 06:00
  • Use based on your Mysql Version, Also make sure you've defined both field combination as Unique(plu + nr_order) in Database. – KHIMAJI VALUKIYA Dec 16 '21 at 06:01
  • @KHIMAJIVALUKIYA In that post that I linked before, in the comments, they say do NOT use "new" because it is a [MYSQL reserved word](https://dev.mysql.com/doc/refman/8.0/en/keywords.html), sorry. – masterguru Dec 16 '21 at 06:08
  • https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html – KHIMAJI VALUKIYA Dec 16 '21 at 06:11
  • @KHIMAJIVALUKIYA Way 1 unfortunately does not work ... Way 2 UPDATE and INSERT new rows. – k_turek Dec 16 '21 at 06:19
  • ON DUPLICATE KEY UPDATE only works If you have unique keys in Database table otherwise it will always insert new entries – KHIMAJI VALUKIYA Dec 16 '21 at 06:21
  • @KHIMAJIVALUKIYA Way2 in Your comment is a answer for my question... Thanks. – k_turek Dec 16 '21 at 06:31

1 Answers1

1

Try this:

$ins_qry = "INSERT INTO produkty_zamowienia (plu, nr_order, il, op) VALUES ".implode(", ", $row_arr)." ON DUPLICATE KEY UPDATE il = VALUES(il), op = VALUES(op)";
KHIMAJI VALUKIYA
  • 626
  • 1
  • 5
  • 16