Insert values from JSON into database

Question

I have a JSON array that is on a page. It is added to as time goes on by some user interaction. When the user is done they need to submit this array to a page where their information is added to a mySQL table via php. My call to the page via AJAX is as follows where answersArray is the array

$.ajax({
    type: "POST",
    dataType: "json",
    data: answersArray,
    url: 'sendToUsersFeedback.php',
});

The array looks like this:

[
    {
        "USERID": "3",
        "INDVID": "0",
        "RATING": "1",
        "CONFIDENCE": "8"
    },
    {
        "USERID": "3",
        "INDVID": "1",
        "RATING": "1",
        "CONFIDENCE": "88"
    }
]

This is where I get confused. I need to decode this incoming json and then loop through it added a new record for each array item (USERID, INDVID, RATING and CONFIDENCE make up one record etc..) I could have as many as 20 in this array. I know USERID is not unique. Already have that set up.

It is the php side I get messed up on. How do I decode an incoming array and go through it. I have tried the following

$sql = json_decode(data,true);
foreach( $data as $row ) {
    $sql[] = '("'.mysql_real_escape_string($row['USERID']).'", '.$row['INDVID'].','.$row['RATING'].','.$row['CONFIDENCE'].')';
}
mysql_query('INSERT INTO users_feedback (USERID, INDVID, RATING. CONFIDENCE) VALUES '.implode(',', $sql));

Am I close? I'm very confused. Thanks in advance.

You're sending json directly to PHP, but never declaring a $_POST variable name. Should probably look at using 'data':{'data':ansersArray} then change the PHP to use json_decode($_POST['data'],true). — Brad Christie
– Brad Christie, Commented Sep 11, 2012 at 19:27
Brad, still doesnt like the $_POST['data']. Tell me it isnt valid. I changed the js code to show $('.submitAll').click(function() { $.ajax({ type: "POST", dataType: "json", data:{'data':answersArray}, url: 'sendToUsersFeedback.php', }); }); — jeynon
– jeynon, Commented Sep 11, 2012 at 20:11

Alain · Accepted Answer · 2012-09-11 19:34:41Z

1

First, give a name to your data you're posting :

$.ajax({
    type: "POST",
    dataType: "json",
    data: {
      my_data: answersArray
    },
    url: 'sendToUsersFeedback.php',
});

Then, in your PHP code, recover your data :

$data = $_POST['my_data'];
$sql = json_decode($data, true);
...

You should be aware that everybody can edit JSON in the client-side, so insert data into a database this way is extremely dangerous.

answered Sep 11, 2012 at 19:34

Alain

37.1k18 gold badges128 silver badges160 bronze badges

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

6 Comments

jeynon Over a year ago

this seems to be the hang up. Keeps telling the that $data is undefined. I know the JSON object is legit. Why isnt it coming over?

jeynon Over a year ago

$('.submitAll').click(function() { $.ajax({ type: "POST", dataType: "json", data:{'data':answersArray}, url: 'sendToUsersFeedback.php', }); });

Alain Over a year ago

where is your answersArray declared?

jeynon Over a year ago

wrestore.iupui.edu/Jon/tool.php Take a look. Scroll down past the table. When you hit the Next button or the Back you will see the answersArray appear. Hitting Submit will fire the script. Thanks much for your help.

Alain Over a year ago

Ham, you're using answersArray which is not declared. Replace data:{'data':answersArray}, by data:{'data':$('#JSONHolder').html()},

|

Claudiu · Accepted Answer · 2012-09-11 19:34:37Z

You are doing:

$sql = json_decode(data,true);

What is data? You probably missed the dollar sign. Are you actually getting your JSON in $data?

You're assigning json_decode() result to $sql; now your $sql variable will contain an associative array with your $data. $data is still a string because you didn't change it's value since retrieving it.

Then you're looping through the string (not happening) and running:

mysql_query('INSERT INTO users_feedback (USERID, INDVID, RATING. CONFIDENCE) VALUES '.implode(',', $sql));

$sql above will be your JSON, as a PHP array, something like:

array(array('USERID' => 1 ...), array('USERID' => 2 ...));

You should have:

$data = json_decode($data, true);
$sql = array();
foreach ($data as ...

Also, make sure you actually retrive $data:

$data = $_POST['data']

Luigi Siri · Accepted Answer · 2012-09-11 19:47:03Z

0

Simple, you have the foreach using the $data array that it's from the json code.

You must set the decoded array as another variable and loop with it.

$j_decoded = json_decode(data,true);

foreach(j_decoded as $row ) {
$sql[] = '("'.mysql_real_escape_string($row['USERID']).'", '.$row['INDVID'].','.$row['RATING'].','.$row['CONFIDENCE'].')';
}
mysql_query('INSERT INTO users_feedback (USERID, INDVID, RATING. CONFIDENCE) VALUES '.implode(',', $sql));

Hope this helps you.

answered Sep 11, 2012 at 19:47

Luigi Siri

2,0182 gold badges19 silver badges29 bronze badges

Collectives™ on Stack Overflow

Insert values from JSON into database

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related