how to count user by date

[aerliana]

i need to count how many user that update their profile n how many user not update their profile within 5 years. and if the user not update their profile then i need send an email as alert notification.

[almostbob]

a database column for timestamp of the last update, filled on update with sql now()

select from table * where lastupdate < (now()-(5*365*24*60*60));

5*365*24*60*60 = 157680000 ~ 5 years of seconds

[aerliana]

i can`t get it.. can u explain more detail.. please...

[almostbob]

you require information about the time of the update
you put a column in the database, that autopopulates the lastupdate column with
now(), which is an sql construct that does what it looks like it does,
if the data management system is one developed by someone else as a saleable script, such function is probably built in

you then select from the database those records that match the selected date spans
inserting the returned values into an automailer is another question,
if the data management system is one developed by someone else as a saleable script, such function is probably built in

where the first reply did not make the answer pop out obviously,
I have to assume someone else wrote the dbms, so hopefully you got a manual, and the reporting function is written in the manual
fingers crossed on your behalf, sql is not as hard as it looks
good luck

[aerliana]

aren`t this statement for sql query??.. i looking for php..

[aerliana]

can anyone give me full coding

[Graphix]

At first: you need to have a record of somekind that registers when the users change their profile, for this you either need a very large file (not recommended) or a database. For example, you can create a table with MySQL:

CREATE TABLE users (
user_id INT(6) NOT NULL AUTO_INCREMENT,
username CHAR(255) NOT NULL,
password CHAR(255) NOT NULL,
e-mail CHAR(255) NOT NULL,
lastupdate DATE,
PRIMARY KEY (user_id) )

After that you need to compare the lastupdate with the current date. If their profile hasent changed for 5 years, they would get a mail, for example you can also make a php-file that you need to open manually to send the mail:

<?php
// DB-selection/connection
$q = "SELECT * FROM user WHERE lastupdate < now()-(5*365*24*60*60)";
$r = mysql_query($q);
while ($row = mysql_fetch_array($r)) {
mail($row['e-mail'], 'Profile 5 year', 'You havent changed your profile within 5 years');
}
?>

I hope this helps you on the way

[almostbob]

can anyone give me full coding

not without more assistance from you, nobody reading your posts has access to the existing code
database structure, field names, and what code you already have.
we are all giving guidelines,
Graphix' code work standalone
that may not work with what there is already
self-deprecating laughdont use any of the sql date formats, text dates are tedious to manipulate, they have to be converted to numeric for any operation which takes al lot of time and processing (relatively)
a timestamp takes 10bytes stores date and time in one column and can be mined for any part of the date and time, and need only be converted once on input
a text date time is
january 1 2009 10:45.22am (25 bytes) not a lot on 1 row, but a lot on a million rowsspeed test,
examine 10000 text records of dates
find dates between December 1 2008 at 12:15pm and 1 January 2009
echo $date;
- or -
examine 10000 numbers >1228133700 and <1230768000
echo date(d m y, $timestamp)bit overboard 'bout this ... :icon_wink: