Page 1 of 1

Date Calculations

Posted: Thu Jun 15, 2006 7:24 am
by acidHL
Hi guys i'm currently doing the following to find the number of entries in a database from the previous month.

Code: Select all

$lastmonth = mktime(0,0,0,date("m")-1,1,date("Y"));
$lastmonth = date("m", $lastmonth);
				
$clicks_lm_sql = mysql_query("SELECT COUNT(*) FROM `tracking` WHERE MONTH(date)={$lastmonth} AND `advert_id`={$advert['id']}");
$clicks_lm = mysql_fetch_row($clicks_lm_sql);
echo $clicks_lm[0];
The problem is that if we are in january, wont it return results from the same year, rather than december of the previous year?
Do anybody have any idea how to get around this? The date field is in mysql date format, and ideally i'd like to keep it that way.

Posted: Thu Jun 15, 2006 7:33 am
by acidHL
Ah, never mind I sorted it out, I think...

This will work wont it?

Code: Select all

$lastmonth = mktime(0,0,0,date("m")-1,1,date("Y"));
$lastmonth = date("m", $lastmonth);
$current_year = date("Y");
				
if ($lastmonth == 12)
{
    // We're on the previous year, lets move back one:
    $year = $current_year - 1;
} else {
    $year = $current_year;
}

$clicks_lm_sql = mysql_query("SELECT COUNT(*) FROM `tracking` WHERE MONTH(date)={$lastmonth} AND YEAR(date)={$year} AND `advert_id`={$advert['id']}");
$clicks_lm = mysql_fetch_row($clicks_lm_sql);
echo $clicks_lm[0];

Re: Date Calculations

Posted: Thu Jun 15, 2006 7:34 am
by tecktalkcm0391
acidHL wrote:Hi guys i'm currently doing the following to find the number of entries in a database from the previous month.

Code: Select all

$lastmonth = mktime(0,0,0,date("m")-1,1,date("Y"));
$lastmonth = date("m", $lastmonth);
				
$clicks_lm_sql = mysql_query("SELECT COUNT(*) FROM `tracking` WHERE MONTH(date)={$lastmonth} AND `advert_id`={$advert['id']}");
$clicks_lm = mysql_fetch_row($clicks_lm_sql);
echo $clicks_lm[0];
The problem is that if we are in january, wont it return results from the same year, rather than december of the previous year?
Do anybody have any idea how to get around this? The date field is in mysql date format, and ideally i'd like to keep it that way.
If you want it to get it from the last month even it its Jan. do this:

Code: Select all

$lastmonth = mktime(0,0,0,date("m")-1,1,date("Y"));
if(date("Y")==January){
$lastmonth = mktime(0,0,0,date("m")-1,1,date("Y")-1));
$lastmonth = date("m", $lastmonth);
} else {
$lastmonth = date("m", $lastmonth);
}
				
$clicks_lm_sql = mysql_query("SELECT COUNT(*) FROM `tracking` WHERE MONTH(date)={$lastmonth} AND `advert_id`={$advert['id']}");
$clicks_lm = mysql_fetch_row($clicks_lm_sql);
echo $clicks_lm[0];
Its untested, but give its a shot

Posted: Thu Jun 15, 2006 7:37 am
by bmcewan
Maybe if you try using strtotime();

Code: Select all

$cdate = explode('/', date('d/m/Y'));
$lastmonth = strtotime("-1 month", mktime(0, 0, 0, $cdate[1], 1, $cdate[2]));

Posted: Thu Jun 15, 2006 10:36 am
by acidHL
Ah thats an idea...

On a related note, can anyone suggest a way to get DISTINCT month's from the database taking the year into account in a similar way?
This is on the same table.

Posted: Thu Jun 15, 2006 10:40 am
by pickle

Code: Select all

SELECT
  DISTINCT(MONTH(date)) as 'month'
FROM
  tracking
WHERE
  YEAR(date) = '$last_year'