gpt4 book ai didi

php - 如何将json对象插入到mysql表中

转载 作者:行者123 更新时间:2023-11-30 21:21:43 24 4
gpt4 key购买 nike

有很多关于解析 JSON 然后将相应字段插入到 MySQL 表的示例。

我的情况有所不同,因为我在运行时创建了一个 json。

我的表格是这样的:

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)

根据收到的输入,我在 php 中使用 json_encode 方法构建了一个 json,我已经在 jsonlint< 上对其进行了验证 当然是有效的。

示例 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\"]"
}

我使用以下代码使用 mysqli 插入到 mysql 表中

$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();

但它不起作用,我收到错误:

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

不知道哪里出了问题。我找不到太多关于如何执行此操作的信息,我读到不建议将 json 数据转储到 mysql 表中,但在我的情况下我我不确定那里有多少句子。另外,我相信这暂时达到了目的,我打算从 mysql 中获取 JSON 并在 python 中处理数据.

另请原谅我使用了jsonJSONMySQLmysql,我不知道标准还没有。

最佳答案

你的 SQL 插入有问题,因为你有这个:

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

$data 没有引号转义,$data 也没有用单引号引起来。

您应该将其构建为准备好的语句并绑定(bind)将为您完成所有操作的参数:

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

以上假设您使用的是 mysqli,而不是 PDO。如果是 PDO,这是 PDO 方法的语法:

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

编辑

最后的努力(和错误的建议),如果你的 php 和 mysql 不支持准备好的语句(它应该!),那么你可以求助于在 sql 构建字符串中包装和转义你的字段的旧方法:

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

但不建议这样做!如果不惜一切代价,您应该尝试让准备好的语句工作,或者升级您的 PHP 或 mysqli 扩展。

关于php - 如何将json对象插入到mysql表中,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/47169624/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com