Search lowest price mysql query

PHP
1

Hi
how would i search for the lowest price and category
here is the code i have

$query = "select category,name, MIN(price) from products where name like '%$q%' GROUP BY name ";
	
$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
	echo "The cheapest  ". $row['name']. " is $" .$row['MIN(price)'];
	echo "<br />";
}

when i use the search form it fetches all items in the database, if i type in bread i want all items in the bread category at the lowest price.
thanks

2

You would set up a query that selects the item info you want where category = (your choice) and price = (the value of a sub-query). The sub-query would be something that you write to query for the min(price) value of the category = (your choice)

$q = "SELECT category, name, price from products a
JOIN (SELECT min(price) as minprice from products
WHERE category=‘$mycateg’) b
ON a.price=b.minprice and a.category=‘$mycateg’ ";

This should give you an idea. Been awhile so this may have an error in it.