Two things I would check.

First.. the values are coming from a post which means they are strings. You need to cast those strings to dates so the database knows it is evaluating dates not strings. It is possible that it will use a string to do a date compare.. not sure how BETWEEN will work when using a date field from the DB and comparing two strings.. it may allow for it though.

sql='SELECT COUNT(*) FROM registration_table WHERE lead_date BETWEEN date(\'$date1\') AND date(\'$date2\') AS totalcount';

Second....I would check that the incoming args are valid dates and in valid formats.

If $date1 = "Auguest 23 2007"

It will most likely error when you try to cast to a date format.

Check what the format is for dates for your database and make sure that the arguments come in in that format.

Ie... "2007-12-01"

If this doesn't solve your problem post back with the error message you are getting and maybe the possible values that the arguments come in.

[edited by: Demaestro at 7:15 pm (utc) on Oct. 31, 2007]

you should post your error message, but it may be that the syntax should go more like:
SELECT COUNT(*) AS totalcount FROM ...

Phranque was right...has the AS totalcount in the wrong spot.

Demaestro, i put the date(... around my variable just in case. The dates in my dbase table are in the yyyy-mm-dd format as i had to use the DATE SUB before....

Now, new problem....

I have a CASE statement.(see below) That i want to count from $date2 which is the last date in the date range, and count back 7 days to get the weekly count. Then for each of the 7 days, count the amount of records for each of the 7 specific days...

Now, the "weekscount" should return 26 if $date2 is set to 2007-10-21, but its returning 39, which is the 26 records, PLUS what ever has been entered after that....

So for some reason it is counting past the $date2...why would that be?

'SELECT nhm_associate, COUNT(nhm_associate) AS testcount,
SUM(CASE WHEN DATE_SUB(\''. $date2 . '\',INTERVAL 7 DAY) <= last_visit THEN 1 END) AS weekscount,
SUM(CASE WHEN DATE_SUB(\''. $date2 . '\',INTERVAL 1 DAY) = last_visit THEN 1 END) AS yesterday,
SUM(CASE WHEN DATE_SUB(\''. $date2 . '\',INTERVAL 2 DAY) = last_visit THEN 1 END) AS twodaysback,
SUM(CASE WHEN DATE_SUB(\''. $date2 . '\',INTERVAL 3 DAY) = last_visit THEN 1 END) AS threedaysback,

//...keep counting INTERVAL to 7 days...

FROM registration_table WHERE community=\'Sonata\' AND nhm_associate!=\'Not Specified\' GROUP BY nhm_associate';

Shouldn't it be.... ==

SUM(CASE WHEN DATE_SUB(\''. $date2 . '\',INTERVAL 1 DAY) == last_visit THEN 1 END) AS yesterday,
SUM(CASE WHEN DATE_SUB(\''. $date2 . '\',INTERVAL 2 DAY) == last_visit THEN 1 END) AS twodaysback,
SUM(CASE WHEN DATE_SUB(\''. $date2 . '\',INTERVAL 3 DAY) == last_visit THEN 1 END) AS threedaysback,

[edited by: Demaestro at 4:02 pm (utc) on Nov. 1, 2007]

Tried that and it doesnt work...give me a failed statement error....

this is nuts.... ill keep working and report back.

Can you post the exact error message you get? Like is it a syntax error? No such field name error...

[edited by: Demaestro at 5:23 pm (utc) on Nov. 1, 2007]

SQL statement that failed below:
---------------------------------------------------------
SELECT nhm_associate, COUNT(last_visit) AS testcount,
SUM(CASE WHEN DATE_SUB('2007-10-21',INTERVAL 7 DAY) <= last_visit THEN 1 END) AS weekscount,
SUM(CASE WHEN DATE_SUB('2007-10-21',INTERVAL 1 DAY) == last_visit THEN 1 END) AS yesterday,
SUM(CASE WHEN DATE_SUB('2007-10-21',INTERVAL 2 DAY) == last_visit THEN 1 END) AS twodaysback,
SUM(CASE WHEN DATE_SUB('2007-10-21',INTERVAL 3 DAY) == last_visit THEN 1 END) AS threedaysback,
SUM(CASE WHEN DATE_SUB('2007-10-21',INTERVAL 4 DAY) == last_visit THEN 1 END) AS fourdaysback,
SUM(CASE WHEN DATE_SUB('2007-10-21',INTERVAL 5 DAY) == last_visit THEN 1 END) AS fivedaysback,
SUM(CASE WHEN DATE_SUB('2007-10-21',INTERVAL 6 DAY) == last_visit THEN 1 END) AS sixdaysback,
SUM(CASE WHEN DATE_SUB('2007-10-21',INTERVAL 7 DAY) == last_visit THEN 1 END) AS sevendaysback,
SUM(CASE WHEN DATE_SUB('2007-10-21',INTERVAL 8 DAY) == last_visit THEN 1 END) AS eightdaysback
FROM registration_table WHERE community='Sonata' AND nhm_associate!='Not Specified' GROUP BY nhm_associate

---------------------------------------------------------

ERROR REPORT www.mysite.com: November 1, 2007, 1:55:09 pm

---------------------------------------------------------
SQL ERROR: SELECT nhm_associate, COUNT(last_visit) AS testcount,
SUM(CASE WHEN DATE_SUB('2007-10-21',INTERVAL 7 DAY) <= last_visit THEN 1 END) AS weekscount,
SUM(CASE WHEN DATE_SUB('2007-10-21',INTERVAL 1 DAY) == last_visit THEN 1 END) AS yesterday,
SUM(CASE WHEN DATE_SUB('2007-10-21',INTERVAL 2 DAY) == last_visit THEN 1 END) AS twodaysback,
SUM(CASE WHEN DATE_SUB('2007-10-21',INTERVAL 3 DAY) == last_visit THEN 1 END) AS threedaysback,
SUM(CASE WHEN DATE_SUB('2007-10-21',INTERVAL 4 DAY) == last_visit THEN 1 END) AS fourdaysback,
SUM(CASE WHEN DATE_SUB('2007-10-21',INTERVAL 5 DAY) == last_visit THEN 1 END) AS fivedaysback,
SUM(CASE WHEN DATE_SUB('2007-10-21',INTERVAL 6 DAY) == last_visit THEN 1 END) AS sixdaysback,
SUM(CASE WHEN DATE_SUB('2007-10-21',INTERVAL 7 DAY) == last_visit THEN 1 END) AS sevendaysback,
SUM(CASE WHEN DATE_SUB('2007-10-21',INTERVAL 8 DAY) == last_visit THEN 1 END) AS eightdaysback
FROM registration_table WHERE community='Sonata' AND nhm_associate!='Not Specified' GROUP BY nhm_associate
Server Type: Apache/1.3.39 (Unix) PHP/5.2.4 mod_auth_passthrough/1.8 mod_log_bytes/1.2 mod_bwlimited/1.4 mod_ssl/2.8.29 OpenSSL/0.9.7a
Request Method: POST
Query String: action=addon_builderreport_displaytestreport
Refereer: [mysite.com...]
User Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.8) Gecko/20071008 Firefox/2.0.0.8
Request URI: /admin/index.php?action=addon_builderreport_displaytestreport
POST Variables: array (
'date1' => '2007-10-15',
'date2' => '2007-10-21',
'submit' => 'Go',
)
GET Variables: array (
'action' => 'addon_builderreport_displaytestreport',
'printer_friendly' => false,
)