JSON Array and PHP problem

Question

array 0 => array 'categoryID' => string '1' (length=1) 'category' => string 'Restaurants' (length=11) 'subcategory' => string 'Chinese' (length=7)

'subcategoryID' => string '12' (length=2)

How can I get this data in MYSQL. I have tried JSON_Decode but problem is I am getting "Array" stored in MYSQL instead of string.

Please help. Sorry I am new in stackoverflow. I following is base code with database structure:

CategoryID Int(5), Category Var(254), Subcategory Var(254), SubCategoryID Int (5).

Following is code I used:

<?php
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("cp");
$alldate = '[{ "categoryID": "1", "category": "Restaurants", "subcategory": "Chinese", "subcategoryID": "12" }]';
$json = json_decode($alldate);
$i=0;
foreach ($json  as $k1=>$v1) {
    echo $i;
  foreach($v1 as $k2=>$v2){
    if ($k2 == 'categoryID')$vals['categoryID'][]=$v2;
    if ($k2 == 'category')$vals['category'][]=$v2;
    if ($k2 == 'subcategory')$vals['subcategory'][]=$v2;
    if ($k2 == 'subcategoryID')$vals['subcategoryID'][]=$v2;
  }
    $insert_categoryID[]='Name:'.$vals['categoryID'][$i];
    $insert_category[]= $vals['category'][$i];
    $insert_subcategory[]= $vals['subcategory'][$i];
    $insert_subcategoryID[]= $vals['subcategoryID'][$i];
    }

    print_r($insert_categoryID);
    print_r($insert_category);
    print_r($insert_subcategory);
    print_r($insert_subcategoryID);
    ?>

Thanks for your answers. I am checking this with solution provided here

What do you mean by "get this data in mysQL" exactly? Can you show some more code? — Pekka
– Pekka, Commented Aug 15, 2011 at 10:07
MySQL is not CouchDB, to save PHP array in MySQL in one(!) line, serialize the array — rabudde
– rabudde, Commented Aug 15, 2011 at 10:10
Are you trying to serialize an array and store in a text field? Use functions: json_encode or serialize then... — madflow
– madflow, Commented Aug 15, 2011 at 10:10

rickyduck · Accepted Answer · 2011-08-15 10:12:45Z

2

On your array (for example $array), you will need to do a foreach:

$array = json_decode($json);
$inserts = "";
$values = "";
foreach($array as $key => $value){
     $inserts .= "'$key', ";
     $values .= "'$value', ";
}
$inserts = substr($inserts,0,-2);
$values =  substr($values,0,-2);
$query = mysql_query("INSERT INTO `table` (".$inserts.") VALUES (".$values.")")or die(mysql_error());

Wrote that blind, it should work

answered Aug 15, 2011 at 10:12

rickyduck

4,08414 gold badges62 silver badges95 bronze badges

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

1 Comment

David Laberge Over a year ago

That is a good way to do it. It allow you to do abstraction of the structure.

David Laberge · Accepted Answer · 2011-08-15 10:38:41Z

2

try something like:

for($x=0; $x<count($arrayJson); $x++){
    $sql = "Insert into TABLENAME (field1, field2) VALUES (".$arrayJson[$]['categoryID'].", '".$arrayJson[$]['category']."')";
}

That is assumming you know both your Json structure and the DB one.

edited Aug 15, 2011 at 10:38

answered Aug 15, 2011 at 10:14

David Laberge

16.1k15 gold badges56 silver badges84 bronze badges

2 Comments

David Laberge Over a year ago

Edited hit the post answer to quickly

genesis Over a year ago

by the way, it's still wrong. 1. category is text, not int, 2. you're missing comma :)

genesis · Accepted Answer · 2011-08-15 10:10:50Z

1

Array is because it's an array. You have to put there something like

$decoded = json_decode($json);
$query = "INSERT INTO something VALUES ('".mysql_real_escape_string($json[0]['categoryID'])."','".mysql_real_escape_string($json[0]['category'])."','".mysql_real_escape_string($json[0]['subcategory'])."','".mysql_real_escape_string($json[0]['subcategoryID'])."')";

answered Aug 15, 2011 at 10:10

genesis

51.1k20 gold badges99 silver badges127 bronze badges

Collectives™ on Stack Overflow

JSON Array and PHP problem

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related