3

This is a follow up question , earlier i had asked about inserting json into mysql . I encoded it again and now i want it to be printed back to mysql . I don't know how am i supposed to print the encoded json output as string back into mysql . Folowing is my current code

<?php
$json = array
   (
   array("pineapple","yellow"),
   array("watermelon","red"),
   array("orange","orange")
   );
var_dump($json);
var_dump(json_decode($json, true));

$newelements = json_encode( $json, JSON_FORCE_OBJECT | JSON_UNESCAPED_UNICODE );
echo $newelements;

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


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


$selected = mysql_select_db("json",$dbhandle) 
  or die("Could not select json");


  //  foreach ($enc as $fruit => $color) {

    $db_insert = mysql_query("INSERT INTO fruits (fruit,color) VALUES('$fruit','$color')");
    mysql_query($db_insert);


    if (!$db_insert)
    {
    die('Could not connect - event insert failed: ' . mysql_error());
    }
  //  }
?>

Any help would be much appreciated .Thanks in advance :)

Community
  • 1
  • 1
Caffeine Coder
  • 1,869
  • 1
  • 17
  • 35
  • 1
    As I understand you want to add JSON text to the database? That is just a string so you can add it to a (var)char column. – Ruben Feb 07 '14 at 14:17
  • do i need to define an extra column in db for that ? Can u show me exactly how that's gonna happen , bit of a newbie to json . – Caffeine Coder Feb 07 '14 at 14:19
  • Careful with SQL injections! One can put whatever one wants in $fruit or $color here. – Pierre Arlaud Feb 07 '14 at 14:19
  • I know , but this is just for my learning purpose . I will start using PDO in near future , but i need to know how am i gonna achieve doing this first . – Caffeine Coder Feb 07 '14 at 14:20
  • 1
    JSON means JavaScript Object Notation. If you `json_encode` the array, it will create a `string` with the array in a JSON format. The string can be easily stored in the database in a (new) column with the datatype `char` or `varchar`. – Ruben Feb 07 '14 at 14:28
  • can u reflect what changes should i do in my code ? Worked on it the whole day so out of ideas how am i gonna do it :( – Caffeine Coder Feb 07 '14 at 14:31

1 Answers1

-1

Because you have an array of arrays, the correct foreach would look like this:

$values = array();
foreach ($newelement as $element) {
    $values[] = "('".mysql_real_escape_string($element[0])."','".mysql_real_escape_string($element[1])."')";
}

$db_insert = mysql_query("INSERT INTO fruits (fruit,color) VALUES ".implode(",", $values);
fboes
  • 2,149
  • 16
  • 17