Inserting record into database using JSON

Question

Following is my code

<?php
$json = '{"apples":"green","bananas":"yellow"}';
$var=(json_decode($json, true));
print_r($var);

$username = "root";
$password = "";
$hostname = "localhost"; 

//connection to the eventbase
$dbhandle = mysql_connect($hostname, $username, $password) 
  or die("Unable to connect to MySQL");
echo "Connected to MySQL<br>";

//select a eventbase to work with
$selected = mysql_select_db("json",$dbhandle) 
  or die("Could not select json");

// Insert $event array into eventbase
    foreach ($json as $key => $value) {
    $db_insert = mysql_query("INSERT INTO fruits (fruit,color) VALUES" . $value);
    mysql_query($db_insert);
    if (!$db_insert)
    {
    die('Could not connect - event insert failed: ' . mysql_error());
    }
    }
?>

New to JSON and PHP, please post any changes I can do to insert those records into MySQL.

However, another part remains. I want to save the JSON encoded data into MySQL in string format. How can I do so?

score 0 · Answer 1 · answered Feb 07 '14 at 08:43

I think you havn't used the correct variable in the for loop. After json decode you used the same variable in the for loop. (you should use $var instead of $json) i guess.

<?php
$json = '{"apples":"green","bananas":"yellow"}';
$var=(json_decode($json, true));
//print_r($var);

$username = "root";
$password = "";
$hostname = "localhost"; 

//connection to the eventbase
$dbhandle = mysql_connect($hostname, $username, $password) 
  or die("Unable to connect to MySQL");
echo "Connected to MySQL<br>";

//select a eventbase to work with
$selected = mysql_select_db("json",$dbhandle) 
  or die("Could not select json");

// Insert $event array into eventbase
    foreach ($var as $key => $value) {
    $db_insert = mysql_query("INSERT INTO fruits (fruit,color) VALUES" . $value);
    mysql_query($db_insert);
    if (!$db_insert)
    {
    die('Could not connect - event insert failed: ' . mysql_error());
    }
    }
?>

Error - Could not connect - event insert failed: Query was empty — Caffeine Coder, Feb 07 '14 at 09:36

score 0 · Accepted Answer · edited May 25 '19 at 13:58

0

Use $var as below,

foreach ($var as $fruit => $color) {
    $db_insert = mysql_query("INSERT INTO fruits (fruit,color) VALUES ('$fruit','$color')");
    .....

Note: Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

edited May 25 '19 at 13:58

Zoe

27,060
21
118
148

answered Feb 07 '14 at 08:46

Rikesh

26,156
14
79
87

do you mean `$var as $fruit => $color`? – Nikita U. Feb 07 '14 at 08:48
Error - Parse error: syntax error, unexpected 'mysql_query' (T_STRING) in simple.php on line 22 – Caffeine Coder Feb 07 '14 at 09:37
there is nothing more to it except a db which consists of 2 fields , fruit and color . – Caffeine Coder Feb 07 '14 at 09:52
It is passing empty fields into db , n dis is coming right now - Notice: Undefined variable: fruit in /opt/lampp/htdocs/example/simple1.php on line 21 Notice: Undefined variable: color in /opt/lampp/htdocs/example/simple1.php on line 21 – Caffeine Coder Feb 07 '14 at 10:05
Are you sure you change foreach as I mention ? – Rikesh Feb 07 '14 at 10:07
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/47002/discussion-between-rikesh-and-caffeine-coder) – Rikesh Feb 07 '14 at 10:07
how can i save the json encoded data back again into db ? – Caffeine Coder Feb 07 '14 at 13:15
I'm sorry but I will suggest you to ask a different question for that. – Rikesh Feb 07 '14 at 13:17
did it http://stackoverflow.com/questions/21629922/inserting-json-encoded-data-into-mysql as you requested – Caffeine Coder Feb 07 '14 at 14:13

Inserting record into database using JSON

2 Answers2

Linked