JSON Array to PHP Array Multidimensional

Question

i have json data like this :

{
      "response": {
        "count": 212,
        "list": [
          {
            "code": "02007",
            "name": "swept the room",
            "rate": 750000,
            "withValue": false
          },
          {
            "code": "02005",
            "name": "mop room",
            "rate": 600000,
            "withValue": false
          },
          {
            "code": "02003",
            "name": "buying food",
            "rate": 175000,
            "withValue": false
          }
        ]
      },
      "metaData": {
        "message": "OK",
        "code": 200
      }
}

and i have table schema like this :

mysql> desc master_service;

    +----------------+-------------+------+-----+---------+----------------+
    | Field          | Type        | Null | Key | Default | Extra          |
    +----------------+-------------+------+-----+---------+----------------+
    | id             | int(25)     | NO   | PRI | NULL    | auto_increment |
    | code           | varchar(10) | YES  |     | NULL    |                |
    | name           | varchar(20) | YES  |     | NULL    |                |
    | rate           | double      | YES  |     | NULL    |                |
    | withvalue      | tinyint(1)  | YES  |     | NULL    |                |
    +----------------+-------------+------+-----+---------+----------------+

and my coding like this.

//using php pdo

 include_once 'db_connect.php';
 $data = json_decode($response, true);

 $tempservice = array();
 if(isset($data['response']) && isset($data['response']['list']))
 {
    //use foreach on ['response']['list'] index - here are teachers data stored
    foreach($data['response']['list'] as $service)      
        $tempservice[$kesadaran['code']] = $service['name'];
    }

    foreach($tempservice as $key =>$value) {
        $sql = "insert into master_service(code,name) Values ('$key','$value')";
        $st = $dbh->prepare($sql);
        $st->execute ($data);
    }

it can only save the database in the form of codes and names. i want rate and withValue can be save on database

Remove `$tempservice` you don't need it at all. Just loop your data and execute inserts. — brzuchal, Nov 14 '16 at 09:45
If you are the OC of this code, may I ask why you use prepared statement and still injecting your data ? — Anwar, Nov 14 '16 at 09:58
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Nov 14 '16 at 10:00
I think it becomes a funny downvote game post now. Not sure why I got downvote. — Muhammed Imran Hussain, Nov 14 '16 at 10:12

score 1 · Answer 1 · edited May 23 '17 at 11:45

Just use a foreach loop and add the missing columns, no need for a restructured $tempservice array:

include_once 'db_connect.php';
$data = json_decode($response, true);

// Simplified if, checking for isset($data['response') is redundant 
if (isset($data['response']['list']))
{
    // Prepare the statement using placeholders
    $sql = "INSERT INTO master_service (code,name,rate,withValue) VALUES (:code,:name,:rate,:withValue)";
    $stmt = $dbh->prepare($sql);

    foreach($data['response']['list'] as $row)
    {
        // Execute query with parameters
        $stmt->execute($row);
    }
}

Important: I replaced the variables in your query with placeholders. This way it is safe against SQL Injection. I also put the prepare outside of the loop so that it doesn't "reprepare" on every iteration.

A hint: you can send $row directly into execute, avoiding the inner loop at all — Your Common Sense, Nov 14 '16 at 11:19
@YourCommonSense indeed. Makes it even simpler (and probably less error-prone). I've updated my answer accordingly. — simon, Nov 14 '16 at 12:25

score -2 · Answer 2 · edited Nov 17 '16 at 08:06

-2

You can try this:-

include_once 'db_connect.php';
$data = json_decode($response, true);

$tempservice = [];
if(isset($data['response']) && isset($data['response']['list'])){
    //use foreach on ['response']['list'] index - here are teachers data stored
    foreach($data['response']['list'] as $service){  
        $withValue = ($service['withValue']) ? 1 : 0;
        $tempservice[$service['code']] = ['name'=>$service['name'], 'rate'=>$service['rate'], 'withvalue'=>$withValue];
    }
}

foreach($tempservice as $key =>$value) {
    $sql = "insert into master_service(code, name, rate, withvalue) Values ('$key', '".$value['name']."', '".$value['rate']."', '".$value['withvalue']."')";
    $st = $dbh->prepare($sql);
    $st->execute ();
}

Note: please fix any syntax error. Also you can skip creating tempservice array and can execute insert sql there

edited Nov 17 '16 at 08:06

Alive to die - Anant

70,531
10
51
98

answered Nov 14 '16 at 09:55

Muhammed Imran Hussain

2,057
16
24

@YourCommonSense please point where is the problem so that it will be easy for me to explain or my views. Thanks – Muhammed Imran Hussain Nov 14 '16 at 11:25
1. Some people do. 2. Some mistakes are so obvious that one don't actually need an environment. You don't need an environment to write down a schoolbook example. All you need is just a knowledge on the matter in the question. – Your Common Sense Nov 14 '16 at 13:22
You may think like that as well. Cheers. – Your Common Sense Nov 14 '16 at 13:26

score -5 · Answer 3 · edited Nov 14 '16 at 11:43

-5

foreach($data['response']['list'] as $value) {
    $sql = "insert into master_service(code,name,rate,withvalue) Values ('{$value['code']}','{$value['name']}', '{$value['rate']}', '{$value['withValue']}')";
    $st = $dbh->prepare($sql);
    $st->execute ($data);
}

sorry i forgot the true parameter on json_decode.. edited

edited Nov 14 '16 at 11:43

RiggsFolly

93,638
21
103
149

answered Nov 14 '16 at 09:46

Semi-Friends

480
5
17

1

TYPO, you might like to fix `Values ('$value)->code'` – RiggsFolly Nov 14 '16 at 09:47
And as OP has used `json_decode($response, true);` the values are an array not an object – RiggsFolly Nov 14 '16 at 09:50
1

this code will never work. yet another accepted answer is a fraud – Your Common Sense Nov 14 '16 at 11:20

JSON Array to PHP Array Multidimensional

3 Answers3