API-call JSON into MySQL

Question

I'm working on a research project where we want to insert a JSON file (from an API-call) into a mysql database. I found multiple examples but I don't know where to begin because there are multiple objects and arrays. A second problem is that the columns and rows are separate arrays (I think?).

Our goal is to fill (daily, hourly, etc) a database that looks likes this (it is an example and we do have multiple items):

-----------------------------------
| Date | Value2 | Value3 | Value4 |
-----------------------------------
| 01-01-2015 | 123 | 1234 | 12345 |
-----------------------------------
| 02-01-2015 | 343443 | 4w3543422 | fref4rw4 | 
-----------------------------------
| 03-01-2015 | 234422r | wrfrw3434 | 2432rfr42324 |
-----------------------------------

Question is how can I get those values from the JSON (which isn't static: sometimes there will be seven days, sometimes less and sometimes more)? Where to begin?

What exactly is it that you want to achieve here? Also, have you tried something? — Andrei
– Andrei, Commented Sep 30, 2015 at 11:55
"because the output from the JSON looks different." Yes, it is, but everything you should know is there. Read that post carefully, try to do it yourself. If you stuck, show us your code, and we could help. — vaso123
– vaso123, Commented Sep 30, 2015 at 13:35

Glarp · Accepted Answer · 2015-10-03 01:15:18Z

1

Code from @Marmik did the trick!

<?php
$array = json_decode($json_data,true);
$sql_inserts = array();
foreach($array['reportitem']['rows']['r'] AS $row)
    {
        $sql_inserts[] = "('".$row[c][0]."','".$row[c][1]."','".$row[c]    [2]."','".$row[c][3]."')";
    }
$insert_values = implode("'",$sql_inserts);
$sql = "INSERT INTO table_name (date,Value2,Value3,Value4) $insert_values         ;";
?>

answered Oct 3, 2015 at 1:15

Glarp

1131 silver badge10 bronze badges

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

Comments

Marmik Bhatt · Accepted Answer · 2015-10-01 08:13:26Z

0

I think using foreach loop after decoding the JSON to PHP Array it can be worked out.

<?php
$array = json_decode($json_data,true);
$sql_inserts = array();
foreach($array['reportitem']['rows']['r'] AS $row)
    {
        $sql_inserts[] = "('".$row[c][0]."','".$row[c][1]."','".$row[c][2]."','".$row[c][3]."')";
    }
$insert_values = implode("'",$sql_inserts);
$sql = "INSERT INTO table_name (date,Value2,Value3,Value4) $insert_values ;";
?>

And this SQL statement is created by JSON array's data.

answered Oct 1, 2015 at 8:13

Marmik Bhatt

5974 silver badges16 bronze badges

Collectives™ on Stack Overflow

API-call JSON into MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related