I have to make a dynamic SQL statement where people have the option of choosing from 3 drop downs. An example of the choices is.

Show ALL model(s) in ANY state within ANY price range.

Now ALL and ANY can be replaced by values like

Show Mustang Stage 1 model(s) in Florida state within 20,000 to 29,999 price range.

What would be the best way to handle this situation? Should I write many different SQL statements to account for every different possibility or is there an easier way to account for ANY and ALL and 20,000 to 29,999, etc..?

Thanks!
😃

    well the any statements are easy, just select all the cars.

    I see no reason you would need to use a different sql statement. I mean you can build your SQL statements dynmically but simply inserting the POST values for the state or price ranges (look at the MySQL manual about BETWEEN syntax.) However, I have to assume your tables are setup in the normal fashion. If not, please post your table structures.

    I guess I owe a little more explanation on what I percieve here:

    I take it from what you have posted that you will always have 3 POST variables: Model, State, Price Range.

    If you get an ANY for any one of these, exclude it from your WHERE query. In other words, if it's not a factor in the drill-down, just leave it out.

      Yes, but "Leaving it out" requires a different SQL statement, which is exactly what I am trying to avoid.

      if I have the SQl statement

      SELECT * FROM tblVehicles WHERE State= '$State' AND Price = '$Price' AND Model = '$Model' ORDER BY $Order

      then you can see that if any of those variables = ANY or ALL the SQL will fail...the SQL WOuld have to be written differently for each case.

        You can write an if statement can't you? Can't you see how if you exclude state, for example, when the corresponding value is 'ANY' that your query suddenly is not concerned with that stipulation? In other words, write your ONE query dynamically.

          Ok, reading this post again, I think you are asking how to write the query dynamically. Which is a fair question. I've always just built a dynamic query in this situation because the alternative would be insane. I've used a tip from phpMyAdmin to deal with a variable number of WHERE statements: make an initial condition that is always true, so the subsequent conditions can all be ANDS:

          $state = mysql_escape_string($_POST['state']);
$model = mysql_escape_string($_POST['model']);
$price = mysql_escape_string($_POST['price']);
$sql = "SELECT * FROM tblVehicles WHERE 1=1 "; //always true but gives you the intinial WHERE.
$sql .= ($state != 'ANY') ? " AND State= '$state'" : "";
$sql .= ($model != 'ALL') ? " AND Model= '$model'" : "";
$sql .= ($price != 'ANY') ? " AND Price= '$price'" : "";
$sql .= " ORDER BY $Order";

            Thanks a lot Brett. That was exactly what I was looking for and it seems to work great. You seem to be real handy with SQL so maybe you can also help me figure this out.

            I need to do a Join and I really have no clue about Joins...Inner outer etc..

            I have a tblVehicles which has all the Vehicle info. It also has a DealerID which relates to the tblDealers that has all the info for each dealer. In the tblDealers is a field called DealerState.

            In my SQL I need to do a search for a certain model car which is in the tblVehicles and I also need to search by State, which is in the tblDealers.

            How would I go about doing a Join on those two tables to ge the need results?

            Here is an example of the current SQL I am using....what you gave me.

            $sql = "SELECT * FROM tblVehicles WHERE 1=1 "; //always true but gives you the intinial WHERE. 
$sql .= ($State != 'Any') ? " AND State= '$State'" : ""; 
$sql .= ($Model != 'All') ? " AND Model= '$Model'" : ""; 
$sql .= " ORDER BY $Order LIMIT $Offset,$Limit";

            Currently this gives an error because there is no State field in the tblVehicles.

            I am thinking it should look something like

            $sql = "SELECT * FROM tblVehicles INNER JOIN ON tblDealers WHERE 1=1 "; //always true but gives you the intinial WHERE. 
$sql .= ($State != 'Any') ? " AND State= '$State'" : ""; 
$sql .= ($Model != 'All') ? " AND Model= '$Model'" : ""; 
$sql .= " ORDER BY $Order LIMIT $Offset,$Limit";

            But of course that is also wrong.

            Thanks for the help.😃

              Never mind, I figured it out. Din't hav eto use a JOIN after all.

              $sql = "SELECT * FROM tblVehicles,tblDealers WHERE 1=1"; //always true but gives you the intinial WHERE. 
$sql .= " AND tblVehicles.DealerID = tblDealers.DealerID AND tblDealers.DealerState= '$State'";
$sql .= ($Model != 'All') ? " AND tblVehicles.Model= '$Model'" : ""; 
$sql .= " ORDER BY $Order LIMIT $Offset,$Limit";
                Write a Reply...