如何使用 PDO 将列从序列化更新为 json? [英] How to update column from serialize to json with PDO?

问题描述

2列name和hobby，都是varchar(255).hobby是serialize(array())的结果 .

2 columns name and hobby,both are varchar(255).hobby is result of serialize(array()) .

id   name    hobby
1    jack    a:2:{i:0;s:8:"swimming";i:1;s:8:"football";}
2    mary    a:2:{i:0;s:5:"music";i:1;s:2:"TV";}  

当我将数据库从 mysql 5.3 更新到 mariadb 10.2 时，我想通过 unserialize() 和 json_encode() 将 hobby 列更新为 json 格式代码>，然后我可以得到:

As I updated database from mysql 5.3 to mariadb 10.2,I want to update hobby column to json format by unserialize() and json_encode(), then I can get:

id   name    hobby
1    jack    ["swimming","football"]
2    mary    ["music","TV"]  

如何使用 PDO 来做到这一点?

How to use PDO to do it?

推荐答案

粗略的方法:

$stmt = $pdo->query('SELECT name, hobby FROM your_table;');

while ($row = $stmt->fetchAll(PDO::FETCH_ASSOC)) {
    $id = $row['id'];

    $unserialized_name = unserialize($row['name']);
    $unserialized_hobby = unserialize($row['hobby']);

    $jsonified_name = json_encode($unserialized_name);
    $jsonified_hobby = json_encode($unserialized_hobby);

    // now the actual UPDATE:
    $stmt = $pdo->prepare('UPDATE your_table SET name = :name, hobby = :hobby WHERE id = :id');

    $stmt->execute([
        'name' => $jsonified_name,
        'hobby' => $jsonified_hobby,
        'id' => $id,
    ]);
}

假设你的桌子一切正常，那应该就可以了.

Assuimg everything is okay with your table, that should do the trick.

这篇关于如何使用 PDO 将列从序列化更新为 json?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！