使用mySQL和php进行JSON数据处理

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

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