Welcome to WebmasterWorld Guest from 54.162.155.183

Forum Moderators: open

Message Too Old, No Replies

Please Help with Query

can't get the between statement to work

     
9:14 pm on Feb 9, 2010 (gmt 0)

5+ Year Member



Here is my query i am using:

$sql = "SELECT * FROM leads WHERE referrer_id = 24 AND date_added BETWEEN $date1 AND $date2 AND status_id = 5 ORDER BY id DESC";

My date format is as such 2010-02-01 and the time format of the database is a timestamp, such as 2010-02-01 05:12:25PM. I am not sure if it is this small difference that is making my query fail. The thing is, it does not give me a empty result set error, it just doesn't do anything. I know there are rows that match, but the page stays blank. Any suggestions?
9:31 pm on Feb 9, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



What are the values of @date1 and $date2? Are they actual date values or VARCHAR values? If VARCHAR, convert to DATETIME before doing the comparison.
10:07 pm on Feb 9, 2010 (gmt 0)

5+ Year Member



They are inputs from a JS calender on the previous page. the calender is configured to give the dates in the format mentioned, it just doesn't work..
10:15 pm on Feb 9, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



You'll probably need to convert them to DATETIME values.
10:23 pm on Feb 9, 2010 (gmt 0)

5+ Year Member



I tried using the convert(datetime, $date1) and it makes the query invalid. without it, the result is empty, if I have that, it throws an error. Is it because the date range I am using to compare doesn't have the H:i:s? I tried using date(Y-m-d H:i:s, $date1) but it doesn't work, sets the value of the variable to 1969, meaning it was invalid.
10:30 pm on Feb 9, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



Try convert($date1,date)
10:48 pm on Feb 9, 2010 (gmt 0)

5+ Year Member



what the hell removing the referrer_id clause makes the date range work. what is the problem with the where clause? it never gives me issues.
11:17 pm on Feb 9, 2010 (gmt 0)

5+ Year Member



Nevermind, it still doesn't work. I finally got the date function to convert it into the SQL friendly format without returning 1969, but still no rows show up. Even with the referrer clause removed I still can't get it. I tried convert, tried using UNIX_TIMESTAMP as the comparison, still nothing.
11:42 pm on Feb 9, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



Just to confirm- the date_added field is a DATETIME or TIMESTAMP field?
1:21 am on Feb 10, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



If you're talking about mySQL (presuming you are) the timestamp format is identical to datetime, it does not have an AM/PM and is a 24 hour clock.

If the incoming date is date only, try the below. If it includes a time with the AM/PM tacked on, you'll have to prepare the input string before querying, remove the textual part, and increment the hour if it's > 12.

$sql = "SELECT * FROM leads WHERE referrer_id = 24 AND date_added (BETWEEN '$date1' AND '$date2') AND status_id = 5 ORDER BY id DESC";

or this,

$sql = "SELECT * FROM leads WHERE referrer_id = 24 AND date_added (BETWEEN '$date1 00:00:00' AND '$date2 59:59:59') AND status_id = 5 ORDER BY id DESC";

or this

$sql = "SELECT * FROM leads WHERE referrer_id = 24 AND date_added >= '$date1 00:00:00' AND date_added <= '$date2 59:59:59' AND status_id = 5 ORDER BY id DESC";

remembering the single quotes won't affect the interpolation, they are "just another character," the PHP string delim's are ", but the quoting is needed by mySQL.

mySQL Date, Datetime, Timestamp [dev.mysql.com]
 

Featured Threads

Hot Threads This Week

Hot Threads This Month