Jump to content

Help with decoding json


rpmb
Go to solution Solved by Barand,

Recommended Posts

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.

Link to comment
Share on other sites

  • Solution

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>';
}

 

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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          |                                          +----------------+  
                          +--------------+                                          
                                                                                    

 

Link to comment
Share on other sites

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.


 

Link to comment
Share on other sites

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;
}
?>

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.