Member Avatar for sanbhu2105

i will explain the scenario as i don't know how to do it and i don't know if someone ever did it before.

I am creating a price comparison website. The customers can add products as their favourites from a list of products. They can also add 4 retailers as their favourites. Both will be stored in 2 tables - tblfavourites and tblfav_retailer. All the retailers have set different prices for the products.

I want to create a table displaying the products set as favourites by the customers and the 4 retailers. Under each retailer, will be displayed the price of the product they set. The total price of products by each retailer will be displayed at the bottom.

Can anyone help me out in creating that?

  • 2 Contributors
  • 3 Replies
  • 277 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by sanbhu2105

Dani AI

Generated

Immediate fix — why nothing prints
Your query probably runs but the way you read the result is wrong. mysql_fetch_row() returns a numeric array, so echo $row['prod_name']; will be empty. Either fetch an associative row or use the numeric index. Also always check the query result for errors so SQL problems don't silently fail.

$result = mysql_query($sql) or die('Query error: ' . mysql_error());
$row = mysql_fetch_assoc($result);    // associative array -> use column names
echo $row['prod_name'];

Schema and design notes
As suggested, move pricing into a proper join table that maps retailers to products and stores price (one row per retailer+product). Keep favourites as two mapping tables (user→product and user→retailer). Make (ret_id, prod_id) the PK on the price table and add indexes on user_id, ret_id and prod_id. This simplifies joins and avoids linking a retailer by an unrelated user_id.

Query pattern and how to build the table
Get the user’s favourite products and favourite retailers, then join them and left‑join the price table so missing prices show as NULL. Example pattern (use prepared statements in real code):

SELECT p.prod_id, p.prod_name, r.ret_id, r.ret_name, rp.price
FROM tblfavourites f
JOIN tblproduct p ON p.prod_id = f.prod_id
JOIN tblfav_ret fr ON fr.user_id = f.user_id
JOIN tblretailer r ON r.ret_id = fr.ret_id
LEFT JOIN retailer_products rp ON rp.prod_id = p.prod_id AND rp.ret_id = r.ret_id
WHERE f.user_id = ?
ORDER BY p.prod_name, r.ret_id;

Pivoting and totals (server side)
Return the rows above and pivot in PHP: build an array keyed by product id, collect each retailer’s price into a subarray, and accumulate per‑retailer totals while iterating. Example logic (mysqli/PDO recommended):

$products = []; $totals = [];
while ($r = $res->fetch_assoc()) {
  $pid = $r['prod_id']; $rid = $r['ret_id'];
  $products[$pid]['name'] = $r['prod_name'];
  $products[$pid]['prices'][$rid] = $r['price'] === null ? null : (float)$r['price'];
  $totals[$rid] = ($totals[$rid] ?? 0) + ($r['price'] ?: 0);
}

Final tips
Limit favourite retailers to four in the UI/SQL so columns stay stable. Prefer mysqli or PDO with prepared statements (avoid deprecated mysql_*). Use COALESCE in SQL or display a placeholder in the table for missing prices, and add indexes to keep the joins fast.

Recommended Answers

All 3 Replies

Member Avatar for sanbhu2105

i have the following tables

tblretprod - id, user_id, prod_id, prod_price

tblproduct - prod_id, prod_name, prod_brand, prod_desc, prod_photo

tblfavourites - fav_id, prod_id, user_id

tblfav_ret - id, user_id, ret_id

tblretailer - ret_id, user_id, ret_name, ret_address, ret_phone, ret_fax

Member Avatar for pzuurveen

you need an table like
retailerProduct - id, -ret_id, prod_id, price

also I recomend to remove user_id from retailer; Because 2 people can like the same retailer

favourteRetaler - id, user_id, ret_id

It's called database normalization

Member Avatar for sanbhu2105

for now i have created the following piece of code and i am having no display when i echo the prod_name

can anyone tell me where the problem lies

<?php 

session_start(); 

include('db_connect.php'); 

$username = $_SESSION['username']; 

$user = mysql_fetch_assoc(mysql_query("select user_id from tbllogin where username = '{$username}'")); 

$sql = ("SELECT prod_id FROM tblfavourites WHERE user_id = '$user[user_id]'"); 

$sql1 = ("SELECT ret_id FROM tblfav_ret WHERE user_id = '$user[user_id]'"); 

$query = mysql_query(" 
SELECT p.prod_name, rp.prod_price, r.ret_name 
FROM tblproduct AS p 
LEFT JOIN tblretprod AS rp  ON (rp.prod_id = p.prod_id) 
LEFT JOIN tblretailer AS r ON (r.user_id = rp.user_id) 
WHERE p.prod_id IN ($sql) AND r.ret_id IN ($sql1)"); 

$row = mysql_fetch_row($query); 

echo $row['prod_name']; 

?>
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.