How to update mysql with json array?

Question

I want to make cron job on my server to save my json array every 10 minutes (I want to update mysql table every 10 minutes)

The thing is that i have really nasted array which is currently really hard for me to save alone without your help!

Here is the json array:

{"firstname":{"eur":14.27,"e":0.00612979},"lastname":{"eur":0.18709,"e":2.655e-05},"middlename":{"eur":617.26,"e":1.0}}

My table contain rows for: name (which is the name of the example-> firstname) So i want to update row where is the table name value same as on my example which is "firstname" or "lastname" or "middlename".

I have two more rows which contain "eur" and "e" rows. I must input values from json array to same line row as in the example. I know its bad explained what i want so i will try with better one.

In my SQL i have lines: (where name is already written and must stay same) Name - EUR - E

I want to update every 10 minutes the values of EUR and E where the NAME from sql is the same as in the array data.

Here is my not working PHP CODE:

$filename = "JSON-FILE-LINK";
$data = file_get_contents($filename);  
echo $data;

Thank you so much community!

edit: I bet the sql code must be something like this: UPDATE names SET eur = $eur, e = $e, ... WHERE name = $firstname.

Firstname is the first name of the everyobject. And eur and e is the only values which must be changed.

How to populate json data to sql variables is the biggest problem now for me.

Bill Karwin · Accepted Answer · 2020-04-07 14:27:33Z

2

Here's a demo. I created a table in MySQL's test schema:

mysql> use test;
mysql> create table mytable (name varchar(20), eur numeric(9,2), e numeric(9,2));

I wrote some PHP to decode the JSON into an associative array with json_decode(), and then use the keys and values to insert into the MySQL table I created.

<?php

$pdo = new PDO('mysql:host=127.0.0.1;dbname=test', 'root', 'xxxx');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$data = '{"firstname":{"eur":14.27,"e":0.00612979},"lastname":{"eur":0.18709,"e":2.655e-05},"middlename":{"eur":617.26,"e":1.0}}';

$sql = "INSERT INTO mytable (name, eur, e) VALUES (?, ?, ?)";
$stmt = $pdo->prepare($sql);

foreach(json_decode($data, true) as $name => $values) {
    $stmt->execute([$name, $values['eur'], $values['e']]);
}

I ran the PHP at my bash command line.

php json-demo.php

Here's an SQL query showing the result:

mysql> select * from mytable;
+------------+--------+------+
| name       | eur    | e    |
+------------+--------+------+
| firstname  |  14.27 | 0.01 |
| lastname   |   0.19 | 0.00 |
| middlename | 617.26 | 1.00 |
+------------+--------+------+

For what it's worth, the JSON example you show is not a JSON array, it's a JSON object.

The key/value pairings inside curly braces { } make it an object.

Arrays are comma-separated lists inside square brackets [ ].

edited Apr 7, 2020 at 14:27

answered Apr 6, 2020 at 23:27

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

9 Comments

Bill Karwin Over a year ago

I cannot visualize the change you want. Can you edit your question above and show an example of the data you want to see before and after the update?

Bill Karwin Over a year ago

I think I understand. You don't want to store JSON directly in the database, you want a JSON document in PHP code, and you're asking how to map it into normal columns in the table? Can you please describe your database table more clearly?

Bill Karwin Over a year ago

I edited my answer to write a PHP example of decoding the JSON and inserting it into a MySQL table.

Bill Karwin Over a year ago

You might like to learn about UPDATE or REPLACE or INSERT ON DUPLICATE KEY UPDATE

Bill Karwin Over a year ago

I'm sorry, I have a job and a home to take care of. I answer questions on Stack Overflow, but I am not looking for another project.

|

Collectives™ on Stack Overflow

How to update mysql with json array?

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related