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;
continue;
}
}
$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">
<thead>
<th>Rendelt mennyiség</th><th>Termék neve</th><th>Kategória</th><th>Opció</th>
</thead>
<tbody>
<?php foreach($products as $product): ?>
<tr>
<td><?=$product['quantity'];?> db</td>
<td><?=$product['title'];?></td>
<td><?=$product['parent'].' / '.$product['child'];?></td>
<td><?=$product['size'];?></td>
</tr>
<?php endforeach ?>
</tbody>
</table>
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
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.
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.