Project description
I'm making a website listing products. The listing goes as following (See here):
- Product name
- Shop name
- Price in that shop
- If the product appears multiple times in the table for the same user (Which means he/she selected it in multiple stores. Merge them and just show multiple shop names)
Examples:
PS: the names are always the same in the screenshots but they could be completely different ones
Database Tables:
Table user_interests
:
user_hash | shop_id | product_id
--------------------+------------+---------------
a0a080f42e6f13b3... | 626 | 547261
a0a080f42e6f13b3... | 554 | 283678
a0a080f42e6f13b3... | 554 | 8680
a0a080f42e6f13b3... | 732 | 283678
0aa1883c6411f787... | 865 | 283678
Table shop_id
:
shop_id | shop_name
-----------+---------------
604 | Halle
626 | Vilvoorde
605 | Heverlee
Table product_price
:
shop_id | product_id | unit_price | ...
---------+------------+-------------------
626 | 547261 | 3,23 | ...
554 | 283678 | 4,59 | ...
732 | 283678 | 3,50 | ...
626 | 732 | 4,32 | ...
626 | 865 | 3,64 | ...
Table product_extra_info
:
which contains extra info's
Encountered Issue
I am able to get a result (the one you're able to see on the screenshots) and it is displayed like I want it to be. Only problem is the code is really messy and filled with (I think) unnecessary sql requests. My comments are below the code
// Merge them and count how many
$checkIfExistsMultipleSql = "SELECT `product_id`, COUNT(*)
FROM `user_interests`
WHERE `user_hash`='$user_hash'
GROUP BY `product_id`
HAVING COUNT(*) >= 1;
";
$checkIfExistsMultipleResult = $mysqli->query($checkIfExistsMultipleSql);
// If not empty continue else tell user
if ($checkIfExistsMultipleResult) {
// Make a website element for each product
while($checkrow = $checkIfExistsMultipleResult ->fetch_assoc()) {
$product = $checkrow['product_id'];
-------------------[1]-------------------
$multipleShopsSql = "SELECT `shop_id`
FROM `user_interests`
WHERE `product_id`='$product'
AND `user_hash`='$user_hash'";
$multipleShopsResult = $mysqli->query($multipleShopsSql);
-------------------[2]-------------------
$productInfoSql = "SELECT `name`, `picture`, `mesurement`, `content`
FROM `products_extra_info`
WHERE `product_id`='$product'";
$productInfoResult = $mysqli->query($productInfoSql);
$productInfo = $productInfoResult->fetch_assoc();
- I really do not like that part, I would like to "include" this one in the top one. (So what I'm trying to get is one select command which gives me a result showing this [product_id] is requested in [shop_id_1] and [shop_id_2] ...)
- For this one I would like to know what's better, as you can see I'm running this one, once for each product card. Is it good like that or should I run one big request and extract the data I need ?
$user_hash
come from? \$\endgroup\$