Jump to content


Photo

COUNT from one date to another


  • Please log in to reply
4 replies to this topic

#1 JasonORDSW

JasonORDSW
  • Members
  • Pip
  • Newbie
  • 7 posts
  • LocationChicago, IL

Posted 30 September 2003 - 07:07 PM

I am running a members profile ssytem.. and I am trying to get a page to display the total of NEW members since a individual last logged on.

My current code is:

$query_newmembers = mysql_query("SELECT COUNT(*) FROM members WHERE TO_DAYS(stamp_approved)-TO_DAYS(\'$stamp_lastlogin2\')>0 AND status=\'A\' AND level IN (2,3,4)");

however it does not seem to be displaying the PROPER number of new members since a person last logged on (lastlogin2)

Can anyone help?

Thanks!

Jason[/code]

#2 shivabharat

shivabharat
  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 01 October 2003 - 05:28 AM

Whats the filed type of \"stamp_approved\" is that timestamp/datetime or date?

This is the synaxt for TO_DAYS

SELECT TO_DAYS(\'2003-10-01\');

Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks

#3 JasonORDSW

JasonORDSW
  • Members
  • Pip
  • Newbie
  • 7 posts
  • LocationChicago, IL

Posted 01 October 2003 - 03:32 PM

The Field Type for stamp approved and for lastlogin2 is datetime

I thank you in advance for your help!

Jason

#4 shivabharat

shivabharat
  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 02 October 2003 - 05:23 AM

If you have the filed type as time stamp you may need to extract the date alone used

SELECT COUNT(*) FROM members WHERE TO_DAYS(substring(<field_name>,1,11)) -TO_DAYS(\'$stamp_lastlogin2\') )>0 AND status=\'A\' AND level IN (2,3,4)");

ensure that \"$stamp_lastlogin2\" is in date format YYYY-MM-DD
Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks

#5 JasonORDSW

JasonORDSW
  • Members
  • Pip
  • Newbie
  • 7 posts
  • LocationChicago, IL

Posted 02 October 2003 - 05:29 AM

Both the stamp approved and the lastlogin2 are datetime fields.. I wanted to kep it this way.. so it has a more ACCURATE reporting for when you do certain functions on the site.

Looks like I will have to extract the date ONLY for both fields.. THANKS for your help!

Any other suggestions.. please pass them along!

Jason




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users