How to insert json object into mysql table

Question

There are many examples around which parse the JSON and then insert the respective fields into MySQL table.

My case is different in a way that I am creating a json at runtime.

my table looks like this:

mysql> describe turkers_data;
+-----------+----------+------+-----+---------+-------+
| Field     | Type     | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| id        | char(36) | NO   | PRI | NULL    |       |
| sentences | json     | NO   |     | NULL    |       |
+-----------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

based on the input received, I build a json using json_encode method in php, which I alredy validated on jsonlint and it is of course valid.

example json:

{
    "opening": "[\"John arrived at Sally's house to pick her up.\",\"John and Sally were going to a fancy restaurant that evening for a dinner.\",\"John was little nervous because he was going to ask Sally to marry him.\"]",
    "first_part": "[\"aa\",\"bb\"]",
    "first_mid": "[\"Waiter shows John and Sally to their table.\"]",
    "mid_part": "[\"cc\",\"dd\"]",
    "mid_late": "[\"John asks Sally, \\\"Will you marry me?\\\"\"]",
    "last_part": "[\"ee\",\"ff\",\"gg\"]"
}

I use following code to insert into mysql table using mysqli

$opening = array("John arrived at Sally's house to pick her up.", "John and Sally were going to a fancy restaurant that evening for a dinner.", "John was little nervous because he was going to ask Sally to marry him.");
$mid_early = array("Waiter shows John and Sally to their table.");
$mid_late = array('John asks Sally, "Will you marry me?"');
$json_data->opening = json_encode($opening);
$json_data->first_part = json_encode($jSentence_1);
$json_data->first_mid = json_encode($mid_early);
$json_data->mid_part = json_encode($jSentence_2);
$json_data->mid_late = json_encode($mid_late);
$json_data->last_part = json_encode($jSentence_3);

$data = json_encode($json_data);
echo($data);


$sql = "INSERT INTO turkers_data (id, sentences)
VALUES ($id, $data)";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();

but it does not work, i get the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"opening":"[\"John arrived at Sally's house to pick her up.\",\"John and Sally w' at line 2

I do not know what is wrong. I could not find much information on how to do this, I read that it is not recommended to have json data dumped as it is into mysql table, but in my case i am unsure of how many sentences are going to there. Also, I believe this serves the purpose for the time being, I plan to just get that JSON from mysql back and process the data in python.

Also pardon me for using json, JSON, MySQL, mysql, I do not know the standard yet.

IncredibleHat · Accepted Answer · 2017-11-08 01:27:05Z

1

You are having a problem with your SQL insert because you have this:

$sql = "INSERT INTO turkers_data (id, sentences) VALUES ($id, $data)";

There is no escaping of quotes on $data, and the $data is not wrapped in single quotes either.

You should build this as a prepared statement and bind the params which will do all that for you:

$sql = "INSERT INTO turkers_data (id, sentences) VALUES (?,?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param('ss', $id, $data );
$stmt->execute();

The above assumes you are using mysqli, and not PDO. If its PDO, this is syntax for PDO method:

$sql = "INSERT INTO turkers_data (id, sentences) VALUES (?,?)";
$stmt = $conn->prepare($sql);
$stmt->execute(array($id, $data));

EDIT

Last ditch effort (AND ILL-ADVISED), if your php and mysql do not support prepared statements (it should!), then you can resort to the old method of wrapping and escaping your fields in the sql build string:

$sql = "INSERT INTO turkers_data (id, sentences) 
        VALUES (
               '". $conn->real_escape_string($id) ."',
               '". $conn->real_escape_string($data) ."'
               )";

But this is NOT ADVISED! If at all costs you should try to get prepared statements to work, or upgrade your PHP, or mysqli extensions.

edited Nov 8, 2017 at 1:27

answered Nov 8, 2017 at 0:47

IncredibleHat

4,1024 gold badges18 silver badges29 bronze badges

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

6 Comments

Adorn Over a year ago

Thank you for the answer. I think it is PDO, I followed a tutorial from w3schools here: w3schools.com/php/php_mysql_insert.asp If I change my code to what you suggested, I get error as

Error: INSERT INTO turkers_data (id, sentences) VALUES (?, ?) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?, ?)' at line 1

IncredibleHat Over a year ago

That makes me think you are using standard mysql methods (not mysqli, note the 'i')... ? Can you confirm?

Adorn Over a year ago

$conn = new mysqli($servername, $username, $password, $dbname);, it is indeed mysqli

IncredibleHat Over a year ago

Ok, then its not PDO... mysqli should handle prepare with the ?, ? in it... thats what prepared statements is all about :D

IncredibleHat Over a year ago

Ah! Good to know. Good luck and have fun coding :)

|

Collectives™ on Stack Overflow

How to insert json object into mysql table

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related