-1

Suppose I have a table like the following:

 id| name | family
---+------+---------
 1 | iman | marashi
 2 | mike | ...
 3 | john | ...
 4 |      |

and I also have an Json object to insert to the table like the following:

{"name ":"will","family":"smith"}

How can I insert together an Json object to a table given that the fields of the table and the keys of the Json object are the same?

Without having to parsing the Json object.

I don't want to use this method:

$name = $data["name"]; 
$family = $data["family"];
GMB
  • 216,147
  • 25
  • 84
  • 135
Iman Marashi
  • 5,593
  • 38
  • 51

2 Answers2

1

If you're using PDO, you can provide an associative array to fill in the parameters of a query.

$query = $conn->prepare("INSERT INTO yourTable (name, family) VALUES (:name, :family)");
$data = json_decode($json_object, true);
$query->execute($data);
Barmar
  • 741,623
  • 53
  • 500
  • 612
0

I think that Barmar provided the correct answer if you are running PDO.

For the sake a completeness, you could also do this purely with MySQL, using json_extract() to read directly from the json string given as parameter:

insert into mytable (name, family) values(
    json_unquote(json_extract(:js, '$.name')),
    json_unquote(json_extract(:js, '$.family'))
);

Demo on DB Fiddle:

create table mytable (
    id int auto_increment primary key, 
    name varchar(50), 
    family varchar(50)
):

insert into mytable (name, family) values(
    json_unquote(json_extract('{"name":"will","family":"smith"}', '$.name')),
    json_unquote(json_extract('{"name":"will","family":"smith"}', '$.family'))
);

select * from mytable;

id | name | family
-: | :--- | :-----
 1 | will | smith
GMB
  • 216,147
  • 25
  • 84
  • 135
  • no need to provide an answer here i think this one also covered me, besides the `PDO` one. I think this way is better given the proper MySQL server version. –  Feb 10 '20 at 22:14