Need help in creating a customer and retailer table for a price comparison

[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?

[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

[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

[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']; 

?>