Hi, i want to create a new page that has a list of all the products with the same make/manufacture..
(as i have many products with the same name) i want to be able to show only the cheapest option..
What would be the correct sql statement for this, and how would i be able to show the data?
Thanks David..
Hi,
This is essentially what the brand:XXXXX: search query does. If you go to the Brand index page, and then select a brand, you will see that the link goes to (if not using .htaccess):
http://www.yoursite.com/search.php?q=brand:XXXXX:
(where XXXXX is the brand)
The search results then list all products with that brand (grouped by name, as normal) using the normal format, with paging etc.
If you just want to show the cheapest product for brand XXXXX, the SQL would be:
SELECT * FROM products WHERE brand='XXXXX' ORDER BY price ASC LIMIT 1For example:
<?php$sql = "SELECT * FROM products WHERE brand='Sony' ORDER BY price ASC LIMIT 1";
if (database_querySelect($sql,$rows))
{
$product = $rows[0];
print $product["name"];
}
?>
Don't forget that in this example, the $product array has all the other fields of a product record; for example $product["price"]. To see them all, you could use print_r($product);
You will also need to include the common headers, and html/header.php and html/footer.php to make this into a standalone page:
<?phprequire("includes/common.php");
require("html/header.php");
$sql = "SELECT * FROM products WHERE brand='Sony' ORDER BY price ASC LIMIT 1";
if (database_querySelect($sql,$rows))
{
$product = $rows[0];
print $product["name"];
}
require("html/footer.php");
?>
Hope this helps,
Cheers,
David.