MONTHLY COUNTER QUESTION (for PHP pro's)

crash_D. · December 2006

Hi.. i know that many of you know php well, and i need your help, guys.. i need to make a counter, which would nullify on the 00:00:01 of the first day of each month.. so,,, it would count the quantity of occurances of something during a month,, and then simply return to zero with the start of a new month... i am coding in php.. what would you advise?

i am a noob in php, however I can code relatively simple web-applications, so i think technical terms i will understand...

this will be an online book-keeping application for a furniture retailer.,.. I am using php and mysql on apache

hope for your help

thanks in advance

NickE · December 2006

I would store the month of the last counter increment somewhere, then check to see if the current month was different than the stored one. If it is, update month and reset counter to 0. Besides that it would probably be just incrementing a counter.

davidh · December 2006

Should be fairly easy. Make a two-column table in the database where you store a value between 1-12 representing the last month changed, and the current counter value. You have one php function that returns the counter value from the database, one function that increments that value, and one that updates the month changed and counter number. At the beginning of "return counter value", you simply check if (currentMonth != databaseMonth) and reset the counter and databaseMonth appropriately.

crash_D. · December 2006

thanks... basically, i had the same idea in my head... only the problem was that i could not put it to action.. i thought that there is maybe some generally accepted way of doing it.. thanks guys... i'll try..

chuyskywalker · December 2006

Getting a tad complicated there guys...

$current_year_month = date('Y-m');
$counter_for[$current_year_month]++;

Pol · December 2006

Is this in a Vanilla add-on, or in plain PHP?

crash_D. · December 2006

no.... not vanilla, just a php application..

and chuyskywalker, i did not understand what tried to say there,,, either it is wrong,,, or i am a newb... i do not need increment by one... i need a nullification ,, increment is not big deal,.. i can handle it by mysql,, if any of you guys could write a general algorithm, i would really appreciate it.. thanks

Vincent · December 2006

Basic table...

CREATE TABLE `time` (
...
`timestamp` INT(11) NOT NULL,
...
);

...and then you would use a basic insert to insert...

INSERT INTO `time` VALUES(..., UNIX_TIMESTAMP(), ...);

...then to find the values of each month, just use this...

<?php

$Month = 5;
$Year = 2006;

$Start = mktime(0, 0, 0, $Month, 1, $Year); 
if($Month == 12) {
$Month2 = 1;
$Year ++;
} else $Month++;
$End = mktime(0, 0, 0, $Month2, 1, $Year)-1;

$data = mysql_fetch_assoc(mysql_query("SELECT count(*) as Number FROM `time` WHERE time.timestamp > ".$Start." AND time.timestamp < ".$End.";"));

echo 'The Number of views for '.date('F', $Start).' was '.$data['Number'];

?>

...and BOOM, you have it, and it can be calculated for any month

wow, i haven't done something like this for a long time, though it only too me 5 min

crash_D. · December 2006

THANKS... only i don't understand the code above completely... here is how i made it:

function make_id() {
global $id;
$m=date('m');
$y=date('y');
settype($m, 'integer');
settype($y, 'integer');
$cq=db('SELECT * FROM cur_date LIMIT 1');
$cd=mysql_fetch_assoc($cq);
if (($cd['m']==$m)&&($cd['y']==$y)) db('UPDATE cur_date SET d=d+1');
else {
if (($cd['m']==12)&&($m==1)) {
db('UPDATE cur_date SET d=1, m=1, y=\'' .$y. '\'');
}
else db('UPDATE cur_date SET d=\'1\', m=\'' .$m. '\''); 
}
$nq=db('SELECT * FROM cur_date LIMIT 1');
$nd=mysql_fetch_assoc($nq);
$id=$nd['d']. '_' .$nd['m']. '_' .$nd['y']; 
return $id;
}

am i a newb in php? :P

Vincent · December 2006

m8... you code is too complicated, what don't you understand about mine??

crash_D. · December 2006

ok.. this application will be an inventory control system in the intranet... each order is given an id, which is a single consecutive number, month, and year.. so one of the id's might be 106_12_6, meaning that this is the 106'th order in December 2006. I simply needed a tool to generate this id, so i created a table cur_date with 3 parameters (columns), which are order (d), month (m), and year (y). And there is only one row of data, which changes just before assigning an id to every next order. So what i did is I made a funciton which checks whether it is the end of the year, if not, it checks whether we are still in the same month, and if yes, it increments the database value of d and assigns a new automatically generated id to an order.

What is unclear with your code is the mysql queries. I am not very good at mysql syntax, i only know the very basic functions. So this count(*) is unfamiliar. I looked it up at the mysql documentation, and it looks quite useful, however i have managed to create id function with the knowledge i have got so far. Thanks anyway.

btw, what do you think of my function. Is it decent or lame?

Vincent · December 2006

count(*) will return a number of all the rows possible returned in that query

and you function is a bit overload, you've got to find the quickest way to do things

also, UNIX_TIMESTAMP() for mysql is exactly the same as time(), but it allows the database to do it

jimw · February 2007

@Vincent: I tried you code and it works great on my local install. However, when I upload it to my live site, I get the error:

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

Here is the code I am using to count the discussions. Any help would be appreciated.


$data = mysql_fetch_assoc(mysql_query("SELECT count(*) as Number FROM ".$Context->Configuration['DATABASE_TABLE_PREFIX']."discussion;"));

MONTHLY COUNTER QUESTION (for PHP pro's)

Comments