PHP如何通过MySQL存储和读取JSON数据? [英] php how to store and read json data via mysql?
问题描述
php如何通过mysql存储和读取json数据?
php how to store and read json data via mysql?
mysql_query("INSERT INTO text (data) VALUES (json_encode('id' => $uid, 'value' => yes))");
然后，如何更新data值?读出数据，然后将其插入json_encode并进行解码过程，还是仅通过简单的方式进行更新?
then, how to update data value? read out the data, then insert it with an json_encode and decode process, or only easy way update?
[{"id": "1", "value": "yes"}]
然后在[{"id": "1", "value": "yes"},{"id": "2", "value": "yes"}] ...
或者即使是长整型值.
[{"id": "1", "value": "yes"},{"id": "2", "value": "yes"}...{"id": "10000", "value": "yes"}]
然后更新另一个，更改为[{"id": "1", "value": "yes"},{"id": "2", "value": "yes"}...{"id": "10000", "value": "yes"},{"id": "10001", "value": "yes"}]
then update another one, change to [{"id": "1", "value": "yes"},{"id": "2", "value": "yes"}...{"id": "10000", "value": "yes"},{"id": "10001", "value": "yes"}]
我想问一下，如何更明智，更有效地执行此mysql查询处理?感谢您的更多建议.
I wanna to ask, how to do this mysql query processing more wiser and efficiently? Thanks for more suggestion.
推荐答案
从技术上讲，您这样做的方式是错误的. MySQL用于分别存储每个ID/VALUE.为了不更改您的代码，我们将首先查看您的解决方案，但随后我将解释这样做的更好"方法.
Technically, you are going the wrong way with that. MySQL is used to store each of your ID/VALUE seperately. For the sake of NOT changing your code we'll look at your solution first but then i'll explain the "better" way of doing it.
首先，您需要将JSON作为变量而不是SQL的一部分:
First, you need to make your JSON as a variable, not part of your SQL:
mysql_query("INSERT INTO text (data) VALUES (".mysql_real_escape_string(array(json_encode('id' => $uid, 'value' => 'yes'))).")");
代替
mysql_query("INSERT INTO text (data) VALUES (json_encode('id' => $uid, 'value' => yes))");
这第一部分将允许您至少将数据正确地至少放入mysql中. 我正在假设您的表具有ID，并且您将使用它来更新或删除
This first part will allow you to at least instead the data correctly into mysql. I am ASSUMING your table has an ID and that you will be using it to update or delete
检索数据时，可以对$ row ['data']进行json_decode，以便从该行取回数据并进行处理.要更新它，只需执行以下操作:
When you retrieve your data, you can json_decode the $row['data'] to get your data back from the row and work with it. To update it, just do:
mysql_query("UPDATE text SET data = "'.mysql_real_escape_string(json_encode($myJsonToBeData)).'" WHERE rowid = '.$myrowid)
现在，以正确的方式执行此操作:
执行此操作的正确方法是将以下字段包含在表中:ID，JSONID，JSONVALUE并改用此SQL:
The right way to do this would be to have these fields into your table: ID, JSONID, JSONVALUE and use this SQL instead:
SELECT * FROM text WHERE id = $rowid
INSERT INTO text VALUES(NULL, $jsonid, $jsonvalue)
UPDATE text SET jsonid = $jsonid, jsondata = $jsondata
SELECT * FROM text WHERE id = $rowid
INSERT INTO text VALUES(NULL, $jsonid, $jsonvalue)
UPDATE text SET jsonid = $jsonid, jsondata = $jsondata
这是非常基础的，但是它将允许您在数据库中具有任意数量的条目，使其可搜索，索引，可排序，可查询等.
This is pretty basic, but it will allow you to have any number of entries in your database that make it searchable, indexed, sortable, queryable, etc...
这篇关于PHP如何通过MySQL存储和读取JSON数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
