current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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

share|improve this question
    
What is your database structure? –  hakre Aug 15 '11 at 10:07
2  
What do you mean by "get this data in mysQL" exactly? Can you show some more code? –  Pekka 웃 Aug 15 '11 at 10:07
2  
MySQL is not CouchDB, to save PHP array in MySQL in one(!) line, serialize the array –  rabudde Aug 15 '11 at 10:10
    
Are you trying to serialize an array and store in a text field? Use functions: json_encode or serialize then... –  madflow Aug 15 '11 at 10:10
    
@Pekka: i think he means to get it into –  genesis Aug 15 '11 at 10:11
show 1 more comment

3 Answers

up vote 2 down vote

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

share|improve this answer
1  
That is a good way to do it. It allow you to do abstraction of the structure. –  David Laberge Aug 15 '11 at 10:20
add comment
up vote 2 down vote

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.

share|improve this answer
    
double parse error + mysql error –  genesis Aug 15 '11 at 10:15
    
Edited hit the post answer to quickly –  David Laberge Aug 15 '11 at 10:18
    
okay :) removed my downvote –  genesis Aug 15 '11 at 10:29
    
@genesis Thanks!! –  David Laberge Aug 15 '11 at 10:30
    
by the way, it's still wrong. 1. category is text, not int, 2. you're missing comma :) –  genesis Aug 15 '11 at 10:35
show 1 more comment
up vote 1 down vote

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'])."')";
share|improve this answer
    
+1 Exactly what I was going to put. –  Bojangles Aug 15 '11 at 10:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.