Insert nested json array into mysql

Question

I am trying to insert a JSON array into my MySQL database. Here is the format of the array:

${
    "customer_id": "1",
    "products":[ {
        "product_id": "1",
        "product_qty": "2"
    }, {
        "product_id": "2",
        "product_qty": "4"
    }, {
        "product_id": "3",
        "product_qty": "12"
    }, {
        "product_id": "4",
        "product_qty": "22"
    }],
    "order_totalamount": "100"
}

I tried inserting the query as below:

 <?php
   require("config.inc.php");
   $jsondata = file_get_contents('OrderFormat.json');
    //convert json object to php associative array
    $data = json_decode($jsondata, true);

   //get the employee details
   $cus_id = $data['customer_id'];
   $product_id = $data['products']['product_id'];
   $product_qty = $data['products']['product_qty'];
   $order_totalamount = $data['order_totalamount'];

        //insert into mysql table
         $sql = "insert into `order`(cm_id,product_id,product_quantity,order_totalamount,order_id,order_date) values ($cus_id,$product_id,$product_qty,$order_totalamount,$cus_id,CURDATE())";

        echo $sql;
        //$sql1 = mysql_query($sql);
        $conn = mysqli_connect($host, $username, $password, $dbname);
        // Check connection
        if (!$conn) {
            die("Connection failed: " . mysqli_connect_error());
        }

        if(!mysqli_query($conn,$sql))
        {
            die('Error : ' . mysql_error());
        }
?>

Also I decode the JSON data, and foreach loop.

Kindly help me in this issue. Thanks in advance.

I suggest you to store the values in variables, so $item['product_id'] becomes $product_id. Also, try with PDO php.net/manual/en/book.pdo.php — Noogic
– Noogic, Commented Jan 13, 2016 at 10:23
@Noogic : thanx for your suggestion, but if i am storing values in variable then same problem occure with nested array. because i have multiple product for same cutomer id and amount — RU_23
– RU_23, Commented Jan 13, 2016 at 10:28
@AnkiiG: No i didn't getting any error but insertion also didn't work. — RU_23
– RU_23, Commented Jan 13, 2016 at 10:29

Ire · Accepted Answer · 2016-01-13 10:49:26Z

1

May be you should try to normalize your database. Since a row of the order table is representing an order, and you can't show arbitrary amount of products in a column(unless you just stringify the array and put it in.)

Your JSON appear to be an order. And intuitively, an order is ordered by a customer. On the other hand, a customer could have many orders. So the customer-to-order relation suppose to be a one-to-many relation, which shall be implemented by a foreign key customer_id from order to customer

Followed up by products and orders relation. A product can be shown in many orders. Also, an order could contain many products. Therefore, the product-to-order relation shall be a many-to-many relation. Empirically, you should have another table to maintain the relation. Let say the table order_product have two foreign keys order_id and product_id point to order and product tables, respectively. In addition, this table should have another column stores product quantity.

I have seen your update, there are some errors in your code. Since 'products' is an array, retrieving product id and product quantity shall like below

$product_id = $data['products'][$i]['product_id'];
$product_qty = $data['products'][$i]['product_qty'];

edited Jan 13, 2016 at 10:49

answered Jan 13, 2016 at 10:43

Ire

2791 silver badge8 bronze badges

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

1 Comment

Ire Over a year ago

Please leave a comment or a result after you down vote an answer. Meaningless down vote doesn't help to solve your question.

AnkiiG · Accepted Answer · 2016-01-13 10:42:54Z

0

Try as below :

<?php
//require("config.inc.php");
$jsondata ='{
"customer_id": "1",
"products":[ {
"product_id": "1",
"product_qty": "2"
}, {
"product_id": "2",
"product_qty": "4"
}, {
"product_id": "3",
"product_qty": "12"
}, {
"product_id": "4",
"product_qty": "22"
}],
"order_totalamount": "100"
}';

//convert json object to php associative array
$data = json_decode($jsondata, true);

//get the employee details
$cus_id = $data['customer_id'];
$order_totalamount = $data['order_totalamount'];
$order_totalamount = $data['order_totalamount'];

foreach($data['products'] as $key => $val)
{
    $product_id = $val['product_id'];
    $product_qty = $val['product_qty'];

    //insert into mysql table
    $sql = "insert into `order`(cm_id,product_id,product_quantity,order_totalamount,order_id,order_date) values ($cus_id,$product_id,$product_qty,$order_totalamount,$cus_id,CURDATE())";

    echo $sql;
    //$sql1 = mysql_query($sql);
    $conn = mysqli_connect($host, $username, $password, $dbname);
    // Check connection
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }

    if(!mysqli_query($conn,$sql))
    {
        die('Error : ' . mysql_error());
    }
}
?>

answered Jan 13, 2016 at 10:42

AnkiiG

3,4861 gold badge19 silver badges28 bronze badges

1 Comment

RU_23 Over a year ago

Hey it's working properly now. Thanks for your help.

Divyesh · Accepted Answer · 2016-01-13 10:53:24Z

0

Try this Code

    $data = json_decode($json_string);
    $customer_id = $data->customer_id;

    foreach($data->products as $product) // its a array of products you showed in json 
    {
        // insert code here for mysql insert
    }

answered Jan 13, 2016 at 10:53

Divyesh

3293 silver badges17 bronze badges

Comments

Karthik · Accepted Answer · 2017-11-11 13:28:12Z

0

<?php
$json = '{
    "customer_id": "1",
    "products":[ {
        "product_id": "1",
        "product_qty": "2"
    }, {
        "product_id": "2",
        "product_qty": "4"
    }, {
        "product_id": "3",
        "product_qty": "12"
    }, {
        "product_id": "4",
        "product_qty": "22"
    }],
    "order_totalamount": "100"
}';

$obj = json_decode($json);
$data=$obj->{'products'};
foreach($data as $item){
$sql = "insert into `order`(cm_id,product_id,product_quantity,order_totalamount,order_id,order_date) values ($cus_id,".$item->{'product_id'}.",".$item->{'product_qty'}.",$order_totalamount,$cus_id,CURDATE())";

}


?>

answered Nov 11, 2017 at 13:28

Karthik

377 bronze badges

Comments

dhanushka · Accepted Answer · 2016-01-13 10:42:09Z

-1

try this code

<?php
$json = '{
    "customer_id": "1",
    "products":[ {
        "product_id": "1",
        "product_qty": "2"
    }, {
        "product_id": "2",
        "product_qty": "4"
    }, {
        "product_id": "3",
        "product_qty": "12"
    }, {
        "product_id": "4",
        "product_qty": "22"
    }],
    "order_totalamount": "100"
}';

$obj = json_decode($json);
$data=$obj->{'products'};
foreach($data as $item){
$sql = "insert into `order`(cm_id,product_id,product_quantity,order_totalamount,order_id,order_date) values ($cus_id,".$item->{'product_id'}.",".$item->{'product_qty'}.",$order_totalamount,$cus_id,CURDATE())";

}


?>

answered Jan 13, 2016 at 10:42

dhanushka

3923 silver badges12 bronze badges

Collectives™ on Stack Overflow

Insert nested json array into mysql

5 Answers 5

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related