如何将json对象插入mysql表

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.

Answer 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.