Query a database with 'any' criteria?

[paulboreham]

Hi all,

I have made a form in PHP where the user can query a mySQL database and the results returned are based on the selections.

On some selections, if the user desires, they can choose 'ANY' from the drop down box. How can I then query this on the results page in PHP?

At the moment the SQL query is for example =

'where variable=querystring' (obviously in proper SQL!)

But if the querystring is blank or 'ANY' then it will not query correctly...

I hope you can understand how I have written this!?

Cheers, Paul

[drew010]

i think you will need an if statement to check if it is ANY or blank, and in that case issue a query with no where clause, otherwise use the where clause with the query string.

[paulboreham]

Originally posted by drew010
i think you will need an if statement to check if it is ANY or blank, and in that case issue a query with no where clause, otherwise use the where clause with the query string.

Thanks Drew, I guessed as much. Just hoped there would be a 'lazy' way to do it in PHP!

[RossC0]

Try:

"WHERE fieldname LIKE '".$dropdown_Selected_Value."%'"

Then for the any option in the drop down box - just set its value to '' and then it will run the query with the like '%' which will retrieve all.

However, that isn't good practise because your not really filtering anything.

So the best option would be to not include the WHERE clause if the user selects any.

One method might be to set the any option value = '-1';

Then do an if statement checking to see if the posted value neq to false - if so append the where clause to the sql.

Hope that helps!

[paulboreham]

Cheers RossCo, but I cannot get that to work. 🙁

Basically, the form the user selects from has 6 drop-down options on it to select a type of property they would like. Any of these options can be ANY (or Dont Mind etc) in regards to things like number of Bedrooms, Bathrooms etc.

I've tried to implement the things you suggested, but wether its my newbieness (is that a word?!) to PHP, I cannot get it to work...

Any other ideas?

Here is the SQL query - all the calls are to the relevant $_POST variable from the form.

SELECT * FROM property_eng WHERE property_eng.propArea='%s' AND property_eng.propType='%s' AND property_eng.propPrice>=%s AND property_eng.propPrice<=%s AND property_eng.propBedrooms='%s' AND property_eng.propBathrooms = '%s' AND property_eng.propPool='%s'"

Thanks for any help...

Paul.

[RossC0]

OK you need to set up the form so that the select option for 'ANY' is equal to '-1' (false);

Here I just used the table field names for the form post vars

$sql = 'SELECT * FROM property_eng WHERE ';
// Flag to see if need to include an AND statement.
$and = false;
if ($propArea){
  $sql .= " property_eng.propArea = '$propArea ' " ;
$and = true;
}
if ($propType ){
  if ($and) {$sql .= ' AND ';}
  $sql .= " property_eng.propType = '$propType ' " ;
  $and = true;
}
if ($propPriceMin){
    if ($and) {$sql .= ' AND ';}
  $sql .= " property_eng.propPrice >= $propPriceMin" ;
  $and = true;
}
if ($propPriceMax){
  if ($and) {$sql .= ' AND ';}
  $sql .= " property_eng.propPrice <= $propPriceMax" ;
  $and = true;
}
if ($propBedrooms){
  if ($and) {$sql .= ' AND ';}
  $sql .= " property_eng.propBedrooms= $propBedrooms" ;
  $and = true;
}
if ($propBathrooms ){
  if ($and) {$sql .= ' AND ';}
  $sql .= " property_eng.propBathrooms = $propBathrooms " ;
  $and = true;
}
if ($propPool){
  if ($and) {$sql .= ' AND ';}
  $sql .= " property_eng.propPool= $propPool" ;
}

That should create the correct sql statement for each variation.

I haven't catered for an instance where the user selects any for all options (it wont need a WHERE statement - but you can deal with it in a similar way as the AND statement 😉 )

[paulboreham]

phew Thanks so my RossC0. I will give that a go and let you know how I get on.

Thanks again, Paul

[paulboreham]

Hi RossC0...

OK, that kinda works - I am echo'ing the SQL string and it appears ok, but any value that is -1 is still being added to the string, rather than being skipped...

Its like "-1" is being seen as a string, rather than a negative value...

Any ideas?

Cheers, Paul

[RossC0]

Hi Paul,

oops! -1 is True :o - it should be 0 which is the equivalent to the boolean False value..

Zend type comparisions

[paulboreham]

Ha! Ha! Genius! Works perfectly now...

Thanks so much 🙂