Json_decode and foreach problem

Hello guys!

In my “cart” table “items” column I store json_encoded strings. I decode, loop trough and grab them as an array. It works fine as long as the items has different product ID-s: [{“id”:“56”,“size”:“Small”,“quantity”:“1”},{“id”:“53”,“size”:“Medium”,“quantity”:“2”}]

But when the items have the same product ID it only shows the last one in the json string,
in this case “Large”: [{“id”:“53”,“size”:“Small”,“quantity”:“1”},{“id”:“53”,“size”:“Large”,“quantity”:“2”}]

I made a var_dump for the products and it says that the two items are there, but like I said it only shows the last one in the output.

$txn_id = sanitize((int)$_GET['txn_id']);
$txnQuery = $db->query("SELECT * FROM transactions_alternative WHERE id = '{$txn_id}'");
$txn = mysqli_fetch_assoc($txnQuery);
$cart_id = $txn['cart_id'];
$cartQ = $db->query("SELECT * FROM cart WHERE id = '{$cart_id}'");
$cart = mysqli_fetch_assoc($cartQ);
$items = json_decode($cart['items'],true);
$idArray = array();
$products = array();var_dump($items);
foreach($items as $item){
   $idArray[] = $item['id'];
$ids = implode(',',$idArray);
$productQ = $db->query("SELECT i.id as 'id', i.title as 'title', c.id as 'cid', c.category as 'child', p.category as 'parent'
   FROM products i
   LEFT JOIN categories c ON i.categories = c.id
   LEFT JOIN categories p ON c.parent = p.id
   WHERE i.id IN ({$ids})");
  while($p =  mysqli_fetch_assoc($productQ)){

    foreach($items as $item){
      if($item['id'] == $p['id']){
        $x = $item;
    $products[] = array_merge($x,$p);var_dump($products);
<h2 class="text-center">Rendelés részletei</h2>
<div class="col-md-12">
  <h3 class="text_center">Rendelt termékek</h3>
<table class="table table-condensed table-bordered table-striped">
    <th>Rendelt mennyiség</th><th>Termék neve</th><th>Kategória</th><th>Opció</th>
    <?php foreach($products as $product): ?>
      <td><?=$product['quantity'];?> db</td>
      <td><?=$product['parent'].' / '.$product['child'];?></td>
    <?php endforeach ?>

I don’t think your second query will return multiple rows for the same id. Try it in phpmyadmin (or whatever you use to run scripts directly against the database) and see how many rows you get if you hard code it to end

WHERE i.id IN (53,53,53)";

Only one, I’m afraid. But why is that?

My guess is that (before any optimisation) the sql just loads each row in turn, compares the id to the WHERE list, if it finds it, it displays it. There is only one row with id 53, so you only see one.

I would think that, unless there’s a way you can extract the product id in the first query so that you can use it to JOIN the products and categories all in a single query, then another option is to run the second query inside a loop for each value of $items['id'].

One of the down-sides of storing data as part of an encoded string is that it makes it difficult to retrieve stuff like this, in a similar way to those who put a comma-separated list of ids inside a single database column rather than using a separate linked table. If you just had a column for the product id in your cart, it would all work from a single query, so far less program code required.

1 Like

what do you have in mind exactly?

by the way I tried to change
$x = $item;
$x[] = $item;

and then I initialized $x = []; right after $products = array();
then I made a var_dump: $products[] = array_merge($x,$p);var_dump($x);

before that it only showed the first item for $x but not it shows two (the two item in the cart)

the only problem is that now I get Undefined index: for quantity and size in the output.

Something along the lines of

foreach ($items as $item) { 
  $query = "SELECT i.id as id " etc etc using $item for the ID
  // execute the query
  // add the results to $products

It’s a bit less efficient if you have multiple cart entries with the same ID because you’re retrieving the same information multiple times.

Another way would be to iterate through $idArray, run the product query for each id, retrieve the product details into an array, then loop through the cart ids and retrieve the product information from the product array. That would be slightly better if you often have the same ID in several lines in the cart.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.