Use PHP to insert JSON to MySQL table

Question

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.

Just take a look at var_dump(json_decode($json)); What do you think you can add in the database and how? — Koen Hollander
– Koen Hollander, Commented Jan 6, 2017 at 16:54
Do you want to insert JSON (as in the title) or do you want to use JSON to create a table and then populate it? — Robert
– Robert, Commented Jan 6, 2017 at 16:55
So I did essentially that, and I know the structure of my php array after importing, but I'm not very good at working with associative arrays in PHP — zdebruine
– zdebruine, Commented Jan 6, 2017 at 16:56
@Robert the JSON key names specify the row titles, and the key values specify the cell values. — zdebruine
– zdebruine, Commented Jan 6, 2017 at 16:56
I'd recommend splitting up the two issues. One is about creating the table form array $columns. This has nothing to do with JSON file. Second is about populating it. — Robert
– Robert, Commented Jan 6, 2017 at 16:58

Robert · Accepted Answer · 2017-01-06 17:32:13Z

3

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.

answered Jan 6, 2017 at 17:32

Robert

1,9051 gold badge17 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marco Over a year ago

I think it can be more programatic but i vote for Roberts answer.

Collectives™ on Stack Overflow

Use PHP to insert JSON to MySQL table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related