Compare current date to another ...

[xProteuSx]

I am building a site that needs to update member accounts on a weekly basis.  At the moment I have a cron job set for 12:01 am on Monday, that resets five database fields for all of my members. The first field is the current week, the second field is the previous week, the third field is from two weeks ago, the fourth field is from three weeks ago, and the fifth field is from four weeks ago.  Obviously the cron job resets the first field to 0, and the data from this field is moved to field two (last week), and so on and so forth, and the data from the fifth field is just removed altogether.  However, this is really not preferred, because if/when I get 1000+ users, the cron job will take up a crapload of CPU as many mysql queries need to be executed for each account.

 

So, I am trying to implement a script that checks the last login date for a user, and if they have have not logged in since before the latest Monday, the Monday before that, the Monday before that, or the Monday before that, etc.

 

I have never really worked with dates, so I don't even know how to begin.

 

The last user login is stored as a DATA datatype in a mysql database, and is in the following format: YYYY-MM-DD.

 

Thanks in advance!

[xyph]

DATA, or DATE type?

 

So you want to remove users that have been inactive for over a week, and perform this action using a cron executed once a week?

[xProteuSx]

xyph,

 

Sorry, yeah ... DATE type, not DATA type.

 

Actually, the premise is to keep tabs on 'user scores' for the current week plus the previous four weeks.

 

So here's the task:

 

- determine current date

- get last login date

- if last login was previous to the last monday, but since 2 mondays ago (so its the first login of the week), do this:

 

(week 0 = current week)

 

week 1 = week 0

week 2 = week 1

week 3 = week 2

week 4 = week 3

week 0 = 0

 

- however, if the user has not logged in since 2 mondays ago, then you have to do this:

 

week 2 = week 1

week 3 = week 2

week 4 = week 3

week 0 = 0

week 1 = 0

 

Is this explanation enough?  Thanks 

 

[xProteuSx]

OK, let me make this more simple:

 

How can I determine, using PHP, the date of the last Monday???

[xyph]

You could normalize your database to make this way easier.

 

Keep scores in a separate table, along with the date they were recorded. Use the MIGHTY POWER OF SQL to create a query that grabs and groups the data you want, as you want it. You could even create a garbage collector that deletes scores older than 4 weeks (if the data won't be used any more - smaller tables = faster queries).

 

Here's my tables, with a bit of data. Obviously, change these to suit your needs, they're bare-bones.

--
-- Table structure for table `scores`
--

CREATE TABLE IF NOT EXISTS `scores` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `game_id` int(11) NOT NULL,
  `score` int(11) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;

--
-- Dumping data for table `scores`
--

INSERT INTO `scores` (`id`, `user_id`, `game_id`, `score`, `date`) VALUES
(1, 1, 1, 5000, '2012-04-12'),
(2, 1, 2, 2500, '2012-04-10'),
(3, 2, 1, 3500, '2012-04-04'),
(4, 1, 2, 7500, '2012-04-01'),
(5, 2, 2, 8000, '2012-03-28'),
(6, 1, 1, 400, '2012-03-29'),
(7, 1, 2, 10000, '2012-02-29'),
(8, 1, 3, 400, '2012-03-15'),
(9, 1, 2, 3500, '2012-03-21');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `name`) VALUES
(1, 'xyph'),
(2, 'proteus');

--
-- Constraints for table `scores`
--
ALTER TABLE `scores`
  ADD CONSTRAINT `scores_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

 

Here's the query I've strapped together... it may not be the most efficient way to do this, as I'm not an SQL expert, but I don't see anything that would really slow it down, besides the necessary date functions.

 

mysql> SELECT
    ->  SUM(`score`) as `total_score`,
    ->  COUNT(`id`) as `games_played`,
    ->  CEIL( DATEDIFF(@monday,`date`)/7 ) as `week`
    -> FROM
    ->  `scores`,
    ->  ( SELECT @monday := DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) ) as `last_monday`
    -> WHERE
    ->  `user_id` = 1 AND DATE_SUB(@monday, INTERVAL 4 WEEK) <= `date`
    -> GROUP BY
    ->  WEEK(`date`,1)
    -> ORDER BY
    ->  `week`;
+-------------+--------------+------+
| total_score | games_played | week |
+-------------+--------------+------+
|        7500 |            2 |    0 |
|        7900 |            2 |    2 |
|        3500 |            1 |    3 |
|         400 |            1 |    4 |
+-------------+--------------+------+
4 rows in set (0.00 sec)

 

And here's how it would look used with PHP

 

<?php

$id = 1;

$sql = new MySQLi('localhost', 'root', '', 'db');

$q =
'SELECT
	SUM(`score`) as `total_score`,
	COUNT(`id`) as `games_played`,
	CEIL( DATEDIFF(@monday,`date`)/7 ) as `week`
FROM
	`scores`,
	( SELECT @monday := DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) ) as `last_monday`
WHERE
	`user_id` = '.(int)$id.' AND DATE_SUB(@monday, INTERVAL 4 WEEK) <= `date`
GROUP BY
	WEEK(`date`,1)
ORDER BY
	`week`';

$r = $sql->query( $q );
if( $r === FALSE ) {
echo 'Query failed';
} else {
$data = array();
while( $row = $r->fetch_assoc() ) {
	$data[$row['week']] = array(
		'total_score'=>$row['total_score'],
		'games_played'=>$row['games_played']
	);
}
for( $i = 0; $i <= 4; $i++ ) {
	echo 'Total score for ';
	if( $i == 0 ) {
		echo 'current week: ';
	} else {
		echo $i.' week(s) ago: ';
	}
	if( !isset($data[$i]) ) {
		echo '<i>No games played</i>';
	} else {
		echo $data[$i]['total_score'].' over '.$data[$i]['games_played'].' game(s)';
	}
	echo '<br>';
}
}

?>

 

And the output

 

Total score for current week: 7500 over 2 game(s)<br>
Total score for 1 week(s) ago: <i>No games played</i><br>
Total score for 2 week(s) ago: 7900 over 2 game(s)<br>
Total score for 3 week(s) ago: 3500 over 1 game(s)<br>
Total score for 4 week(s) ago: 400 over 1 game(s)<br>

 

Hope this is what you were looking for.

[xyph]

Sorry for bump, edit time limit is up.

 

You don't need the ORDER BY clause in the query, PHP orders it for you, while dealing with empty weeks.

 

To explain what the query is doing line-by-line, in case you were interested.

SELECT
-- This line returns the sum of scores, after they've been grouped
SUM(`score`) as `total_score`,
-- This line returns the total number of entries in each group
COUNT(`id`) as `games_played`,
-- This line takes the difference in days between the previous Monday (calculated further down)
-- and the dates of the groups. It takes that number, divides it by 7 (to get weeks) and
-- rounds it up.
CEIL( DATEDIFF(@monday,`date`)/7 ) as `week`
FROM
-- Obvious why wer want this here
`scores`,
-- Putting this as a sub query in the FROM clause allows us to define a MySQL variable, and use
-- it in the query. This saves us from having to calculate the date of the previous Monday more
-- than once. It pretty much grabs the current numerical day of the week (0 = Monday), and subtracts
-- that amount of days from the current date.
( SELECT @monday := DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) ) as `last_monday`
WHERE
-- Restricts our results to a single user's score
`user_id` = *USER ID HERE*
AND
-- Makes sure the score's date is greater than or equal to 4 weeks before the previous Monday 
DATE_SUB(@monday, INTERVAL 4 WEEK) <= `date`
GROUP BY
-- Groups our results by the week number of the score's date. The second argument causes it to
-- calculate weeks starting Monday, rather than Sunday (default)
WEEK(`date`,1)