Reputation: 25

insert data from JSON into mysql using php

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?

Upvotes: 2

Answers (4)

jeroen

Reputation: 91734

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) {
  ...

Upvotes: 1

cyadvert

Reputation: 875

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.

Upvotes: 0

Manu

Reputation: 931

Change your code:

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

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