I have a products table, in which I store all the information of a product, EXCEPT the price. Why? Because price is a multivalued attribute. There may be four or five different prices (based on quantity) for a product, and those prices, along with their respective quantities are placed in a products_pricing table (both tables shown below).
Here's what I'm doing thus far on the site. When a customer clicks a subcategory, products.php?catID=blah&subcatID=blah is called. The database is queried based on catID and subcatID and it returns all the appropriate products. I then use pagination to split the results into however many pages necessary. On these pages, I am ONLY showing a thumbnail of the product, the name of the product, and ideally, a little line saying "as low as $X.XX".
That's where I'm stuck right now. How do I get this price from the database. I could do something, probably VERY inefficient. For each product that is pulled from the main $result array (each product entry is accessed via $i), I could do the following:
$prodID = mysql_result($result, $i, 'item_num');
Then for EACH product shown on the page, I could do an individual query to the products_pricing table as follows:
$resultprice = mysql_query("SELECT price
FROM product_pricing
WHERE item_num='$prodID'
ORDER BY price") or die(mysql_error());
Because I ordered these results, the lowest price will be the first row in the $resultprice array.
I could then do the following:
echo "as low as $";
echo mysql_result($resultprice , $0, 'price');
But this just seem EXTREMELY inefficient. My tables are shown below. Could someone please advise a way, that is hopefully not too complicated, to accomplish this? Or maybe you will say that I should do these individual queries. This basically means that on a page with 9 products showning, I will have 10 queries, which seems crazy. But if this is they way to do it, so be it.
Here's the two table schemas:
CREATE TABLE products (
item_num VARCHAR(20) NOT NULL,
supplier_num VARCHAR(10) NOT NULL,
name VARCHAR(50),
description VARCHAR(1000),
size VARCHAR(255),
color VARCHAR(255),
picture1 VARCHAR(255),
picture2 VARCHAR(255),
picture3 VARCHAR(255),
logo_method VARCHAR(100),
logo_loc_size VARCHAR(100),
turnaround VARCHAR(255),
addl_notes VARCHAR(255),
setup VARCHAR(255),
catID VARCHAR(20) NOT NULL,
index (catID),
subcatID VARCHAR(20) NOT NULL,
index (subcatID),
PRIMARY KEY (item_num),
index (supplier_num),
FOREIGN KEY (supplier_num) REFERENCES suppliers (supplier_num)
ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (catID) REFERENCES category (catID)
ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (subcatID) REFERENCES category (subcatID)
ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=INNODB;
#Create products_pricing table. Pricing is a multi-valued attribute of
#the products table and thus has its own table shown below.
#item_num is a foreign key in this table and refers to the products table.
CREATE TABLE products_pricing (
item_num VARCHAR(20) NOT NULL,
quantity INT,
price DECIMAL(6,2),
code CHAR(1),
PRIMARY KEY (item_num, price, code, quantity),
INDEX (item_num),
FOREIGN KEY (item_num) REFERENCES products (item_num)
ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=INNODB;
Thanks.
Jonathan
