selecting records based on timestamp then counting the rows

[matthewst]

I'm trying to keep a running tally of (lets say) button clicks. Each time a user clicks a button I put a record in the database.

	$ad_now=(mktime()-21600);
$sql_today = "INSERT INTO total_today (but, date_time) VALUES ('1', '$ad_now')"; 
$result_today = mysql_query($sql_today);
$sql_yesterday = "INSERT INTO total_yesterday (but, date_time) VALUES ('1', '$ad_now')"; 
$result_daily = mysql_query($sql_yesterday);
$sql_weekly = "INSERT INTO total_weekly (but, date_time) VALUES ('1', '$ad_now')"; 
$result_weekly = mysql_query($sql_weekly); 
$sql_monthly = "INSERT INTO total_monthly (but, date_time) VALUES ('1', '$ad_now')"; 
$result_monthly = mysql_query($sql_monthly); 
$sql_yearly = "INSERT INTO total_yearly (but, date_time) VALUES ('1', '$ad_now')"; 
$result_yearly = mysql_query($sql_yearly); 
$sql_ytd = "INSERT INTO total_ytd (but, date_time) VALUES ('1', '$ad_now')"; 
$result_ytd = mysql_query($sql_ytd); 

What I can't figure out is how do I SELECT just the records from:
5:00 pm yesterday (end of workday) to now to echo for the $today variable in my page
5:00 pm two days ago to 5:00 pm yesterday to echo for the $yesterday variable in my page
Friday at 5:00 pm to the following friday at 5:00 pm for my $weekly variable
The last day of the month to the last day of the following month for my $monthly variable
Same for year and year to date.

I don't even know where to start, any help would be much appreciated.

[cahva]

Why on earth you have so many tables? I dont understand your logic at all: You put the same information to 5 different tables? Why not just one table with timestamp field and maybe a button name field. And better change that 'date_time'-field to actual timestamp or datetime field also and not use unix timestamp at all. Its easier to do selects with dates with normal dates(you can use different date functions straight in mysql).

Check out this section in mysql manual. Theres plenty of examples how to do a query within certain timeperiod:
Mysql date and time functions

[matthewst]

6 tables because I'm an idiot
I've reduced to just the total_today table.

I'm hopeing for something like:

$sql_today = SELECT * from total_today WHERE $timestamp_stuff = whatever it needs to equal to get todays count
count the number of rows returned
echo $numb_of_rows

$sql_yesterday = SELECT * from total_today WHERE $timestamp_stuff = whatever it needs to equal to get yesterdays count
count the number of rows returned
echo $numb_of_rows

and so on

[matthewst]

got it!!
now if I can just figure out how to get the count for yesterday, last week, last month, last year, and year to date I'll be set

<?php 
$yesterday5pm = strtotime ('-1 days', mktime(17,0,0));
$now = time();

$sql = "SELECT COUNT(*) 
        FROM total_today 
        WHERE date_time BETWEEN $yesterday5pm AND $now";

$res = mysql_query($sql);
$today = mysql_result ($res, 0, 0);
?>

[MattG]

Use this as your WHERE clause

SELECT COUNT(*) 
        FROM total_today
        WHERE DATE_SUB(CURDATE(),INTERVAL $daterange)
        <= FROM_UNIXTIME(date_time)

AND for $daterange just have it as how far you wish to go back for instance

12 HOUR
24 HOUR
7 DAY
1 MONTH
1 YEAR

[matthewst]

I tried this:

$daterange = "24";

$sql = "SELECT COUNT(*) 
        FROM ad_total_today 	
		WHERE DATE_SUB(CURDATE(),INTERVAL $daterange)
        <= FROM_UNIXTIME(date_time)";

$res = mysql_query($sql);
$today = mysql_result ($res, 0, 0);

But got this error:

Warning: mysql_result(): supplied argument is not a valid MySQL result resource

any ideas?

[MattG]

Yeah, you can't put just 24 you have to put one of the keywords on the end of the 24...

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR

So change the code to...

$daterange = "24 HOUR";

$sql = "SELECT COUNT(*) 
        FROM ad_total_today 	
		WHERE DATE_SUB(CURDATE(),INTERVAL $daterange)
        <= FROM_UNIXTIME(date_time)";

$res = mysql_query($sql);
$today = mysql_result ($res, 0, 0);

[matthewst]

Thanks MattG!!

This code works:

$daterange = "24 HOUR";

$sql = "SELECT COUNT(*) 
        FROM ad_total_today 	
		WHERE DATE_SUB(CURDATE(),INTERVAL $daterange)
        <= FROM_UNIXTIME(date_time)";		

$res = mysql_query($sql);
$today = mysql_result ($res, 0, 0);

But If I understand correctly "24 HOUR" will get the count for the last 24 hours. How do I structure it to get count from 5:00pm yesterday till now or just last week or last month not 7 days ago till now or 30 days ago till now?

[matthewst]

OK I got yesterday and today covered but how do I do last week, not 7 days ago, but last calendar week, last month etc?

$yesterday5pm = strtotime ('-1 days', mktime(17,0,0));
$yesterdaytill5pm = strtotime ('-2 days', mktime(17,0,0));

[MattG]

What you need to do is work some code out which determines the date of the monday to begin and insert that instead of CUR_DATE() but keep the $datarange as "7 Day".

One way you could do it (although probably not the best way) is this. (should work okay)

// Minus 1 from today because you don't want to take away Monday,
// you want to take away the day's since Monday
$today = date('N') - 1;

// Get the timestamp for Monday's date
$monday = strtotime("last Monday - 7 day");
$sunday = strtotime("last Sunday");

// Set date range to one week
$daterange = '7 DAY';

// Perform MySQL query
$sql = "SELECT COUNT(*) 
        FROM ad_total_today 	
		WHERE DATE_SUB($sunday,INTERVAL $daterange)
        <= FROM_UNIXTIME($monday)";		

$res = mysql_query($sql);
$today = mysql_result ($res, 0, 0);

[matthewst]

thanks mattg, I couldn't have figured it out without your help
now i just need the magic "year to date" code

//last week
$dow = date('w');
$daysago = $dow+2;
$last_fri_5pm = strtotime("-$daysago days", $_5pm);
$prev_fri_5pm = strtotime("-7 days", $last_fri_5pm);
$sql_week = "SELECT COUNT(*)
        FROM total_today
        WHERE date_time BETWEEN $prev_fri_5pm AND $last_fri_5pm ";
        $res_week = mysql_query($sql_week);    

//last month
$last_of_month = mktime(17,0,0,date('m'), 0, date('Y'));
$last_of_prev_month = mktime(17,0,0,date('m')-1, 0, date('Y'));        

$sql_month = "SELECT COUNT(*)
        FROM total_today
        WHERE date_time BETWEEN $last_of_prev_month AND $last_of_month ";
        $res_month = mysql_query($sql_month);    

//last year
$last_of_year = mktime(17,0,1, 0, date('Y'));
$last_of_prev_year = mktime(17,0,1, 0, date('y')-1, 0, date('Y'));
$sql_year = "SELECT COUNT(*)
        FROM total_today
        WHERE date_time BETWEEN $last_of_prev_year AND $last_of_year ";
        $res_year = mysql_query($sql_year);

[MattG]

For the past 365 days just set the date range as '1 YEAR'
For last year (i.e. Jan 1 2006 - Dec 31 2006) you should be able to use mktime

[matthewst]

GOT IT!!!!!!!
Thanks mattg!!

// last day of prev year

$last_of_year = mktime(17,0,0,1, 0, date('Y'));

$now = time();

$sql_ytd = "SELECT COUNT(*) 
        FROM total_today 
        WHERE date_time BETWEEN $last_of_year  AND $now ";
        $res_ytd = mysql_query($sql_ytd);

$ytd = mysql_result ($res_ytd, 0, 0);

echo "$ytd"