0

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

Improve this question
7
  • 1
    have you tried json_encode for save to db... and json_decode after reading from database? 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 Commented Nov 1, 2016 at 12:38
  • Which mysql version you are using ? Commented Nov 1, 2016 at 12:42
  • @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/… Commented Nov 1, 2016 at 12:43
  • @himeshc and jcorry mySQL version is 5.7.16-0ubuntu0.16.04.1 - (Ubuntu) Commented Nov 1, 2016 at 12:44

2 Answers 2

Reset to default
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...

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

8 Comments

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
$data --> is string? does it look like this? '{"telephone" : "$20", "hotel" : "$400"}'
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...
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
oops sorry it should be json_decode ... I edited my answer above and go through it...
|
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.

Improve this answer

1 Comment

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.