CODEWITHSUNDEEP
javascriptphpmysqljson
zdebruine
zdebruine

Reputation: 3807

Use PHP to insert JSON to MySQL table

I need to insert a Javascript object into a new MySQL table using PHP. My object is in the following form:

{
  rowA:[A1,A2,A3],
  rowB:[B1,B2,B3],
  rowC:[C1,C2,C3],
}

I also have an array of column names:

$columns = array("col1","col2","col3");

I need to use this data to create and fill a MySQL table in the following format:

      col1 col2 col3
rowA  A1   A2   A3
rowB  B1   B2   B3
rowC  C1   C2   C3

I can access my server and create a table, but am still unsure how to deal with JSON:

$str = file_get_contents('barchartData/US-HI.json');
$json = json_decode($str, true);
$conn = new mysqli($servername, $username, $password, $dbname);
$sql = "CREATE TABLE testTable (
   // not sure how to specify column, row, and cell values from the JSON...
)";

I am fairly new to PHP and haven't been able to get code in PHP working which read the JSON keys and set them as MySQL row values.

Upvotes: 0

Views: 3173

Answers (1)

Robert
Robert

Reputation: 1907

As mentioned in the earlier comment, I'd recommend splitting up the issue. Table creation should not be an issue because the values don't come from the JSON file.

So let's start by validating your JSON file. You can use an online tool for that. Have a look at the code below which you can run apart from your current script. It's not the most elegant solution but it's one that I think will help you understand the solution the best.

<?php

echo '<pre>';  // For testing purposes.

// Convert the array to comma separated values as is 
// required by the MySQL insert statement.
$fields          = array("col1","col2","col3");
$imploded_fields = implode(',', $fields);

$json            = '{"rowA":["A1","A2","A3"], "rowB":["B1","B2","B3"], "rowC":["C1","C2","C3"]}';
$decoded         = json_decode($json);

foreach ($decoded as $d) {

    $values  = implode(',', $d);
    $statement =  "INSERT INTO `table` ($imploded_fields) VALUES ($values)";

    echo $statement; // Change this to actually execute the statement
}

Hope this makes sense. If you further want to optimise this solution, look at helpful functions like array_map and array_keys.

Upvotes: 3

Related Questions