[PHP-DB] RE: [PHP] date problem
SELECT COUNT(*) AS c FROM users_table WHERE UNIX_TIMESTAMP( user_regdate ) '1022882400' The only way you can do it with a char column is to select the entire database, load it into a PHP array, using strtotime() to (hopefully) convert May 29, 2002, etc, into a unix timestamp, and then sort by that timestamp. Alternatively you could use the query you are now (if its returning the correct subset of rows from the table). Replace COUNT(*) AS c with your primary key field (eg: id), and then use mysql_count_row() [rtfm for more details] rather than pulling the rows. From here it looks like your fastest option, but your not providing enough information. -- Dan Hardiker [[EMAIL PROTECTED]] ADAM Software Systems Engineer First Creative Ltd -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: [PHP] date problem
On Thu, 6 Jun 2002, andy wrote: I would like to count the users out of a mysql db who registered after a certain date. The column I have in the db is a char and I do not want to change this anymore. This is how a typical entry looks like: May 29, 2002 This is how I tryed it: // while '10...' is unix timestamp june 1, 02 SELECT COUNT(*) AS c FROM users_table WHERE UNIX_TIMESTAMP( user_regdate ) '1022882400' You can only call UNIX_TIMESTAMP on a DATE or DATETIME field, not on just any generic CHAR/VARCHAR/TEXT/whatever. May 29, 2002 isn't a MySQL timestamp, so I'm guessing you have a textual field type. The lesson of all this is: Convert dates to either unix or database-native date format before storing them in the database. Things like May 29, 2002 are useless in a database. At this point I'd recommend running a quick script to strtotime() all your dates and then re-write them to a new field that's in a proper format. miguel -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
