Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2010

    Unanswered: How to return all queries if user specifies all


    I am trying to run a MySQL query against a property database from an html form drop down list. This is fine if the user selects one of the specific drop down values since i can compare the value against the value stored in the database. It is also fine if the user selects 'Show All' from one of the numeric fields but how do I code the MySQL if the user selects 'Show All' against a text column? For example if they want to see properties > 500 with at least 2 bedrooms but for ANY PROPERTY TYPE?

    I have provided the SQL below which works if a property type is specified, but obviously won't work if 'All types' is selected

    HTML Code:
    <p>Property Type 
    <select name="proptype" size="1">
    <option value="Show all properties">All types</option>
    <option value="detached">detached</option>
    <option value="semi-detached">semi-detached</option>
    $sql = 
    "SELECT, table1.price, table1.beds, table1.type
    FROM table1 WHERE 
    (table1.price >= $minprice) AND
    (table1.price <= $maxprice) AND
    (table1.beds >= $bedrooms) AND
    (table1.type = '$proptype')";
    Any help much appreciated

    Thanks a lot


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    in your query, the WHERE clause conditions act as filters

    if the user selects the "all" option for a particular data item, then what you do is simply exclude the filter condition for that item, and you automatically get all | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009
    San Sebastian, Spain
    As r937 has mentioned the WHERE filters the results returned. There are two posibilities:
    1. Dynamically generating the where clause using something like:

    $sql = "SELECT, table1.price, table1.beds, table1.type FROM table1";
    $where = "";
    if(isset($minprice) && $minprice >= 0) {
      $where .= ($where == "")? " (table1.price >= $minprice)": " and (table1.price >= $minprice)";
    if(isset($maxprice) && $maxprice >= 0) {
      $where .= ($where == "")? "(table1.price <= $maxprice)": " and (table1.price <= $maxprice)";
    if(isset($beds) && $beds > 0) {
      $where .= ($where == "")? "(table1.beds <= $bedrooms)": " and (table1.beds <= $bedrooms)";
    if(isset($type) && $type != "") {
      $where .= ($where == "")? "(table1.type = '$prototype')": " and (table1.type = '$prototype')";
    $sql .= (($where == "")?"":" WHERE" . $where);

    2. Write the SQL as follows:

    I am assuming that all the PHP variables are set to "" if no value has been added.

    $sql = 
    "SELECT, table1.price, table1.beds, table1.type
    FROM table1 WHERE 
    ('$minprice' = '' or table1.price >= $minprice) AND
    ('$maxprice' = '' or table1.price <= $maxprice) AND
    ('$bedrooms' = '' or table1.beds >= $bedrooms) AND
    ('$prototype' = 'Show all properties' or table1.type = '$proptype')";
    Last edited by; 01-02-11 at 18:49.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    Follow me on Twitter

Posting Permissions

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