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!