Page 1 of 1
de-incrementing value in database
Posted: Tue Apr 27, 2010 3:45 pm
by geoffmuskett
Hi all,
I'm trying to think of a way to either:
1. Automatically de-increment all values for a particular row in a table, in a database, by 1 per day. The value is a number between 0 and 100 and associated with a user. Most users will have different numbers. I guess I could run a script each day to do this but my memory is pretty rubbish!
or
2. Check when a user last logged in and de-increment their value by the number of days it has been since they last logged in.
Or maybe there's another way of doing it? I haven't tried anything significant yet as am not sure where to start!!
Any ideas?
Thanks,
Geoff
Re: de-incrementing value in database
Posted: Tue Apr 27, 2010 3:56 pm
by Christopher
Not sure what you are trying to do, but probably storing a timestamp and subtracting from the current time timestamp would be simpler.
Re: de-incrementing value in database
Posted: Tue Apr 27, 2010 4:57 pm
by geoffmuskett
Awesome, thanks for pointing me in the right direction!
I'm using this as a start:
Code: Select all
$today = getdate();
echo $today['yday'];
Thanks again
Re: de-incrementing value in database
Posted: Tue Apr 27, 2010 6:51 pm
by a94060
If you are trying to subtract dates, there is a mysql command which can subtract dates form each other I think. It may be mysql_subtract but I am not sure. As for subtracting one,do something like this:
Code: Select all
UPDATE MyTable SET MyColumn=MyColumn+1 Where MyID=123
I found that from this page
http://www.knowledgesutra.com/index.php ... 24676.html , I have done a different way but I don't remember the way I did it.
To check how long it has been since the user last logged in, have a column which stores the last logged in date, then do as I said above.
Some pages that might help:
http://dev.mysql.com/doc/refman/5.1/en/ ... n_datediff
Re: de-incrementing value in database
Posted: Wed Apr 28, 2010 10:02 am
by geoffmuskett
Great, subtracting dates seems perfect. The code I have so far is:
Code: Select all
$date1 = time();
$last_loggin_in_result = mysql_query("SELECT * FROM users WHERE username='$member_username'")
or die(mysql_error());
$logged_in = mysql_fetch_array( $last_loggin_in_result );
$date2a = $logged_in['last_login'];
$dateDiff = $date1 - $date2a;
$fullDays = floor($dateDiff/(60*60*24));
echo "Hi $member_username, the last time you logged in was $fullDays days ago";
The problem is (I think) the format for timestamp coming from the database. If $date2a is put in manually ($date2 = mktime(0,0,0,4,23,2010);) the code works.
The timestamp from the database outputs like this: 2010-04-23 14:36:18, so I guess I need to swap this round to match the format for mktime??
Any ideas how I would go about doing this?
Re: de-incrementing value in database
Posted: Wed Apr 28, 2010 3:44 pm
by Jonah Bron
No, instead, switch around what comes out of mktime. Have a look at the date function.
http://us3.php.net/manual/en/function.date.php
Re: de-incrementing value in database
Posted: Thu Apr 29, 2010 11:34 am
by geoffmuskett
Thanks Jonah, thats help me get this far...
I call the logged in timestamp from the database, change it to UNIX and compare it to the current UNIX. Then my plan was to run a mysql_query to update the database with the current timestamp so that next login will have the previous login's timestamp (hope that makes sense!).
However the mysql_query doesn't work, can anyone see what might be wrong from this..? There's no error message, it just doesn't update the database...
Code: Select all
// Collects data from table
$last_loggin_in_result = mysql_query("SELECT * FROM users WHERE username='$member_username'")
or die(mysql_error());
// puts data info into an array
$logged_in = mysql_fetch_array( $last_loggin_in_result );
echo "<p>last_login timestamp date is " . $logged_in['last_login'] . "</p>";
// gets the last_login timestamp and converts it to UNIX
$date2b = strtotime($logged_in['last_login']);
echo "The last_login UNIX is $date2b <br /><br />";
// Get current UNIX
$date1 = time();
echo "current time value is " . $date1 . " <br /> <br />";
// compare the two dates
$datediff2 = $date1 - $date2b;
echo "The difference is $datediff2 seconds <br /><br />";
// converts the number of seconds to 'XX days'
$elapsed_days = floor(($datediff2 / 60) / 60 / 24);
echo "which equates to " . $elapsed_days . " days<br /><br />";
mysql_query("UPDATE users SET last_login = (NOW() ) WHERE username = '$login_username'");
Re: de-incrementing value in database
Posted: Thu Apr 29, 2010 11:49 am
by mikosiko
where did you define the variable $login_username ? or you were trying to use the variable $member_username?
Re: de-incrementing value in database
Posted: Fri Apr 30, 2010 3:42 am
by geoffmuskett
Oh no, rookie mistake!! But then, I am a rookie!
Well spotted mikosiko, I just couldn't see what was going wrong.
Thanks to all for your help!