Problem with Query and using BETWEEN
Posted: Fri May 07, 2010 10:16 am
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]";
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]";