mysql - 如何将json对象插入mysql字段并读回？

Question

我有一个数组，我必须将其转换为 json 变量并将其存储在 mysql 表字段中，我通过以下代码执行此操作:

$arr = array(
        'title_it' => $category->title->attributes()->it,
        'desc_it' => $category->desc->attributes()->it,
        'tags_it' => $category->tags->attributes()->it,

        'title_es' => $category->title->attributes()->es,
        'desc_es' => $category->desc->attributes()->es,
        'tags_es' => $category->tags->attributes()->es,

        'title_fr' => $category->title->attributes()->fr,
        'desc_fr' => $category->desc->attributes()->fr,
        'tags_fr' => $category->tags->attributes()->fr,

        'title_en' => $category->title->attributes()->en,
        'desc_en' => $category->desc->attributes()->en,
        'tags_en' => $category->tags->attributes()->en,

        'title_de' => $category->title->attributes()->de,
        'desc_de' => $category->desc->attributes()->de,
        'tags_de' => $category->tags->attributes()->de
        );
    $params = mysql_real_escape_string(json_encode($arr));

    $query = mysql_query("INSERT INTO category_tags (id, params) VALUES ($id, '$params')") or die("could not connect");

然后我想读取这个字段并只显示属性 title_it 我试过类似的东西:

$query = mysql_query("SELECT * FROM article_tags WHERE id = $id LIMIT 0,1") or die("could not connect");    
$row = mysql_fetch_array($query);
$jsoni = json_encode($row['params']); 

$decoded = json_decode($jsoni, true);
echo $decoded->title_it;

但没有结果。此外，json 以一种奇怪的格式存储。 mysql 字段如下所示:

{"title_it":{"0":"titolo1"},"desc_it":{"0":"descrizione1"},"tags_it":{"0":"tags1"},"title_es": {"0":"titulo1"},"desc_es":{"0":"descripci\u00f3n1"},"tags_es":{"0":"etiquetas1"},"title_fr":{"0":"titre1"},"desc_fr":{"0":"description1"},"tags_fr":{"0":"balises1"},"title_en":{"0":"title1"},"desc_en": {"0":"description1"},"tags_en":{"0":"tags1"},"title_de":{"0":"titel1"},"desc_de":{"0":"beschreibung1"},"tags_de":{"0":"etikett1"}}

那么...将这个json插入mysql字段然后只读取这个字段的参数的正确方法是什么？

Answer 1

问题是当您获取属性值时，SimpleXML 不会返回字符串。当您执行 $category->title->attributes()->it 时，实际上返回的是一个 SimpleXMLElement 对象。它看起来像这样:

object(SimpleXMLElement)#3 (1) {
  [0]=>
  string(3) "Your_Value"
}

在 JSON 中序列化时，它会转换为:{0: "Your_Value"}，这就是您在解码时看到的内容。

将它们添加到数组时需要将它们转换为字符串:

$arr = array(
    'title_it' => (string)$category->title->attributes()->it,
    'desc_it' => (string)$category->desc->attributes()->it,
    'tags_it' => (string)$category->tags->attributes()->it,
    # etc.
);

---

当你得到你的数据时，你不需要再次json_encode，你只需要json_decode它。

$query = mysql_query("SELECT * FROM category_tags WHERE id = $id LIMIT 0,1") or die("could not connect");
$row = mysql_fetch_array($query);

$decoded = json_decode($row['params'], true);
// The ",true" makes it into an array, not an object
echo $decoded['title_it'];