CODEWITHSUNDEEP
phpmysqljsonodk
Jason
Jason

Reputation: 311

Decoding JSON in PHP to insert into mySQL

I am writing a php file to get data from ODK Aggregate (a survey management program) into a mySQL server.

So far, I am able to get data from ODK into mySQL, but that data is not being decoded correctly.

From an initial database that looks like this (exported from ODK):

    name            age     meta:instanceID
    Jason           25      uuid:a2f38e2a-e74b-4bec-aae6-8e1fe6a5d9d8
    Chris           42      uuid:6df5ce55-1398-4588-a852-a73b4cdebebe
    Applebee        72      uuid:4957be51-c640-4c24-97ee-3036511992f9
    Herbert         24      uuid:676916a9-0887-46ea-af45-65a9f812e5fe

(Note: this the the manual export from ODK. In fact, the JSON file that is getting decoded in the php file comes from their automatic "publish" export, and has a few more fields, all of which are null. The error log below includes that JSON data).

I am ending up with a mySQL database that looks like this:

    id          name        age         instanceID
    1           [null]      0           [null]
    2           r           0           r
    3           S           0           S
    4           [null]      0           [null]
    5           [null]      0           [null]
    6           [null]      0           [null]
    7           r           0           r
    8           S           0           S
    9           [null]      0           [null]
    10          [null]      0           [null]
    11          [null]      0           [null]
    12          r           0           r
    13          S           0           S
    14          [null]      0           [null]
    15          [null]      0           [null]
    16          [null]      0           [null]
    17          r           0           r
    18          S           0           S
    19          [null]      0           [null]
    20          [null]      0           [null]

I'm assuming the issue is with how the JSON file is being decoded. Here's my php (with server detailed replaced by *s):

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Decode JSON
$jsonData = file_get_contents('php://input');
$phpArray = json_decode($jsonData, true);

// Connect to databse
$dbhost = '*';
$dbuser = '*';
$dbpass = '*';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}

mysql_select_db('dbTest');

// Insert data
foreach($phpArray as $item) {
       $mysql_query = ("INSERT INTO simple_survey (name, age, instanceID) 
       VALUES ('".$item['name']."', '".$item['age']."', '".$item['instanceID']."')"); 

       $retval = mysql_query( $mysql_query, $conn ); //run the query
        if(! $retval )
        {
          die('Could not enter data: ' . mysql_error()); 
        }
     }          

// Check to see if it worked
echo "Entered data successfully\n";

// Close connection
mysql_close($conn);

?>

And here is my error log (with time and file names replaced by *s):

[*time*] {"token":"","content":"record","formId":"SimpleSurvey","formVersion":"","data":[{"*meta-instance-id*":"uuid:a2f38e2a-e74b-4bec-aae6-8e1fe6a5d9d8","*meta-model-version*":null,"*meta-ui-version*":null,"*meta-submission-date*":"2014-09-15T13:35:09.751Z","*meta-is-complete*":true,"*meta-date-marked-as-complete*":"2014-09-15T13:35:09.751Z","name":"Jason","age":25,"instanceID":"uuid:a2f38e2a-e74b-4bec-aae6-8e1fe6a5d9d8"}]}
[*time*] PHP Notice:  Undefined variable: name in *file* on line 26
[*time*] PHP Notice:  Undefined variable: age in *file* on line 26
[*time*] PHP Notice:  Undefined variable: instanceID in *file* on line 26
[*time*] PHP Notice:  Undefined variable: name in *file* on line 26
[*time*] PHP Notice:  Undefined variable: age in *file* on line 26
[*time*] PHP Notice:  Undefined variable: instanceID in *file* on line 26
[*time*] PHP Notice:  Undefined variable: name in *file* on line 26
[*time*] PHP Notice:  Undefined variable: age in *file* on line 26
[*time*] PHP Notice:  Undefined variable: instanceID in *file* on line 26
[*time*] PHP Notice:  Undefined variable: name in *file* on line 26
[*time*] PHP Notice:  Undefined variable: age in *file* on line 26
[*time*] PHP Notice:  Undefined variable: instanceID in *file* on line 26
[*time*] PHP Notice:  Undefined variable: name in *file* on line 26
[*time*] PHP Notice:  Undefined variable: age in *file* on line 26
[*time*] PHP Notice:  Undefined variable: instanceID in *file* on line 26
[*time*] {"token":"","content":"record","formId":"SimpleSurvey","formVersion":"","data":[{"*meta-instance-id*":"uuid:6df5ce55-1398-4588-a852-a73b4cdebebe","*meta-model-version*":null,"*meta-ui-version*":null,"*meta-submission-date*":"2014-09-15T13:35:13.803Z","*meta-is-complete*":true,"*meta-date-marked-as-complete*":"2014-09-15T13:35:13.803Z","name":"Chris","age":42,"instanceID":"uuid:6df5ce55-1398-4588-a852-a73b4cdebebe"}]}
[*time*] PHP Notice:  Undefined variable: name in *file* on line 26
[*time*] PHP Notice:  Undefined variable: age in *file* on line 26
[*time*] PHP Notice:  Undefined variable: instanceID in *file* on line 26
[*time*] PHP Notice:  Undefined variable: name in *file* on line 26
[*time*] PHP Notice:  Undefined variable: age in *file* on line 26
[*time*] PHP Notice:  Undefined variable: instanceID in *file* on line 26
[*time*] PHP Notice:  Undefined variable: name in *file* on line 26
[*time*] PHP Notice:  Undefined variable: age in *file* on line 26
[*time*] PHP Notice:  Undefined variable: instanceID in *file* on line 26
[*time*] PHP Notice:  Undefined variable: name in *file* on line 26
[*time*] PHP Notice:  Undefined variable: age in *file* on line 26
[*time*] PHP Notice:  Undefined variable: instanceID in *file* on line 26
[*time*] PHP Notice:  Undefined variable: name in *file* on line 26
[*time*] PHP Notice:  Undefined variable: age in *file* on line 26
[*time*] PHP Notice:  Undefined variable: instanceID in *file* on line 26
[*time*] {"token":"","content":"record","formId":"SimpleSurvey","formVersion":"","data":[{"*meta-instance-id*":"uuid:4957be51-c640-4c24-97ee-3036511992f9","*meta-model-version*":null,"*meta-ui-version*":null,"*meta-submission-date*":"2014-09-17T07:34:42.785Z","*meta-is-complete*":true,"*meta-date-marked-as-complete*":"2014-09-17T07:34:42.785Z","name":"Applebee","age":72,"instanceID":"uuid:4957be51-c640-4c24-97ee-3036511992f9"}]}
[*time*] PHP Notice:  Undefined variable: name in *file* on line 26
[*time*] PHP Notice:  Undefined variable: age in *file* on line 26
[*time*] PHP Notice:  Undefined variable: instanceID in *file* on line 26
[*time*] PHP Notice:  Undefined variable: name in *file* on line 26
[*time*] PHP Notice:  Undefined variable: age in *file* on line 26
[*time*] PHP Notice:  Undefined variable: instanceID in *file* on line 26
[*time*] PHP Notice:  Undefined variable: name in *file* on line 26
[*time*] PHP Notice:  Undefined variable: age in *file* on line 26
[*time*] PHP Notice:  Undefined variable: instanceID in *file* on line 26
[*time*] PHP Notice:  Undefined variable: name in *file* on line 26
[*time*] PHP Notice:  Undefined variable: age in *file* on line 26
[*time*] PHP Notice:  Undefined variable: instanceID in *file* on line 26
[*time*] PHP Notice:  Undefined variable: name in *file* on line 26
[*time*] PHP Notice:  Undefined variable: age in *file* on line 26
[*time*] PHP Notice:  Undefined variable: instanceID in *file* on line 26
[*time*] {"token":"","content":"record","formId":"SimpleSurvey","formVersion":"","data":[{"*meta-instance-id*":"uuid:676916a9-0887-46ea-af45-65a9f812e5fe","*meta-model-version*":null,"*meta-ui-version*":null,"*meta-submission-date*":"2014-09-17T07:35:02.672Z","*meta-is-complete*":true,"*meta-date-marked-as-complete*":"2014-09-17T07:35:02.672Z","name":"Herbert","age":24,"instanceID":"uuid:676916a9-0887-46ea-af45-65a9f812e5fe"}]}
[*time*] PHP Notice:  Undefined variable: name in *file* on line 26
[*time*] PHP Notice:  Undefined variable: age in *file* on line 26
[*time*] PHP Notice:  Undefined variable: instanceID in *file* on line 26
[*time*] PHP Notice:  Undefined variable: name in *file* on line 26
[*time*] PHP Notice:  Undefined variable: age in *file* on line 26
[*time*] PHP Notice:  Undefined variable: instanceID in *file* on line 26
[*time*] PHP Notice:  Undefined variable: name in *file* on line 26
[*time*] PHP Notice:  Undefined variable: age in *file* on line 26
[*time*] PHP Notice:  Undefined variable: instanceID in *file* on line 26
[*time*] PHP Notice:  Undefined variable: name in *file* on line 26
[*time*] PHP Notice:  Undefined variable: age in *file* on line 26
[*time*] PHP Notice:  Undefined variable: instanceID in *file* on line 26
[*time*] PHP Notice:  Undefined variable: name in *file* on line 26
[*time*] PHP Notice:  Undefined variable: age in *file* on line 26
[*time*] PHP Notice:  Undefined variable: instanceID in *file* on line 26

And the error message I get from the php file itself is: Invalid argument supplied for foreach () in file on line 24.

What do I need to change in my php to make the data import correctly into mySQL?

Upvotes: 1

Views: 252

Answers (2)

heiglandreas
heiglandreas

Reputation: 3861

Seems like $phpArray does not contain what you expect it to contain.

I'd do some 'poor man debugging' and include a var_Dump($phpArray); right beneath the json_decode to see what's in it!

Upvotes: 1

user2289510
user2289510

Reputation:

Reading your json it seems like you are trying to access in wrong way

Change this

foreach($phpArray as $item) {

to the correct form

foreach($phpArray['data'] as $item) {

and ofc dont forget to validate $phpArray

Upvotes: 2

Related Questions