insert data from html table to mysql-CodePudding

First I convert CSV to html table, next I post data from form html table to this below script and then INSERT new records to database:

foreach($file_data as $row)
 {
  $data[] = '("'.$row[$_POST["sku"]].'", "'.$row[$_POST["stock"]].'")';
 }

 if(isset($data))
 {
  $query = "
  INSERT INTO products 
  (sku, stock) 
  VALUES ".implode(",", $data)."
  ";

This above function working correct and I can insert example 1000 records to data mysql.

And now I need change this function and UPDATE mysql table.

I try some like this:

 $file_data = $_SESSION['file_data'];

 unset($_SESSION['file_data']);

 foreach($file_data as $row)
 {
  $data[] = '("'.$row[$_POST["sku"]].'")';
  $title[] = '("'.$row[$_POST["title"]].'")';
  $lang_id[] = '("'.$row[$_POST["lang_id"]].'")';
 }

 if(isset($data))
 {
  $query = "
  UPDATE product_details
  SET title = ".implode(",", $title)." 
  WHERE sku = ".implode(",", $data)." AND lang_id = ".implode(",", $lang_id)."
  ";

  $statement = $connect->prepare($query);

  if($statement->execute())
  {
   echo 'Data Imported Successfully';
  }
 }

and this update function working, but only when is available/post 1 record in CSV file (html table). When I try update/post example 2 or more records from html table (csv file) then not working.

I know currently from this above function I get result when I post only 1 record:

UPDATE product_details
SET title = title1 WHERE sku = "P6951H0E3-Q12"  AND lang_id = 1;

Then this above query working. But When I try post 2 or more records then I get output query somelike this:

UPDATE product_details
    SET title = title1 WHERE sku = "P6951H0E3-Q12"  AND lang_id = 1
    SET title = title2 WHERE sku = "PLD_4051/S";" AND lang_id = 1

and this above SQL query never will work but how to get result like this:

UPDATE product_details SET title = "title1" WHERE sku = "P6951H0E3-Q12" AND lang_id = "1"; 
UPDATE product_details SET title = "title2" WHERE sku = "PLD_4051/S" AND lang_id = "1";

CodePudding user response：

I think this will work

$file_data = $_SESSION['file_data'];

unset($_SESSION['file_data']);

foreach ($file_data as $row) {
    $data = $row[$_POST["sku"]];
    $title = $row[$_POST["title"]];
    $lang_id = $row[$_POST["lang_id"]];

    if (isset($data)) {
        $statement = $connect->prepare("UPDATE product_details  SET title = '$title'
        WHERE sku = '$data' AND lang_id = '$lang_id'");

        if (!$statement->execute()) {
            $error = 'None or part of the data was updated';
        }
    }
}
echo $error ?? 'Data Updated Successfully';