insert data from JSON into mysql using php

Question

I have an JSON that I would like to insert into mysql database. My JSON is

{"users": { "bert":6.44, "earnie":0.25, "bigbird":34.45 }}

I have a table in mysql called "USERSAMOUNTS" with two coloumns.

The columns are called "USERNAME" and "AMOUNT".

I am trying to insert each name into USERNAME column and each amount into AMOUNT column in one query using a foreach loop and implode. The code I am using is as follows....

$array = json_decode($data, true);
$keys = array_keys($array);                          // get the value of keys
$rows = array();                                     // create a temporary storage for rows
foreach($keys as $key) 
{                                                    // loop through
    $value = $array[$key];                           // get corresponding value
    $rows[] = "('" . $key . "', '" . $value . "')";  // add a row to the temporary storage 
}
$values = implode(",", $rows);           // 'glue' your rows into a query                                    
$hostname = 'localhost';                 // write the rest of your query
$username = 'usersname';
$password = 'userspassword';
try 
{
    $dbh = new PDO("mysql:host=$hostname;dbname=my_database", $username, $password);
    echo 'Connected to database<br />'; // echo a message saying we have connected 
    $count = $dbh->exec("INSERT INTO USERAMOUNTS(USERNAME, AMOUNT) VALUES ($values)"); 
    echo $count;// echo the number of affected rows
    $dbh = null;// close the database connection
}
catch(PDOException $e)
{
    echo $e->getMessage();
}

....my problem is when I run the code all I get is message Connected to database to confirm database connection. No records are inserted and no error messages are shown. Can anyone here point out where I am going wrong and maybe give me a hint as to how to fix the code?

Manu · Accepted Answer · 2014-02-14T13:49:07.580

1

Change your code:

$array = json_decode($data, true);
$rows = array();                                   
foreach($array['users'] as $key => $value)                         
    $rows[] = "('" . $key . "', '" . $value . "')"; 

$values = implode(",", $rows);

edited Feb 14 '14 at 13:49

answered Feb 14 '14 at 13:43

Manu

922
6
16

score 1 · Answer 2 · answered Feb 14 '14 at 13:50

You have a problem at the beginning of your code:

$array = json_decode($data, true);
$keys = array_keys($array);                          // get the value of keys
$rows = array();                                     // create a temporary storage for rows
foreach($keys as $key) 
{                                                    // loop through
    $value = $array[$key];                           // get corresponding value
    $rows[] = "('" . $key . "', '" . $value . "')";  // add a row to the temporary storage 
}

$array is a multi-dimensional array with only one element with the key of users, so you are not looping over your data, but over the outer array. If you enable error display you will already see a warning: PHP Notice: Array to string conversion on the line of:

$rows[] = "('" . $key . "', '" . $value . "')";

You need to loop over the content of your users sub-array:

foreach ($array['users'] as $key => $value) {
  ...

score 0 · Answer 3 · answered Feb 14 '14 at 13:41

0

You do not need brackets in insert

$count = $dbh->exec("INSERT INTO USERAMOUNTS(USERNAME, AMOUNT) VALUES " . $values);

answered Feb 14 '14 at 13:41

cyadvert

855
7
19

Thanks for the pointer - when I replace with your code i only have one record inserted USERNAME has users posted to it and AMOUNT is default 0. – hd-pixel Feb 14 '14 at 13:46
what's the field type of [AMOUNT]? – cyadvert Feb 14 '14 at 13:46
the field type is decimal(16,8) also sorry I don't think i was clear - i don't have a username 'users' - that is at the start of the json object - but i don't need to store that. – hd-pixel Feb 14 '14 at 13:48

score 0 · Answer 4 · answered Feb 14 '14 at 13:49

Initial problem is that your array is not $array, but $array["users"].

Do the following. Replace all that

$keys = array_keys($array);                          // get the value of keys
$rows = array();                                     // create a temporary storage for rows
foreach($keys as $key) 
{                                                    // loop through
    $value = $array[$key];                           // get corresponding value
    $rows[] = "('" . $key . "', '" . $value . "')";  // add a row to the temporary storage 
}
$values = implode(",", $rows);           // 'glue' your rows into a query

with the following:

$values = Array();
foreach($array["users"] as $user=>$amount) {
    $values[] = "('" . $user. "', '" . $amount. "')";
}

Less code - same result.

insert data from JSON into mysql using php

4 Answers4

Linked