mysql search 'any' - Thought I should be using '%'

[c_shelswell]

Hi I've got a mysql query which works fine but I'm trying to make it so i don't have to make many querys and can rely on the one. I'm sure this is really easy but i'm struggling to find anything for it.

 

My query is

select * from properties where location='2' and rent_pcm between '300' and '1000' and bedrooms between '2' and '3' and furnishings='full'and type='flat'

 

Now providing the user selects a variable on the previous page the query is fine but there's also the option to search 'any' so the query could be changed to "location='any'" however the db doesn't hold 'any' so how do i make it just choose any location? I thought I should use % but that doesn't seem to work.

 

Thank for any help

[rhodesa]

Just leave off the location portion of the query:

select * from properties where rent_pcm between '300' and '1000' and bedrooms between '2' and '3' and furnishings='full' and type='flat'

[c_shelswell]

That'll mean I'll have to create my query using php i.e. if that data contains 'any' for location don't include it in the query. I had hoped I could just make one query that would cover all bases.

 

Cheers

[rhodesa]

well, you must be using PHP to put in those values right? it's pretty simple to dynamically build the query (and omit pieces).

 

can you post the code that builds this query?

 

(if PHP code is posted, a moderator should move this to PHP Help)

[c_shelswell]

it's pretty simple at the moment the $_POST data comes in to a function and creates the query below:

 

function getProperties($data)
{
$conn = db_connect();
$q = "select * from properties where location='".$data['location']."' and rent_pcm between '".$data['rentFrom']."' and '".$data['rentTo']."' and bedrooms between '".$data['minBeds']."' and '".$data['maxBeds']."' and furnishings='".$data['furnishings']."'and type='".$data['propType']."'";

$r = mysql_query($q) or die (mysql_error());

while($row = mysql_fetch_array($r, MYSQL_ASSOC))
{
	$res[] = $row;
}

 

I was just really hoping there would be a way to make a variable for the query be 'anything'. I was looking at mysql injection to see if i could search for "OR 1" but it didn't seem to work.

 

Cheers

[rhodesa]

it's a little more complicated, but not too bad:

function getProperties($data)
{
$conn = db_connect();
$q = "select * from properties where ";
if($data['location'] != 'any')
	$q .= "location='".$data['location']."' and ";
$q .= "rent_pcm between '".$data['rentFrom']."' and '".$data['rentTo']."' and bedrooms between '".$data['minBeds']."' and '".$data['maxBeds']."' and furnishings='".$data['furnishings']."'and type='".$data['propType']."'";

$r = mysql_query($q) or die (mysql_error());

while($row = mysql_fetch_array($r, MYSQL_ASSOC))
{
	$res[] = $row;
}

[c_shelswell]

yeah that's something like what i'd have expected - thanks.

 

Can't believe there's not a mysql thing like location='%'

[rhodesa]

wow...i'm out of it today...(while I still recommend building it my way) you can use:

 

select * from properties where location LIKE '%' and rent_pcm between '300' and '1000' and bedrooms between '2' and '3' and furnishings='full'and type='flat'

 

...you have to use LIKE to use %. but LIKE also slows down the query.