Page 1 of 1

Simple PHP Date Comparison

Posted: Thu Mar 27, 2008 11:29 pm
by arunkar
Hi,

I'm very new to PHP. I'm trying count the number of registrations today and display in the web page. Below is the code I'm using but I'm unable to compare:

The date is the mysql database is stored as

Code: Select all

2008-03-27 21:09:41

Code: Select all

$today = date("Y-m-d H:i:s");
 
$resultCount = mysql_query("SELECT count(*) FROM users WHERE registerDate='$today' ");
its returning 0 but there are 2 registrations. So there is a logical error. I'm using the date functions here: http://sg2.php.net/date for reference.

Any ideas? :|

Thanks in advance.

Re: Simple PHP Date Comparison

Posted: Fri Mar 28, 2008 4:28 am
by aceconcepts
Try outputting $today and manually compare it with the dates in your database. Make sure they both use the same format.

I always find it useful and easier to seperate the date and time into seperate fields within the database.

Re: Simple PHP Date Comparison

Posted: Fri Mar 28, 2008 4:42 am
by EverLearning
arunkar wrote:

Code: Select all

$today = date("Y-m-d H:i:s");
 
$resultCount = mysql_query("SELECT count(*) FROM users WHERE registerDate='$today' ");
 
With this you're counting the number of registrations in that moment(since you included hour:minute:second part), which will most of the time be equal to zero.

To get registrations count for today, you need to format both comparing values (php variable $today, and mysql registerDate value) to be the same, and to only compare date without time part

Code: Select all

$today = date("Y-m-d"); //omited the time part
 
// format database date for comparing
$resultCount = mysql_query("SELECT count(*) FROM users WHERE  DATE_FORMAT(registerDate, '%Y-%m-%d')='$today' ");