JSON data handling with mySQL and php

Question

For the past 2 days I have been looking over the internet on how to handle data stored as json in mySQL database.All I found was a single article in here which I followed with no luck.So here is my question

This is my table called additional with 2 columns only...jobid and costs. jobid is an int of length 5 and obviously the primary key, costs is simply stored as text. Reason I combined all the costs under one column is because the user in my application can put whatever he/she wants in there, so to me the costs is/are unknown. For example one entry could be

24321 , {"telephone" : "$20"} or 24322 , {"telephone" : "$20", "hotel" : "$400"}

and so on and so forth but I hope you get the point.

Now given this example I need to know how to handle data in and out from the database stored as json using php. So insert, select and update but I think with one given example I can do the rest If someone can help me understand how to handle json data in and out from a database.

Oh and one last thing. Not only I need to know how to fetch the data I need to be able to separate it too e.g:

$cost1 = {"telephone" : "$20"};
$cost2 = {"hotel" : "$400"};

I really hope someone can help with this because like I said above I spent 2 days trying to get my head around this but either no articles on this matter(except the one from this site) or completely irrelevant to my example

have you tried json_encode for save to db... and json_decode after reading from database? — barudo
– barudo, Commented Nov 1, 2016 at 12:37
Which version of MySQL? MySQL 5.7.8 introduced support for a native JSON datatype, you may want to check that out. Here's a good article on the subject: sitepoint.com/use-json-data-fields-mysql-databases — John Corry
– John Corry, Commented Nov 1, 2016 at 12:38
@barudo yes I have with no luck..it simplies displays nothing, oh and this is the article on this site about my question stackoverflow.com/questions/7602204/… — Eilleen
– Eilleen, Commented Nov 1, 2016 at 12:43
@himeshc and jcorry mySQL version is 5.7.16-0ubuntu0.16.04.1 - (Ubuntu) — Eilleen
– Eilleen, Commented Nov 1, 2016 at 12:44

barudo · Accepted Answer · 2016-11-01 13:39:03Z

1

You tagged it as PHP so you can use php functions: json_encode and json_decode.

For example when you read (SELECT) and got this cost value in string corresponding to the primary key 24322:

//after you query db and got the cost in string...
$sql = "SELECT * FROM additional"; 
$result = mysqli_query($conn,$sql); $row = mysqli_fetch_array($result);
//from your comment below.... just changed to $cost so I don't have to change everything here...
$cost = $row['costs'];
//$cost = '{"telephone" : "$20", "hotel" : "$400"}'
//you just have to:
$cost = json_decode($cost); 
// result in an object which you can manipulate such as:
print_r($cost->telephone);
// $20 or:
print_r($cost->hotel);
//$400;
//or if you want to go through all of the costs... you change that to array:
$cost = (array)$cost; //or on your json_decode you add a TRUE param... ie(json_decode($cost, TRUE))...
print_r($cost);
//will produce an associative array: ['telephone'=>'$20', 'hotel'=>'$400']
//for which you can do a foreach if you want to go through each value...

On the other hand when you save to db with an object:

$cost = (object)['hotel'=>'$300', 'taxi'=>'$14'];
//you json_encode this so you can write to db:
$cost = json_encode($cost);
//a string... you can then use $cost to write to db with (insert, update, etc)

Note: json_decode needs the input string to be UTF-8 encoded. So you might need to force your mysql server to provide UTF-8. Some reading: https://www.toptal.com/php/a-utf-8-primer-for-php-and-mysql

Hope this helps...

edited Nov 1, 2016 at 13:39

answered Nov 1, 2016 at 12:52

barudo

6651 gold badge4 silver badges13 bronze badges

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

8 Comments

Eilleen Over a year ago

when $sql = "SELECT * FROM additional"; $result = mysqli_query($conn,$sql); $row = mysqli_fetch_array($result); $data = $row['costs']; and echo $data i get {"telephone" : "$20"} but when i add the line json_decode($data); nothing get echoed anymore

barudo Over a year ago

$data --> is string? does it look like this? '{"telephone" : "$20", "hotel" : "$400"}'

barudo Over a year ago

you can't echo it if you add json_encode... try print_r... or var_dump... reason: its now an object or array.... depending on the data there...

Eilleen Over a year ago

i am using json_decode not encode we are talking about fetching data not inserting data. basically if i echo $data i get the string '{"telephone" : "$20", "hotel" : "$400"}' but when i add json_decode($data) and then echo $data i get nothing

barudo Over a year ago

oops sorry it should be json_decode ... I edited my answer above and go through it...

|

mustafa96m · Accepted Answer · 2016-11-01 12:47:02Z

0

You can use json_encode() and json_decode() throughout your update or insert process.

Basically

json_encode() takes Array and returns JSON as String

json_decode() takes JSON as String and returns Array

http://php.net/manual/en/function.json-encode.php

So in your case whenever you want to update 24321 , {"telephone" : "$20"} you got to decode like

$array = json_decode($row['jsonDataOrWhateverTheColumnNameIs'],true); $array['telephone'] = "40$"; ... ... $jsonString = json_encode($array); // Use this string with your update query.

answered Nov 1, 2016 at 12:47

mustafa96m

3373 silver badges16 bronze badges

1 Comment

Eilleen Over a year ago

like I stated above I have already tried this with no success and I said that in my question and in one my comment with a link to a similar question from this site which suggests your answer...basically the reason why I downvoted you

Collectives™ on Stack Overflow

JSON data handling with mySQL and php

2 Answers 2

8 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related