SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict
    Join Date
    Jan 2005
    Location
    england
    Posts
    328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    search lowest price mysql query

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

    PHP Code:
    $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. #2
    SitePoint Enthusiast
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    88
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •