homepage Welcome to WebmasterWorld Guest from 54.211.113.223
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Please Help with Query
can't get the between statement to work
chasehx

5+ Year Member



 
Msg#: 4077214 posted 9:14 pm on Feb 9, 2010 (gmt 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?

 

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4077214 posted 9:31 pm on Feb 9, 2010 (gmt 0)

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.

chasehx

5+ Year Member



 
Msg#: 4077214 posted 10:07 pm on Feb 9, 2010 (gmt 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..

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4077214 posted 10:15 pm on Feb 9, 2010 (gmt 0)

You'll probably need to convert them to DATETIME values.

chasehx

5+ Year Member



 
Msg#: 4077214 posted 10:23 pm on Feb 9, 2010 (gmt 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.

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4077214 posted 10:30 pm on Feb 9, 2010 (gmt 0)

Try convert($date1,date)

chasehx

5+ Year Member



 
Msg#: 4077214 posted 10:48 pm on Feb 9, 2010 (gmt 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.

chasehx

5+ Year Member



 
Msg#: 4077214 posted 11:17 pm on Feb 9, 2010 (gmt 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.

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4077214 posted 11:42 pm on Feb 9, 2010 (gmt 0)

Just to confirm- the date_added field is a DATETIME or TIMESTAMP field?

rocknbil

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



 
Msg#: 4077214 posted 1:21 am on Feb 10, 2010 (gmt 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]

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved