Whats wrong with this query, it's returning the wrong value...

[MoreBloodWine]

SELECT COUNT(*) FROM `ucard` WHERE `registered` = time('%e%b%Y')

Total DB records is 3800 with two of those records whos `registered` time matches todays date. So if this was working right it should as of right now be returning a value of 2 and not 3798 which is the total value minus those with todays date.

The values stored in the `registered` row are in a unix format.

Anyway, what am I doing wrong with this ?

My guess is that it's rather simple and staring me right in the face but I'm just not seeing it ;-/

Edit: As far as functionality goes idealy it should display daily totals for the number of records whos `registered` time matches the current day.

So if two people register tomorrow (thursday) the total for that day reads 2 and increases with each new registration then on friday the count reads 0 and begins again etc. etc. etc.

Edit: I thought the below might work but it turns out it didn't... instead of returning a 0 or any number for that matter it didn't return a thing.

   $request = mysql_query("SELECT COUNT(`registered`) FROM `ucard` WHERE date('njY', strftime(`registered`)) = date('njY')");
   list($dailymcount) = mysql_fetch_row($request);
   mysql_free_result($request);

[hidden69]

SELECT COUNT(*) FROM `ucard` WHERE `registered` = time('%e%b%Y')

Total DB records is 3800 with two of those records whos `registered` time matches todays date. So if this was working right it should as of right now be returning a value of 2 and not 3798 which is the total value minus those with todays date.

The values stored in the `registered` row are in a unix format.

Anyway, what am I doing wrong with this ?

My guess is that it's rather simple and staring me right in the face but I'm just not seeing it ;-/

Edit: As far as functionality goes idealy it should display daily totals for the number of records whos `registered` time matches the current day.

So if two people register tomorrow (thursday) the total for that day reads 2 and increases with each new registration then on friday the count reads 0 and begins again etc. etc. etc.

Edit: I thought the below might work but it turns out it didn't... instead of returning a 0 or any number for that matter it didn't return a thing.

   $request = mysql_query("SELECT COUNT(`registered`) FROM `ucard` WHERE date('njY', strftime(`registered`)) = date('njY')");
   list($dailymcount) = mysql_fetch_row($request);
   mysql_free_result($request);

I think you should look a little bit into mysql time/date functions.

FROM_UNIXTIME(unix-timestamp) converts unix-time format into mysql-time format
UNIX_TIMESTAMP(mysql-time/date) converts from mysql-date/time format into unix-date/time format

date() and time() within a sql-statement is interpreted by mysql and not by php, so the date() in your example trys to extract the date from a mysql-date/time format, but you have a unix-format.

the same applies to strftime() which is a php function but not a mysql, this cant work.

[MoreBloodWine]

Well, I understand very little PHP and even less of SQL, so ya, probably both lol. In any case, someone somewhere else suggested I post this for the row in question.

[MoreBloodWine]

Problem appears to have been solved with the following query, will know more come a new add to see if the count goes up and come midnight to see if the count reads 0.

$request = mysql_query("SELECT COUNT(*) FROM `ucard` WHERE DATE(FROM_UNIXTIME(registered)) = DATE(NOW())");