Parsing JSON to MySQL table

Question

I'm using Zend Framework (1.12) and I'd like to create a table based on a JSON file. I've already created the table and its fields (they're all longtext for now), all it has to do is insert them into the right columns. I followed these examples:

http://www.daniweb.com/web-development/php/threads/381669/json-to-mysql-with-php (second post) Parse JSON to mySQL

The problem is that my JSON is constructed differently (mine has a "root element" called Actie, don't really know the right term) which contains an array with all the objects. Currently, I'm using this code:

$actieurl = "http://creative3s.com/thomas/nmdad/actie.json";
        $my_arr = json_decode(file_get_contents($actieurl));

        $db = new Zend_Db_Adapter_Pdo_Mysql(array(
            'host' => 'localhost',
            'username' => 'root',
            'password' => NULL,
            'dbname' => 'zf-tutorial'
            ));

        foreach($my_arr as $key => $value){
            $sql[] = (is_numeric($value)) ? "`$key` = $value" : "`$key` = '" . mysql_real_escape_string($value) . "'"; 
        }

        $sqlclause = implode(",",$sql);
        $query = "INSERT INTO `testerdetest` SET $sqlclause";
        $db->query($query);

But I'm getting an error saying that I'm passing an array:

Warning: mysql_real_escape_string() expects parameter 1 to be string, array given in C:\Users\Thomas\Documents\GitHub\NMDAD-testing\application\controllers\IndexController.php on line 29

Does anyone know how to solve this with a JSON of this format? Keep in mind that I cannot alter the JSON in any way. Extra links:

JSON: http://creative3s.com/thomas/nmdad/actie.json Table structure: https://i.sstatic.net/HPKVL.png

Post your JSON. It's not decoding to a flat array like you expect. Also why are you calling mysql_query and $db->query()? — Cfreak
– Cfreak, Commented Jan 22, 2013 at 15:33
nice SQL injection hole... you're trying to escape the value... but you've forgotten to escape the key. And did you read the error message? you're trying to escape an ARRAY. — Marc B
– Marc B, Commented Jan 22, 2013 at 15:33
@MarcB I know that, it's trying to parse the Actie array which contains all my objects. The problem I'm having is that I don't know how to properly change the code to reflect this change of structure in the JSON. I realize it's trying to parse the entire Actie array. I updated my answer to include the JSON at the bottom of the question. — thomastuts
– thomastuts, Commented Jan 22, 2013 at 15:39
@haiqt: you'd need to loop over that sub-array and do whatever you need to. you've provided NO details as to your table structure. — Marc B
– Marc B, Commented Jan 22, 2013 at 15:43
@MarcB I've included the table structure in my question as well. You can find at i.imgur.com/KtXeEuw.png. I followed the tutorials in my question and they said to create columns with the same name as all the fields in the JSON. — thomastuts
– thomastuts, Commented Jan 22, 2013 at 15:46

Tim Fountain · Accepted Answer · 2013-01-22 16:03:00Z

2

Your json data has the top level key 'Actie', so you need to be looping through $my_arr->Actie.

You can simplify your code to just:

$actieurl = "http://creative3s.com/thomas/nmdad/actie.json";
$my_arr = json_decode(file_get_contents($actieurl));

$db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host' => 'localhost',
    'username' => 'root',
    'password' => NULL,
    'dbname' => 'zf-tutorial'
));

foreach($my_arr->Actie as $row){
    $db->insert('testerdetest', (array)$row);
}

answered Jan 22, 2013 at 16:03

Tim Fountain

33.2k5 gold badges44 silver badges70 bronze badges

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

Collectives™ on Stack Overflow

Parsing JSON to MySQL table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related