Timestamp questions

[dean7]

Hey all, I'm coding a script which gets all the money what is in the game but I'm wanting it to only show the users who have been active in the past year.

So if you've been active your money adds on if you've not been online for a year it don't add the money on, if that makes sense?

 

Here is my little bit of code I'm working with:

$LastMove = mysql_query("SELECT last_action, last_login FROM users") or die(mysql_error());
$Move = mysql_fetch_object($LastMove);
 
$Movement = $Move->last_action;
 
 
$newTimestamp12 = strtotime('-1 years', $Movement);
$date3 = new DateTime("@$newTimestamp12");
$New3 = $date3->format('Y') . "\n";
 
 $stats=mysql_query("SELECT SUM(money),COUNT(id),SUM(exp) FROM users WHERE banned = 'n' AND active = 'y' AND last_action <= '$New3'");
 

 

Last_action is just time() which gets updated soon as them come online and are doing things on the site.

 

With my code, would that only show users who have been active for the past year?

 

Hope I've explained that well enough.

 

Thanks for any help provided

[cyberRobot]

MySQL has built in date functions. More information can be found here:

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

 

Also note the first example that uses INTERVAL

 

 

Side note: In case you are not aware, the mysql_* functions were removed in PHP 7.0. More information can be found here:

http://php.net/manual/en/mysqlinfo.api.choosing.php 

[benanamen]

You don't need two queries. Perhaps @Barand will show you.

[Barand]

I would show you if I weren't totally confused by what you are trying to do.

 

First you select last_action time for the first record in the table - why? That record could be several years old

 

When you say "active for the past year" does that mean within the last 12 months (Dec 21st 2015 - Dec 21st 2016) or active during 2016?

 

What is the structure of the "users" table. Is it one record per user, as the name implies?

[Psycho]

I have similar questions to Barand. Not sure what the query to get the last_action from the users table is for. It look slike you want to use that to determine who was active in the last "year" from that date. If your application/game is going to have long periods of inaction by any users, maybe it isn't successful? I would just query using all users where their last action was within one year from the current date.

 

Also, you state that last_active is set using time(). I assume you mean the PHP function time that returns a complete timestamp (which also identified a date) as opposed to the MySQL function that only returns a time (irrespective of date). If so, you are doing it wrong. Let MySQL do the work for you. Set the field up as a timestamp in the database with an onchange trigger to update the field automatically when making changes to the records. But, that means the table should only be used for tracking user activity since making any changes to a record (an admin updating banned status would update that field). So, the activity tracking should be in its own table.

 

But, if you want to use the users table, then you should update that value using the MySQL function NOW() as the value for the last_active field.

 

As I interpret your code, your current query would be returning all records where the last active date is less than (?) the "year" for the date you grabbed in the first query which, as Barand pointed out, has no relevance.

 

You should probably be using something like this:

 

SELECT COUNT(id) as userCount,
       SUM(money) as totalMoney,
       SUM(exp) as totalExp
 
FROM users
 
WHERE banned = 'n'
  AND active = 'y'
  AND last_action >= DATE_SUB(NOW(), INTERVAL 1 YEAR)

[Barand]

If you are storing the time as an integer timestamp then that last line of that query needs to use FROM_UNIXTIME to convert to a DATETIME type

... AND FROM_UNIXTIME(last_action) >= NOW() - INTERVAL 1 YEAR

[dean7]

 

If you are storing the time as an integer timestamp then that last line of that query needs to use FROM_UNIXTIME to convert to a DATETIME type

... AND FROM_UNIXTIME(last_action) >= NOW() - INTERVAL 1 YEAR

Yeah I am storing as a interger timestamp. So would this only show the users who have done something in the pass year? So users who haven't been on for 2-3-4 years won't be shown?

 

Thanks for all your help!

[Jacques1]

First off, you need to fix your data types. Unix timestamps and strings like “y”/“n” instead of actual booleans are amateurish, break type safety (the database system can no longer check if the values are even valid) and make the queries unnecessarily complex.

 

Timestamps are stored in DATETIME fields. Booleans are stored in BOOLEAN fields.

 

When that's done, you can do proper date calculations like in Psycho's post.