appropriate sql statement for search

[charvie]

Hi,

I have page that allows the user to search using a "date range" AND "location".
I'm able to do this individually.
Its like, if the user searches using the date range (from begin date to end date) the results show.
Then if the user searches using a specific location (lets say USA) the results show.

but when the user searches using the date range AND the location, i get an error.

this is my code for the date range.

$data = mysql_query("SELECT * FROM table WHERE date BETWEEN '$beginDate' AND '$endDate' ");

and this is for the location

$data = mysql_query("SELECT * FROM table WHERE location = '$loc' ");

and this is for the combined range and location. (which generates an error)

$data = mysql_query("SELECT * FROM table WHERE date BETWEEN '$beginDate' AND '$endDate' AND  location = '$loc' ");

what should be the appropriate statement for this? Is it really ok to use 2 "AND" ?

[urtrivedi]

your query is absolutely fine. what error message you are getting.

[tyson.crouch]

Sorry urtrivedi you are right the query itself is correct, but i think the syntax isnt quite right. What i believe is happening is that the Between is lookin at the 2nd AND statement and misreading it.

Maybe try this:

$data = mysql_query("SELECT * FROM table WHERE (date BETWEEN '$beginDate' AND '$endDate') AND `location` = '$loc'");

By segmenting the WHERE statement you avoid having the MySQL engine from accidentally reading the query the wrong way.

I find that it is good practice to separate exclusive conditions to avoid any little mishaps.

Regards,
TC

[charvie]

waaaaa!!! thank you so so sooo much! its ok now ^^

thnx urtrivedi and tyson.crouch ♥♥♥

[tyson.crouch]

No problem.

Please mark as solved