[SOLVED] Select Statement Help!

[darknessmdk]

I need help with a select statement. My statement is

 

$result = mysql_query("SELECT * FROM properties WHERE city = '$city' AND beds LIKE '%$beds%' AND baths LIKE '%$baths%' OR state = '$state' AND beds LIKE '%$beds%' AND baths LIKE '%$baths%' OR  zip = '$zip' AND beds LIKE '%$beds%' AND baths LIKE '%$baths%'  ORDER BY date DESC LIMIT 0,8");

 

What I am trying to do is select rows by state, city or zip and those results are further narrowed by how many bedrooms and baths the user selects. The problem If the user just wants out a city or state and ignores the beds and baths, it doesnt return any results.  I want it to.  Any suggestions?

[TheLostGuru]

I'm not sure if this will help, I'm pretty new to PHP, but couldn't you make a default beds and baths, so that if the user didn't select anything it just defaulted to whatever value you give it?

[darknessmdk]

You would think that would work wouldnt you. I set the default to 0 and it searchs for properties with 0 beds and 0 baths

[darknessmdk]

Can anyone help with this?

[chrisuk]

perhaps a long winded way but a possible stop gap would be to have a seperate select statement that is executed if only the  city is entered...so along the lines of if city is not empty AND beds OR baths are empty, just select from the DB where city matches the user input

 

 

[Psycho]

You need to create your query dynamically based upon the user input. Plus, that query is inefficient as you are repeatedly restating the clauses for beds and baths. You just need to put the locations within parenthetical operators.

 

Here is an example, but I would add a lot more error handling and validation of the variables.

 

<?php

$query = "SELECT *
         FROM properties
         WHERE (city = '$city' OR state = '$state' OR zip = '$zip') ";

$query .= ($beds!='')? "AND beds LIKE '%$beds%' "  : '' ;
$query .= ($baths!='')? "AND baths LIKE '%$baths%' " : '' ;

$query .= "ORDER BY date DESC LIMIT 0,8";

$result = mysql_query($query) or DIE (mysql_error());

?>

 

[darknessmdk]

I didnt know you could break up a query like that, never seen that before

[Psycho]

Break it up which way exactly? Do you mean using the parenthetical operators or the dynamic creation of the AND clauses?

 

Let me know if you have any questions. Otherwise, if this works for you please mark the post as solved.