Samuel Robert Samuel Robert - 3 years ago 109
JSON Question

How to insert values to table that are only there in the JSON object in PHP

I've a table which is created with below SQL statement

CREATE TABLE example (
col1 varchar(20) DEFAULT NULL,
col2 varchar(20) DEFAULT NULL,
col3 varchar(20) DEFAULT NULL

I want to insert values to this table from a JSON object. JSON object is constructed such way that its key is the column name and value is the value to be inserted.

How do I write a PHP code to only insert the values that are there in the JSON object?

For instance, If I have a JSON object

"col1": "some value",
"col3": "some value"

The insert statement should only insert values to col1 and col3 and the col2 should remain as NULL.

Please help me out to solve this puzzle. PHP with PDO is preferable.

Experiments performed so far

$object = json_decode($jsonObject);

foreach ($object as $col => $val) {
$columns .= $col . ",";
$values .= $val . ",";

$sql = "INSERT INTO example (" .substr($columns, 0, strlen($columns)-2) .
") VALUES (" . substr($values, 0, strlen($columns)-2) . ")";

Answer Source

Your code is pretty close. The problem is that you don't have quotes around each value in $values.

Here's another way to write it:

$object = json_decode($jsonObject, true);
$columns = implode(', ', array_keys($object);
$values = implode(', ', array_map(function($x) { return "'$x'"; }, $object));

$sql = "INSERT INTO example($columns) VALUES ($values)";

The function in array_map() returns each value with quotes around it, then implode() combines them all with commas.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download