使用JSON_INSERT更新表的PDO

Question

我有一个类可以从表中插入、更新和删除项，所以当使用JSON_INSERT时，类不能正常工作，它会给出以下错误：

致命错误: Uncaught : SQLSTATE22032：<>：3140无效JSON文本：“缺少对象成员的名称”。列“Products.p_description”值的位置1。在/Library/WebServer/Documents/Tests/PHP/MySQL-JSON/CRUD.php:182堆栈跟踪中：#0 /Library/WebServer/Documents/Tests/PHP/MySQL-JSON/CRUD.php(182)：PDOStatement->execute() #1 /Library/WebServer/Documents/Tests/PHP/MySQL-JSON/json_update.php(30)：数据库->execute() #2 {main}抛入第182行的/Library/WebServer/Documents/Tests/PHP/MySQL-JSON/CRUD.php中

我在CRUD.php中使用的代码：

public function update($table, $fields = [], $where = null)
{
    $args    = [];
    foreach ($fields as $fk => $fv) :
        $fk = $this->strSafe($fk);
        $args[] .= $fk . ' = :' . $fk;
    endforeach;

    $sql = 'UPDATE ' . $table . ' SET ' . implode(', ', $args);

    if ($where != null) {
        $sql .= ' WHERE ' . $where;
    }

    $this->sql    = $sql;
}

public function execute()
{
    return $this->stmt->execute(); // LINE 182
}

要更新表字段"p_description“并添加值"Qualcomm”的新项“芯片组”，我使用了以下代码json_update.php：

$upMemList = [
    'p_description' => "JSON_INSERT(`p_description` , '$.chipset' , 'Qualcomm')"
];
$upOn = $dbh->update('products', $upMemList, 'id = :id');
$dbh->prepare($upOn);

foreach ($upMemList as $fk => $fv) :
    $dbh->bind(':' . $fk, $fv);
endforeach;
$dbh->bind('id', 1);
$dbh->execute(); // LINE 30

表结构：

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `id` int UNSIGNED NOT NULL,
  `p_name` varchar(50) DEFAULT NULL,
  `p_description` json DEFAULT NULL,
  `p_cat` int NOT NULL,
  `p_price` int NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`id`, `p_name`, `p_description`, `p_cat`, `p_price`) VALUES
(1, 'TV1', '{\"p_usb\": \"2\", \"p_hdmi\": \"3\", \"chipset\": \"Qualcomm\", \"p_screen\": \"21\", \"resolution\": null}', 1, 3400),
(2, 'TV2', '{\"p_usb\": \"3\", \"p_hdmi\": \"3\", \"p_screen\": \"43\", \"resolution\": null}', 1, 4500),
(3, 'Computer1', '{\"p_usb\": \"2\", \"p_hdmi\": \"0\", \"chipset\": \"Qualcomm\", \"p_screen\": \"15\", \"resolution\": null}', 4, 7350),
(4, 'TV3', '{\"ports\": {\"p_usb\": \"3\", \"p_hdmi\": \"2\"}, \"p_screen\": \"65\", \"resolution\": null}', 1, 9900),
(5, 'TV4', '{\"ports\": {\"p_usb\": \"2\", \"p_hdmi\": \"2\"}, \"p_screen\": \"42\", \"resolution\": null}', 1, 3300);

Answer 1

因为您的update方法不返回值，但将查询放在属性中，请尝试

$upMemList = [
    'p_description' => "JSON_INSERT(`p_description` , '$.chipset' , 'Qualcomm')"
];

$dbh->update('products', $upMemList, 'id = :id');
$dbh->prepare($dbh->sql);

foreach ($upMemList as $fk => $fv) :
    $dbh->bind(':' . $fk, $fv);
endforeach;
$dbh->bind('id', 1);
$dbh->execute(); // LINE 30

或者，由于查询已经是类的一个属性，所以将prepare()方法更改为使用$this->sql并准备

由于您的一些行已经包含了chipset，而其他行则不包含，所以我认为使用JSON_SET()会做得更好，后者在JSON文档中插入或更新数据并返回结果。

$upMemList = [
    'p_description' => "JSON_SET(`p_description` , '$.chipset' , 'Qualcomm')"
];