[SOLVED] 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.

[Barand]

Why 6 tables?

[simon551]

I'm guessing you have some kind of script that resets total_today and total_yesterday to zero and total_today, respectively at 5pm. Then you just have to query those tables.

 

$query="SELECT * FROM total_today";

$result=mysql_query($query, $connection);

$today=mysql_num_rows($result);

[matthewst]

Barand:

6 tables because I'm an idiot

I've reduced to just the total_today table.

 

Simon551:

Since I've reduced the number of tables I'm hoping to just have:

$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

[Barand]

OK, we're agreed

 

MySQL datetime function mostly work with datetime type columns rather than unix timestamps so probably better to set the datetime ranges in PHP.

 

For example, count from yesterday at 5pm till now

 

<?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);
?>

[matthewst]

:D ;D

 

NOIIICE!!

Thank you!!!

 

 

Not to expose my noobness any further, but can you show me to show the count from yesterday, lastweek (friday at 5 to the following friday at 5), last month, last year, and the cupe de gras, year to date?

[matthewst]

Does any know how to get just the result from yesterday, last week, last month etc?

[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));

[Barand]

Here's a few more then

 

<?php
$_5pm = mktime(17,0,0);

//
// for $yesterday
//
$prev5pm = strtotime('-2 days', $_5pm);
$yest_5pm = strtotime('-1 days', $_5pm);

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

        
//
// 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 = "SELECT COUNT(*) 
        FROM total_today 
        WHERE date_time BETWEEN $prev_fri_5pm AND $last_fri_5pm ";

//
// day 0 is last day of previous 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'));

//
// last day of prev year
//
last_of_year = mktime(17,0,0,1, 0, date('Y'));
?>

[matthewst]

YOU ARE AWESOME!! If this site had a rep points system you would be top dog.

 

 

One final question. I've been fiddling around with this trying to get the magic year to date total.

Any suggestions?

//how do I get jan 1 07 to now
$daterange = "YEAR";

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

[Barand]

I gave you that one (well, 5pm on Dec 31st, 2006

//
// last day of prev year
//
$last_of_year = mktime(17,0,0,1, 0, date('Y'));

 

so you need

$now = time();

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

[matthewst]

;D ;D ;D

 

Thanks Barand!!