如何使用PHP Mysql查询在mysql表中获取json值

I have a database table which has one of its column containing a json value. I want to know how i use php mysql query to get the value of "title" and "name" from the "elements" column.

我有一个数据库表,其中一列包含一个json值。我想知道我如何使用php mysql查询从“elements”列中获取“title”和“name”的值。

id | name | elements
---------------------
 1 | Dan  | { 
                "book": { 
                "0": { "title": "The Oblivion of Hope"  }           
                },
                "author": {
                 "0": { "name": "David Addoteye" },
                 "1": { "date": "2015-93-13"}
                 }
            }

elements => book => 0 => title

elements => book => 0 => title

elements => author=> 0 => name

elements => author => 0 => name

I will be glad if someone can help me solve this, thank you.

如果有人能帮助我解决这个问题,我将很高兴,谢谢。

2 个解决方案

#1

Fetch the entry from the database and use json_decode() to turn it into an associative array.

从数据库中获取条目并使用json_decode()将其转换为关联数组。

http://repl.it/msZ

Some example code:

一些示例代码:

// Equivalent of obtained from DB query.
$fetched_value = '{"book":{"0":{"title":"The Oblivion of Hope"}},"author":{"0":{"name":"David Addoteye"},"1":{"date":"2015-93-13"}}}';

$decoded = json_decode($fetched_value, true);

echo $decoded['book'][0]['title'];
echo $decoded['author'][0]['name'];

#2

Once you get record $row from the database:

从数据库中获取记录$ row后:

$json = json_decode($row['elements']);
echo $json->book->{0}->title;

http://ideone.com/9yV33F

#1