in array, json data and put in MySql with php

Question

I have a data under format 'JSON' like this one :

{
    "email": "john@john.fr",
    "line_items": [
        {
            "sku": "123456789",
            "price": "0.67",
            "price_with_tax": "4.00",
            "tax_lines": [
                {
                    "title": "tax010",
                    "rate": 0.01,
                    "price": "1.11"
                },
                {
                    "title": "tax00200",
                    "rate": 0.02,
                    "price": "2.22"
                }
            ]
        },
        {
            "sku": "012345666",
            "price": "1.67",
            "price_with_tax": "5.00",
            "tax_lines": [
                {
                    "title": "tax0003000",
                    "rate": 0.03,
                    "price": "3.33"
                }
            ]
        }
    ]
}

I want put it in my database (MySql) by PDO::prepare. My following sql query works but second line_items have not good value im Mysql :

1st item :::: good value

email:john@john.fr
sku:123456789
price:0.67
price_with_tax:4.00
price_tax1:1.11
price_tax2:2.22

2nd item ::::

email:john@john.fr
sku:012345666
price:1.67
price_with_tax:5.00

wrong value ::::

price_tax1:1.11
price_tax2:2.22

How can I put it good value ?

here is my code :

$dataDecode = json_decode($jsondata);

$email = $dataDecode->email;

try
{
    $dtbs = new PDO($dsn_dev, $pdo_user, $pdo_password, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
}
catch (Exception $e)
{
    die('Error : ' . $e->getMessage());
}

try 
{
    foreach ($dataDecode->line_items as $obj) 
    {

        $var_sku = $obj->sku;

        $var_price = $obj->price;

        $var_price_with_tax = $obj->price_with_tax;

        $taxNewArray = array();
        foreach ($dataDecode->line_items[0]->tax_lines as $obj2)
            {
                array_push($taxNewArray , $obj2);

            }

        $val1st = array_shift($taxNewArray);
        $val2nd  = array_pop  ($taxNewArray);

        $var_tax1 = $val1st->price;
        $var_tax2 = $val2nd->price;

        $stmt = $dtbs->prepare("INSERT INTO $tabledata ($mysql_email, $mysql_sku, $mysq_price, $mysql_price_with_tax, $mysql_price__tax1___line_items, $mysql_price__tax2___line_items)
                                VALUES (:email, :sku, :price, :price_with_tax, :price_tax1, :price_tax2)"); 
        $stmt->execute(array(':email'=>$email,
                             ':sku'=>$var_sku,
                             ':price'=>$var_price,
                             ':price_with_tax'=>$var_price_with_tax,
                             ':price_tax1'=>$var_tax1,
                             ':price_tax2'=>$var_tax2
                             ));

    }

}
catch(Exception $e) 
{
    throw $e;
}

Do you have a idée ?

Answer 1

If there's only one entry in tax_lines, your code will try to set $var_tax2 from the nonexistent second entry. You need to check for this and substitute some other value:

$var_tax2 = $val2nd ? $val2nd->price : '0.0';

The other problem is this line:

foreach ($dataDecode->line_items[0]->tax_lines as $obj2)

You're using the tax lines from the first line item every time. That should be:

foreach ($obj->tax_lines as $obj2)