Inset php json encoded array into database

Question

I need to insert elements of a php encoded array into a database and I'm completely stuck. I have first used json encode to to grab data from the database using an SQL query (which I did successfully) but I now need to be able to do the opposite. If anyone could help I'd greatly appreciate it. It's my second day of work and I'm not doing so well. The following is my code:

    $UserCoords_Query  = "Select Accuracy, Longitude, Latitude, Timestamp                            
        FROM UserDetails
          WHERE UserId =" . $UserID;
                  $UserCoords_result = mysql_query($UserCoords_Query);

if (mysql_num_rows($UserCoords_result) == 0) {
    echo "There are no users with an id of ". $UserID;
}

else {
            $EmptyArray=array();
    while ($row = mysql_fetch_array($UserCoords_result)) {
        $Accuracy=$row['Accuracy'];
        $Longitude= $row['Longitude'];
        $Latitude=$row['Latitude'];
        $Timestamp= $row['Timestamp'];

        $Queue= array('Accuracy:' => $Accuracy, 'Latitude' => $Latitude, 'Longitude' => $Longitude, 'Timestamp' => $Timestamp); 
        array_unshift($EmptyArray,$Queue);
}   

    $ObjectResponse = array('Coords' => $EmptyArray);
    echo json_encode($ObjectResponse);

    $Json_Encoded= json_encode($ObjectResponse);

   $Json_Decoded= json_decode($Json_Encoded, true);

    $Update_Query= "INSERT INTO UserDetails (UserId, Accuracy, Latitude, Longitude,         Timestamp)
    VALUES ('".$UserID."','".$Json_Decoded[0]        ['Accuracy']."','".$Json_Decoded[0]['Latitude']."',
    '".$Json_Decoded[0]['Longitude']."','".$Json_Decoded[0]['Timestamp']."')";

    mysql_query($Update_Query) or die(mysql_error());

you shouldn't be inserting json text into a db, unless you plan to NEVER access any individual sub-data from the json text with sql. json's a transport format, not a storage format. — Marc B
– Marc B, Commented May 8, 2013 at 16:05
@ Marc B Well I'd need to explode the string to get the elements and add them to the DB. I thought that was what the json decode function does? — user2363025
– user2363025, Commented May 8, 2013 at 16:24

dhavald · Accepted Answer · 2013-05-08 18:16:10Z

1

I agree with chandresh_cool. you should use 'true' option to decode the json encoded string as array, otherwise it will return an object.

moreover, file_get_contents() expects a filename (with full or relative path). When you try to give a json_encoded string, it thinks that its the file name and it will try to open it as a file, which, obviously does not exist, and thus throws an error. Try to give an existing filename and see that solves the problem.

P.s. I know this should be a comment, but due to insufficient points, I cannot comment

answered May 8, 2013 at 18:16

dhavald

5644 silver badges12 bronze badges

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

14 Comments

user2363025 Over a year ago

@ dhavald I have edited the code to include the true option and removed the file get contents function. However, I am still receiving the error:Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in /home/roseanne/public_html/display_Coords.php on line 26

user2363025 Over a year ago

No because the query involves using variables from the decoded array but I have some mistake involving that decode.

user2363025 Over a year ago

@ dhavald If it helps, I was using the following link as a guideline. But it didn't work. stackoverflow.com/questions/14736880/…

user2363025 Over a year ago

@ dhavald That produces this??: array(1) { ["Coords"]=> array(2) { [0]=> array(4) { ["Accuracy"]=> string(2) "56" ["Latitude"]=> string(2) "78" ["Longitude"]=> string(2) "89" ["Timestamp"]=> string(3) "909" } [1]=> array(4) { ["Accuracy"]=> string(2) "56" ["Latitude"]=> string(2) "78" ["Longitude"]=> string(2) "89" ["Timestamp"]=> string(3) "909" } } }

user2363025 Over a year ago

@ dhavald Do you know how I could access these elements? In my query I'm using $Json_Decoded[0]['Accuracy'] etc., but that obviously isn't working

|

chandresh_cool · Accepted Answer · 2013-05-08 16:03:48Z

0

You need to set second parameter of json_encode as true to return array

$Json_Decoded = json_decode($Json_Encoded, true);

answered May 8, 2013 at 16:03

chandresh_cool

11.9k3 gold badges32 silver badges46 bronze badges

2 Comments

user2363025 Over a year ago

Thanks, I tried that. However, I'm still getting an error of "Warning: file_get_contents({"Coords":[{"Accuracy:":"56","Latitude":"78","Longitude":"89","Timestamp":"909"}]}): failed to open stream: No such file or directory in /home/roseanne/public_html/display_Coords.php on line 48"

user2363025 Over a year ago

@ chandresh_cool this is the only error I get. Line 48 is this line: $Json_Data = file_get_contents($Json_Encoded);

Collectives™ on Stack Overflow

Inset php json encoded array into database

2 Answers 2

14 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

14 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related