Welcome to WebmasterWorld Guest from 54.235.46.164

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)

Junior Member

5+ Year Member

joined:Nov 17, 2009
posts:41
votes: 0


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)

Moderator from US 

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

joined:Dec 10, 2005
posts:5628
votes: 48


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)

Junior Member

5+ Year Member

joined:Nov 17, 2009
posts:41
votes: 0


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)

Moderator from US 

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

joined:Dec 10, 2005
posts:5628
votes: 48


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

Junior Member

5+ Year Member

joined:Nov 17, 2009
posts:41
votes: 0


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)

Moderator from US 

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

joined:Dec 10, 2005
posts:5628
votes: 48


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

Junior Member

5+ Year Member

joined:Nov 17, 2009
posts:41
votes: 0


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)

Junior Member

5+ Year Member

joined:Nov 17, 2009
posts:41
votes: 0


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)

Moderator from US 

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

joined:Dec 10, 2005
posts:5628
votes: 48


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

Senior Member

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

joined:Nov 28, 2004
posts:7999
votes: 0


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]