Date Calculations

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
acidHL
Forum Commoner
Posts: 41
Joined: Wed Dec 07, 2005 7:38 am

Date Calculations

Post 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.
acidHL
Forum Commoner
Posts: 41
Joined: Wed Dec 07, 2005 7:38 am

Post 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];
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Re: Date Calculations

Post 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
User avatar
bmcewan
Forum Commoner
Posts: 55
Joined: Wed Jun 02, 2004 7:19 am
Location: West Yorkshire, UK.

Post 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]));
acidHL
Forum Commoner
Posts: 41
Joined: Wed Dec 07, 2005 7:38 am

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Code: Select all

SELECT
  DISTINCT(MONTH(date)) as 'month'
FROM
  tracking
WHERE
  YEAR(date) = '$last_year'
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply