Page 1 of 1

using mktime() help - how many users joined today

Posted: Sat Nov 11, 2006 8:37 am
by will83
Hi there,

I am trying to create somecode that will me how users have signed up today. The forum uses the unix timestamp when a new user joins. I have this code so far:

Code: Select all

$today = mktime(0,0,0,date("m"),date("d"),date("Y")); 

$today = date('dS M Y', $today);   

$query = "SELECT joindate FROM table WHERE joindate = '$today'"; 
$result = mysql_query($query, $conn); 
$todayusercount = mysql_num_rows($result); 

echo "<h1>$todayusercount of which have joined today!!</h1>";

However it is not working because it is looking for that particular timestamp and not a timestamp that falls within today's date.

Can anybody give a suggestion?

thanks, Will

Posted: Sat Nov 11, 2006 8:59 am
by RobertGonzalez
What version of MySQL are you running? There are some pretty nifty date functions that can help you with this in version 4.1+.

Posted: Sat Nov 11, 2006 9:05 am
by will83
Hi, not sure but assume it's 4 at least.

Posted: Sat Nov 11, 2006 9:12 am
by feyd
Can you run the following queries please?

Code: Select all

SELECT VERSION();
SHOW CREATE TABLE `table`;
Substitute the real table name for "table."

Posted: Sat Nov 11, 2006 2:20 pm
by califdon
There may be an easier way, but one thing you could do is create two variables, $today_min and $today_max:

Code: Select all

$today_min=mktime(,,,0,0,0)
$today_max=mktime(,,,11,59,59)
and in your SELECT statement use

Code: Select all

'...WHERE joindate => '$today_min' AND joindate =< '$today_max'
I haven't tried that, and the above may contain syntax errors, but you get the idea?

Don

Posted: Sat Nov 11, 2006 3:49 pm
by aaronhall
Should be:

Code: Select all

WHERE joindate >= '$today_min' AND joindate <= '$today_max'
EDIT: also, mktime would be:

Code: Select all

$today_min = mktime(0, 0, 0, date('m'), date('d'), date('Y'));
$today_max = mktime(11, 59, 59, date('m'), date('d'), date('Y'));