How to update a database table data from an PHP array?

I need to update the information of a mysql table database from a php array.

I’m looking for the best approach to do this, what I want to do is to update an existing database table with new information, this means that there could be new rows, the existing rows could have new values in their fields, or rows deleted.

The approaches that I am thinking are:

1. Erase all the data and rows from the current table and insert again all the rows with the most updated data provided by the php array. But I don’t know if this is an expensive approach or indeed it is used.
2. Another idea that I have is that maybe I could check if the field values from the rows had changed and if it is the case update then the values, also check the order of the rows to check if some rows were deleted and the order changed, and lastly insert new rows if it is the case, but i think this get really tricky and messy.

What do you think? Some ideas for the better approach? Thanks for your support.

Answer

You can use INSERT … ON DUPLICATE KEY UPDATE to INSERT new rows and UPDATE existing rows.

Example:

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE a=1,b=2,c=3;

You have to build DELETE statement using some trick in php.

Example:

<?php

$data = array(
    array( 'a'=>1, 'b'=>2, 'c'=>3 ),
    array( 'a'=>4, 'b'=>5, 'c'=>6 ),
    array( 'a'=>7, 'b'=>8, 'c'=>9 ),
);

$condition = implode( ',', array_map( function($temp){return sprintf("('%s','%s','%s')",$temp['a'],$temp['b'],$temp['c']);},$data));
$sql = "DELETE FROM t1 WHERE (a,b,c) NOT IN (" . $condition . ");";
echo $sql; // DELETE FROM t1 WHERE (a,b,c) NOT IN (('1','2','3'),('4','5','6'),('7','8','9'));

// OR

$condition1 = implode( ',', array_map( function($temp){return sprintf("'%s'",$temp['a']);},$data));
$sql1 = "DELETE FROM t1 WHERE a NOT IN (" . $condition1 . ");";
echo $sql1; // DELETE FROM t1 WHERE a NOT IN ('1','4','7');

?>