current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.6 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote -1 down vote favorite

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.

share|improve this question
    
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 Jan 13 '16 at 10:23
    
Are you getting any error? – AnkiiG Jan 13 '16 at 10:26
    
@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 Jan 13 '16 at 10:28
    
@AnkiiG: No i didn't getting any error but insertion also didn't work. – RU_23 Jan 13 '16 at 10:29
    
@RU_23 will you be able to add full code? – AnkiiG Jan 13 '16 at 10:31
up vote -1 down vote accepted

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());
    }
}
?>
share|improve this answer
    
Try to add a comment when you down vote any answer.. – AnkiiG Jan 13 '16 at 10:45
    
Thanks for your answer but it didn't work for me. – RU_23 Jan 13 '16 at 10:51
    
Are you getting any error? @RU_23 – AnkiiG Jan 13 '16 at 10:51
    
No I didn't get any error. but also it didn't work. – RU_23 Jan 13 '16 at 16:42
    
Hey it's working properly now. Thanks for your help. – RU_23 Jan 13 '16 at 17:30
up vote 1 down vote

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'];
share|improve this answer
    
Please leave a comment or a result after you down vote an answer. Meaningless down vote doesn't help to solve your question. – Sheng-Duan Sun Jan 13 '16 at 10:53
up vote 0 down vote

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
    }
share|improve this answer
up vote -1 down vote

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())";

}


?>

share|improve this answer
1  
thanks for your answer but it didn't work. – RU_23 Jan 13 '16 at 10:51

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.