Page 1 of 1

Problem with Query and using BETWEEN

Posted: Fri May 07, 2010 10:16 am
by cavalier33901
Hey guys, I'm new to php and came across a little problem that I was hoping you could help me out with or point me in the right direction.

Im creating a website wehere users can query a database of housing information. They have the option to search by zip, bed, bath, and between a certain price range or year built. The problem I run into is with the BETWEEN query. For zip, bed, bath, I use something like this: AND 2010wlee.SITEZIP LIKE '%$qstring[3]%'

This code works because if the user does not want to search by zip it does not cause an error with the query, however, when I try to use the same format for between: AND 2010wlee.HEATEDAREA BETWEEN '%$qstring[12]%' AND '%$qstring[13]%' I keep running into an error and when I remove the %% It works but the query requires some text input be entered before it runs.

Is There a way to make the BETWEEN query work the same as the Like, in the sense that if the user doesn't input data into the search bar, then the query wont be effected?

Thanks for any help
Will

and btw here is the full query function if that helps:

$query="SELECT 2010wlee.ADV_NUMBER, 2010wlee.PARCEL_ID, 2010wlee.FACE_AMOUN, 2010wlee.GISACRES, 2010wlee.LANDUSEDES, 2010wlee.LANDISON,
2010wlee.SITEADDR, 2010wlee.SITECITY, 2010wlee.SITEZIP, 2010wlee.JUST, 2010wlee.LAND, 2010wlee.BUILDING, 2010wlee.MAXBUILTY,
2010wlee.HEATEDAREA, 2010wlee.MAXSTORIES, 2010wlee.BEDROOMS, 2010wlee.BATHROOMS, 2010wlee.GARAGE, 2010wlee.POOL, 2010wlee.BOATDOCK,
2010wlee.SEAWALL, 2010wlee.S_1AMOUNT, 2010wlee.S_1DATE FROM 2010wlee

WHERE 2010wlee.FACE_AMOUN BETWEEN $qstring[0] AND $qstring[1] AND 2010wlee.SITEADDR LIKE '%$qstring[2]%' AND 2010wlee.SITEZIP LIKE '%$qstring[3]%'
AND 2010wlee.JUST BETWEEN $qstring[4] AND $qstring[5] AND 2010wlee.BUILDING BETWEEN $qstring[6] AND $qstring[7] AND 2010wlee.LAND BETWEEN
$qstring[8] AND $qstring[9] AND 2010wlee.MAXBUILTY BETWEEN $qstring[10] AND $qstring[11] AND 2010wlee.HEATEDAREA BETWEEN $qstring[12] AND $qstring[13]
AND 2010wlee.BEDROOMS LIKE '%$qstring[14]%' AND 2010wlee.BATHROOMS LIKE '%$qstring[15]%' AND 2010wlee.POOL LIKE '%$qstring[16]%' AND 2010wlee.S_1AMOUNT
BETWEEN $qstring[17] AND $qstring[18]";

Re: Problem with Query and using BETWEEN

Posted: Fri May 07, 2010 7:39 pm
by Christopher
There are several things that could be going wrong. You did not show what the string actually contains. That usually shows the error. Also, you should probably wrap your array variables in braces inside strings to makes sure they expand correctly, e.g. "2010wlee.HEATEDAREA BETWEEN '{$qstring[12]}' AND '{$qstring[13]}'"