15

php how to store and read json data via mysql? 

mysql_query("INSERT INTO text (data) VALUES (json_encode('id' => $uid, 'value' => yes))");

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"}]

then insert another change to [{"id": "1", "value": "yes"},{"id": "2", "value": "yes"}]...

Or even if a long long value.

[{"id": "1", "value": "yes"},{"id": "2", "value": "yes"}...{"id": "10000", "value": "yes"}]

then update another one, change to [{"id": "1", "value": "yes"},{"id": "2", "value": "yes"}...{"id": "10000", "value": "yes"},{"id": "10001", "value": "yes"}]

I wanna to ask, how to do this mysql query processing more wiser and efficiently? Thanks for more suggestion.

Improve this question
0

4 Answers 4

Reset to default
25

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.

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'))).")");

instead of

mysql_query("INSERT INTO text (data) VALUES (json_encode('id' => $uid, 'value' => yes))");

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

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)

Now, for the RIGHT way to do this:

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

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...

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I believe there's an extra single quote in your first code example
3

You can do this more efficiently by NOT storing JSON in a single field but by creating a proper MySQL table with the JSON object's property names as field names.

Storing a text-string encoded representation of your data in a database completely destroys the point of using databases in the first place.

Improve this answer

5 Comments

MongoDb uses a binary JSON storage format. So I'd have to disagree with your second point that storing data in JSON is a bad idea. For MySQL (and other sql contagions), your point is valid.
The question (and my answer) is specifically about MySQL, and storing data encoded as a JSON string. Furthermore, as you said yourself, MongoDb stores "JSON" as binary objects, not as JSON strings, so storing "JSON" in MongoDb is NOT storing data encoded as a string (which is still bad practice and breaks database normalisation).
i will disagree what if i don't know how many fields are going to be there? therefore best best would be stave all json.
is there any example of saving JSON object in mysql? how can I do that?
Most scenarios are not black and white, and this is absolutely one of them. You should not store array structure in a single field IF each key-value pair would be beneficial, or even avoid painful usage and management, from being a separate row. E.g. more are added/removed often. However, a Json object in a single field most certainly has it's place when the scenario is right. It avoids a potentially pointless table just to store a pairing, as then you have a new table with an ID and value column. Symfony user roles is a good example where a Json field is suitable usage.
3

Yes but....WordPress stores a lot of its data as encoded JSON strings, such as the user capabilities. Storing an array as a discrete bit of data takes away having to do multiple reads on the database and allows you to get a lot of data on one read. If you never see the need to SELECT the individual parts of the JSON string I don't see why it isn't acceptable to do it this way. MySQL must think so too because it has functions to allow a SELECT on the individual fields within a JSON string if you so desired (see MySQL EXPLAIN keyword). But I do agree if you were going to do a lot of SELECTs on a field it should have one of its own. It all depends on how you are going to use the data.

Improve this answer

2 Comments

Wordpress doesn't store lots of data as json strings, he uses the internal serialize/unserialize mechanism :)
@Mathieu - I said it was "encoded JSON" which means using searialize/unserialize. Take a look at the EXPLAIN keyword in MySQL.
1

Assuming you want to store the JSON string as a text or varchar datatype in MySQL, you can simply just escape the JSON string like so:

Storing

$object = new stdClass();
$object->key_a = "Value A";
$object->key_b = "Value B";

$storable_json_string = trim( addslashes( json_encode( $object ) ) );

// `$storable_json_string` contains an ugly invalid JSON
mysqli_query( "INSERT INTO table_a (column_a) VALUES ('{$storable_json_string}')" );

Reading

// read from the database
$result = mysqli_query( "SELECT column_a FROM table_a" );
$row = $result->fetch_object();

// makeover the ugly invalid JSON into a machine readable valid JSON
$valid_json_string = substr( stripslashes( $row->column_a ), 1, -1 );
    
// makes the JSON as a PHP object
$readable_json_object = json_decode( $valid_json_string );

echo $readable_json_object->key_a;    // Value A

This could be not that secure but at least these should give you some stepping stones.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.