There is a database is online. The database has the same schema in local and online with more than 25 tables. I am sending the INSERT or UPDATE records by JSON file. The id
is the key field for all tables. The JSON file may contains new id
records which to be inserted and old id
fields which to be updated the entire online table fields.
The following is the counters
table in online.
+-------+---------+---------------+---------------------+---------------------+------------+
| id | name | description | added_on | last_updated | department |
+-------+---------+---------------+---------------------+---------------------+------------+
| 1 | A | Bill | 2018-02-18 21:48:28 | 2018-02-18 15:08:34 | 1 |
| 2 | B | SAKTHY | 2018-06-21 12:49:30 | 2018-02-18 12:49:40 | 1 |
| 3 | C | | 2018-02-18 21:48:28 | 2018-02-18 21:48:28 | 1 |
+-------+---------+---------------+---------------------+---------------------+------------+
The following data is passed by JSON file to online database.
[
{
"tableName": "bank_accounts",
"rows": []
},
{
"tableName": "counters",
"rows": [
{
"id": "2",
"name": "B",
"description": "SAKTHY",
"added_on": "2018-06-21T12:49:30",
"last_updated": "2018-02-18T12:49:40",
"department": "1"
},
{
"id": "5",
"name": "E",
"description": "SAKTHY2",
"added_on": "2018-06-21T12:50:21",
"last_updated": "2018-06-21T14:52:18",
"department": "1"
},
{
"id": "6",
"name": "SAKTHY3",
"description": "Sample Friday",
"added_on": "2018-06-22T10:47:18",
"last_updated": "2018-06-22T10:47:18",
"department": "1"
}
]
},
{
"tableName": "customers",
"rows": []
}
]
To INSERT
or UPDATE
the records to the online database, this php script is used (thanks @Sloan Thrasher, @lovepreet-singh).
<?php
try
{
$connect = mysqli_connect("localhost", "username", "password", "database");
$query = '';
$table_data = '';
$filename = "sample.json";
$data = file_get_contents($filename);
$array = json_decode($data, true);
foreach($array as $set)
{
$tblName = $set['tableName'];
if(sizeof($set['rows']) > 0)
{
$query = '';
$colList = array();
$valList = array();
// Get list of column names
foreach($set['rows'][0] as $colName => $dataval)
{
$colList[] = "`".$colName."`";
}
$query .= "INSERT INTO `".$tblName."`
";
$query .= "(".implode(",",$colList).")
VALUES
";
// Go through the rows for this table.
foreach($set['rows'] as $idx => $row)
{
$colDataA = array();
// Get the data values for this row.
foreach($row as $colName => $colData)
{
$colDataA[] = "'".$colData."'";
}
$valList[] = "(".implode(",",$colDataA).")";
}
// Add values to the query.
$query .= implode(",
",$valList)."
";
// If id column present, add ON DUPLICATE KEY UPDATE clause
if(in_array("id", $colList))
{
$query .= "ON DUPLICATE KEY UPDATE
\t SET ";
$tmp = array();
foreach($colList as $idx => $colName)
{
//$tmp[] = $colName." = new.".$colName." ";
$tmp[] = $colName." = VALUE(".$colName.") "; // Changed this line to get value from current insert row data
}
$query .= implode(",",$tmp)."
";
}
else
{
echo "<p><b>`id`</b> column not found. <i>ON DUPLICATE KEY UPDATE</i> clause <b>NOT</b> added.</p>
";
echo "<p>Columns Found:<pre>".print_r($colList, true)."</pre></p>
";
}
echo "<p>Insert query:<pre>$query</pre></p>";
$r = mysqli_query($connect, $query);
echo mysqli_errno($connect) . ": " . mysqli_error($connect) . "
";
echo "<h1>".mysqli_affected_rows($connect). " Rows appended in .$tblName.</h1>";
}
else
{
echo "<p>No rows to insert for .$tblName.</p>";
}
}
}
catch(Exception $e)
{
echo $e->getMessage();
}
?>
But I got the following SQL echo's in the browser. In this case the online database does not updated or inserted new records.
No rows to insert for .bank_accounts.
`id` column not found. ON DUPLICATE KEY UPDATE clause NOT added.
Columns Found:
Array
(
[0] => `id`
[1] => `name`
[2] => `description`
[3] => `added_on`
[4] => `last_updated`
[5] => `department`
)
Insert query:
INSERT INTO `counters`
(`id`,`name`,`description`,`added_on`,`last_updated`,`department`)
VALUES
('2','B','SAKTHY','2018-06-21T12:49:30','2018-02-18T12:49:40','1'),
('5','E','SAKTHY2','2018-06-21T12:50:21','2018-06-21T14:52:18','1'),
('6','SAKTHY3','Sample Friday','2018-06-22T10:47:18','2018-06-22T10:47:18','1')
1062: Duplicate entry '2' for key 'PRIMARY'
-1 Rows appended in .counters.
No rows to insert for .customers.