Possible problem with strtotime returning incorrect date 1

[jgd1234567]

Hi, i have a fantasy manager site with football players. The players are stored in the following table:

players:
- player_id
- transfer_player_id (if not 0 then then the player has been transferred)
- date_created (date the player was added)

Every week i run a script which runs through all the players who have not been transferred within the last week and inserts into another table to log this. The table has the following structure:

players_log:
- player_id
- date_created

Here is the code i run each week. It can only log the players if they were added before the previous saturday (mid-day) or if they have been transferred and the transferred player was added after the previous saturday (mid-day).

$last_time = strtotime('last saturday 12:00:00');
$week = 60 * 60 * 24 * 7;

if (time() - $week > $last_time) {
$last_time += $week;
}

$result = mysql_query("SELECT p.player_id FROM players p LEFT OUTER JOIN players tp ON p.transfer_player_id = tp.player_id WHERE p.date_created < $last_time AND (p.transfer_player_id = 0 OR (p.transfer_player_id != 0 AND tp.date_created >= $last_time))");

while ($player = mysql_fetch_array($result)) {
mysql_query("INSERT INTO player_log (player_id, date_created) VALUES ($player[player_id], " . time() . ")");
}

However i am having a few problems. Every now and again i get records inserted into the log table even though the player has been transferred weeks ago. I believe this comes down to this line:

$last_time = strtotime('last saturday 12:00:00');

I have checked the php site and it turns out this function has a few bugs with it. I applied my own hack to it to make sure it returned the last saturday and not the saturday before that but it possibly still has it's problems.

This is a really hard thing for me to test. I have been racking my brain around this for so long now and have tried to explain this problem as simple as possible. If anyone could help i would appreciate it alot.

Thanks

 

[PaulTEG]

use mysql inbuilt date functions such as CURDATE()

Paul
------------------------------------
Spend an hour a week on CPAN, helps cure all known programming ailments ;-)

 

[jpadie]

i have written this minimal test harness and it gives me the results that i would expect. are they what you would expect?

<?php
$year = date("Y");
$month = date("m");
$numDays = date ("t", strtotime("$year-$month-01"));
$times = array("00:00:00", "08:00:00", "12:00:00", "15:00:00", "18:00:00", "21:00:00");
for($day=1; $day<=$numDays; $day++){
	foreach ($times as $time){
		$dates[] = strtotime("$year-$month-$day $time");
	}
}
echo "<table border=\"1\">";
foreach ($dates as $date){
	echo "<tr>";
	echo "<td>".date("(l) Y-m-d h:i:s", $date) ."</td>";
	echo "<td>" .date("(l) Y-m-d h:i:s", strtotime("noon last Saturday",$date)) ."</td>";
	echo "</tr>";
}
echo "</table>";
?>

I note from the description in you post that it is unclear whether saturday noon is always the cutoff or whether it is always noon on the last saturday. so saturday 13h00 goes back one hour or one week plus one hour?

 

[jgd1234567]

Hi yeah seems to get the results i want. So are you saying i should use "noon last Saturday" instead of "last saturday 12:00:00"?

 

[jpadie]

i found the two to work in the same way. i'm just old fashioned.