Help with decoding json

[rpmb]

I will be getting from an android app json code which will need to be inserted into a MySQL database.

 

{"androidID":"6e5d819af1afb92d","cardAmount":7.149999999999999,"cashAmount":10.0,
"list":[{"barcode":"1111117971111 5010018003165","cat_id":23,"cost":1.45,"id":0,"name":"1.45 CAN","payment_id":"","payment_type":"","product_id":2695,"product_type":"","qty":1,"staff_name":"","total":1.45},
{"barcode":"5054267000704 5054267000681 5449000125019 5449000124999 5038512005041 1111111141159 50271511 5449000107077 5038512000756 40822938 54492493 54493957","cat_id":23,"cost":1.85,"id":0,
"name":"1.85 BOTTLE","payment_id":"","payment_type":"","product_id":2694,"product_type":"","qty":1,"staff_name":"","total":1.85},
{"barcode":"54491496 5000112628739","cat_id":23,"cost":1.95,"id":0,"name":"500ml Diet Coke","payment_id":"","payment_type":"","product_id":1758,"product_type":"","qty":1,"staff_name":"","total":1.95},
{"barcode":"1211111111111","cat_id":6,"cost":6.7,"id":0,"name":"WAFFLE 3 SCOOP","payment_id":"","payment_type":"","product_id":1254,"product_type":"","qty":1,"staff_name":"","total":6.7},
{"barcode":"1111111111160","cat_id":6,"cost":5.2,"id":0,"name":"WAFFLE 2 SCOOP","payment_id":"","payment_type":"","product_id":1252,"product_type":"","qty":1,"staff_name":"","total":5.2}],
"paymentID":8646434759308,"paymentType":"Split"}

Using PHP 8 I am struggling with how to obtain all the data in a usable format.

What I need to do is get the first values androidID, cardAmount and cashAmount and then split the list by each product so from barcode to total.

Have tried various google search solutions using foreach loops but that either only returns the first 3 values and nothing from the list section or throws an error.

I did come across the following which will decode the entire lot but again struggling to then split the results as I cannot seem to access the array of information for each product. 

 

$iterator = new RecursiveIteratorIterator(
    new RecursiveArrayIterator(json_decode($json_output, TRUE)),
RecursiveIteratorIterator::SELF_FIRST);


foreach($iterator as $key => $value)
{
print $key . " : " . $value . "<br/>";
}

Further more the above json is an example. It may have 3 products in the list section it may have 1,2,5,10 

What I need to achieve is get the data, determine how many products are in the list section and then insert those products into the database.

Would appreciate any pointers in the right direction or even if a no that's not possible with the json output in its current format.

[Barand]

Try something like

$j = '{"androidID":"6e5d819af1afb92d","cardAmount":7.149999999999999,"cashAmount":10.0,
"list":[{"barcode":"1111117971111 5010018003165","cat_id":23,"cost":1.45,"id":0,"name":"1.45 CAN","payment_id":"","payment_type":"","product_id":2695,"product_type":"","qty":1,"staff_name":"","total":1.45},
{"barcode":"5054267000704 5054267000681 5449000125019 5449000124999 5038512005041 1111111141159 50271511 5449000107077 5038512000756 40822938 54492493 54493957","cat_id":23,"cost":1.85,"id":0,
"name":"1.85 BOTTLE","payment_id":"","payment_type":"","product_id":2694,"product_type":"","qty":1,"staff_name":"","total":1.85},
{"barcode":"54491496 5000112628739","cat_id":23,"cost":1.95,"id":0,"name":"500ml Diet Coke","payment_id":"","payment_type":"","product_id":1758,"product_type":"","qty":1,"staff_name":"","total":1.95},
{"barcode":"1211111111111","cat_id":6,"cost":6.7,"id":0,"name":"WAFFLE 3 SCOOP","payment_id":"","payment_type":"","product_id":1254,"product_type":"","qty":1,"staff_name":"","total":6.7},
{"barcode":"1111111111160","cat_id":6,"cost":5.2,"id":0,"name":"WAFFLE 2 SCOOP","payment_id":"","payment_type":"","product_id":1252,"product_type":"","qty":1,"staff_name":"","total":5.2}],
"paymentID":8646434759308,"paymentType":"Split"}';

$data = json_decode($j, 1);                                      // put json data into an array

$required = ['androidID', 'cardAmount', 'cashAmount'];

foreach ($required as $k) {
    echo "<b>$k : </b>{$data[$k]}<br>";
}
echo '<hr>';
foreach ($data['list'] as $prod) {
    foreach ($prod as $k => $v) {
        echo "<b>$k : </b>$v<br>";
    }
    echo '<hr>';
}

 

[rpmb]

Brilliant thanks for that splits it up exactly how I was looking for.

Now just need to run inserts into the database.

[rpmb]

Hi,

Shamefully back again as I cannot fathom how to now put the data into usable inserts for mysql.

So, where it splits the results between each hr here

 

foreach ($data['list'] as $prod) {
    foreach ($prod as $k => $v) {
        echo "<b>$k : </b>$v<br>";
    }
    echo '<hr>';
}

I need a way to insert that data between each hr line

Thanks for your help. I do appreciate it. 

[Barand]

1 hour ago, rpmb said:

how to now put the data into usable inserts for mysql.

I'd love to help but I have no idea what your (five?) tables look like, and why each product has so many barcodes.

I would expect it to be like this, given the data, but need to be sure ...

                                                                                   +----------------+
                                                                                   | prod_barcode   |
                                                                                   +----------------+
                                                                            +-----<| product_id (PK)|
+--------------+                                      +----------------+    |      | barcode    (PK)|
| transaction  |                                      | product        |    |      +----------------+
+--------------+                                      +----------------+    |
| id           |---+                          +-------| id             |----+
| cash_amount  |   |                          |       | product_type   |    
| card_amount  |   |                          |       | cat_id         |>---+
| payment_type |   |                          |       | name           |    |
| payment_id   |   |      +--------------+    |       | cost           |    |
| staff_name   |   |      | trans_item   |    |       +----------------+    |       +----------------+  
+--------------+   |      +--------------+    |                             |       | category       |  
                   |      | id           |    |                             |       +----------------+  
                   +-----<| trans_id     |    |                             +-------| id             |  
                          | product_id   |>---+                                     | cat_name       |  
                          | qty          |                                          +----------------+  
                          +--------------+                                          
                                                                                    

 

[rpmb]

Sorry should have explained that.

I have one table for the first foreach loop with the androidid, cashamount and cardamount

The second foreach loop has a table that stores everything else barcode, cat_id, product_id, cost etc

In that second foreach loop I need to insert the data. The table matches the fields.
 

{"barcode":"1111117971111 5010018003165","cat_id":23,"cost":1.45,"id":0,"name":"1.45 CAN","payment_id":"","payment_type":"","product_id":2695,"product_type":"","qty":1,"staff_name":"","total":1.45}

and then insert the next product array
 

{"barcode":"1211111111111","cat_id":6,"cost":6.7,"id":0,"name":"WAFFLE 3 SCOOP","payment_id":"","payment_type":"","product_id":1254,"product_type":"","qty":1,"staff_name":"","total":6.7}

continuing for how many products have come through.


 

[Barand]

Disclaimer: just to say I am reluctantly aiding and abetting this db structure; it needs normalizing.

Try

<?php
const HOST     = 'localhost';                                                           
const USERNAME = '????';                                                                
const PASSWORD = '????';                                                                
const DATABASE = '????';               // default db                                   
                                                                                        
function pdoConnect($dbname=DATABASE)                                                   
{                                                                                       
    $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD); 
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);                       
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);                  
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);                               
    return $db;                                                                         
}

################################################################################
### CONNECT TO DB SERVER                                                     ###
################################################################################
   $pdo = pdoConnect();                                                          
                             

$j = '{"androidID":"6e5d819af1afb92d","cardAmount":7.149999999999999,"cashAmount":10.0,
"list":[{"barcode":"1111117971111 5010018003165","cat_id":23,"cost":1.45,"id":0,"name":"1.45 CAN","payment_id":"","payment_type":"","product_id":2695,"product_type":"","qty":1,"staff_name":"","total":1.45},
{"barcode":"5054267000704 5054267000681 5449000125019 5449000124999 5038512005041 1111111141159 50271511 5449000107077 5038512000756 40822938 54492493 54493957","cat_id":23,"cost":1.85,"id":0,
"name":"1.85 BOTTLE","payment_id":"","payment_type":"","product_id":2694,"product_type":"","qty":1,"staff_name":"","total":1.85},
{"barcode":"54491496 5000112628739","cat_id":23,"cost":1.95,"id":0,"name":"500ml Diet Coke","payment_id":"","payment_type":"","product_id":1758,"product_type":"","qty":1,"staff_name":"","total":1.95},
{"barcode":"1211111111111","cat_id":6,"cost":6.7,"id":0,"name":"WAFFLE 3 SCOOP","payment_id":"","payment_type":"","product_id":1254,"product_type":"","qty":1,"staff_name":"","total":6.7},
{"barcode":"1111111111160","cat_id":6,"cost":5.2,"id":0,"name":"WAFFLE 2 SCOOP","payment_id":"","payment_type":"","product_id":1252,"product_type":"","qty":1,"staff_name":"","total":5.2}],
"paymentID":8646434759308,"paymentType":"Split"}';

$data = json_decode($j, 1);                                      // put json data into an array

try {
    $pdo->beginTransaction();                                                                                                 
    ################################################################################
    ### TRANSACTION DATA                                                         ###
    ################################################################################
        $tran_data = array_slice($data, 0, 3);
        $stmt = $pdo->prepare("INSERT INTO transaction (androidid, cardamount, cashamount)
                               VALUES( :androidID, :cardAmount, :cashAmount)
                              ");
        $stmt->execute($tran_data);                                                                                             
    ################################################################################
    ### ITEM DATA                                                                ###
    ################################################################################
        $stmt = $pdo->prepare("INSERT INTO sale_item 
                               (barcode, cat_id, cost, name, payment_id, payment_type, product_id, product_type, qty, staff_name, total)
                               VALUES
                               (:barcode, :cat_id, :cost, :name, :payment_id, :payment_type, :product_id, :product_type, :qty, :staff_name, :total)
                              ");
        foreach ($data['list'] as $item) {
            unset($item['id']);
            if ($item['payment_id'] == '') $item['payment_id'] = null;          // nullify missing int values
            if ($item['payment_type'] == '') $item['payment_type'] = null;
            if ($item['product_type'] == '') $item['product_type'] = null;
            $stmt->execute($item);
        }
    
    $pdo->commit();
}
catch (PDOException $e) {
    $pdo->rollBack();
    throw $e;
}
?>