marios zachariou marios zachariou - 11 months ago 45
JSON Question

JSON data handling with mySQL and php

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

Answer Source

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:
// $20 or:
//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))...
//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:

Hope this helps...