rpmb Posted July 4, 2023 Share Posted July 4, 2023 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. Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted July 4, 2023 Solution Share Posted July 4, 2023 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>'; } Quote Link to comment Share on other sites More sharing options...
rpmb Posted July 4, 2023 Author Share Posted July 4, 2023 Brilliant thanks for that splits it up exactly how I was looking for. Now just need to run inserts into the database. Quote Link to comment Share on other sites More sharing options...
rpmb Posted July 5, 2023 Author Share Posted July 5, 2023 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted July 5, 2023 Share Posted July 5, 2023 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 | +----------------+ +--------------+ Quote Link to comment Share on other sites More sharing options...
rpmb Posted July 5, 2023 Author Share Posted July 5, 2023 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted July 5, 2023 Share Posted July 5, 2023 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; } ?> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.