MySQL Update - Am I going mad?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Crew
Forum Newbie
Posts: 16
Joined: Fri Jul 15, 2005 4:05 am

MySQL Update - Am I going mad?

Post by Crew »

I have a load of dates in my database and want to increase them all by one week. When I test the mysql code in phpMyAdmin it works for the single date, but when using the following code it just makes all of the dates the same.

Code: Select all

$miss_date = $_POST['miss_date'];	// The first date to miss
$start_date = $_POST['start_date'];	// The date to come back on
list($match_year, $match_month, $match_day) = split('[:.-]', $start_date);
$start_date_timestamp = mktime(0,0,0,$match_month,$match_day,$match_year);
?>
<table class="tborder" cellpadding="6" cellspacing="1" border="0" width="100%">
	<tr class="thead">
		<td align="center" nowrap="nowrap">Information</td>
	</tr>
	<tr class="tbody">
		<td align="center" nowrap="nowrap">
<?
		$query = mysql_query("SELECT DISTINCT date FROM $league ORDER by date ASC") or die(mysql_error());
		$num=mysql_num_rows($query);
		$i=0;
		while ($i < $num) {
			$sql_date=mysql_result($query,$i,"date");
			if($sql_date >= $miss_date) {
				$date_from_timestamp = date('Y-m-d',$start_date_timestamp);
				echo "Change Date - ".$sql_date." to ".$date_from_timestamp."<br>";
				mysql_query("UPDATE $league SET date = '$date_from_timestamp' WHERE date='$sql_date'") or die(mysql_error());
				$start_date_timestamp = strtotime('+7 days',$start_date_timestamp);
			}
			$i++;
		}
		?>
		</td>
	</tr>
</table>
The thing that really confuses me is that the echo actually displays the correct data and what I'm trying to do.
Crew
Forum Newbie
Posts: 16
Joined: Fri Jul 15, 2005 4:05 am

Post by Crew »

The echo shows:

Change Date - 2005-10-17 to 2005-10-24
Change Date - 2005-10-24 to 2005-10-31
Change Date - 2005-10-31 to 2005-11-07
Change Date - 2005-11-07 to 2005-11-14
Change Date - 2005-11-14 to 2005-11-21
Change Date - 2005-11-21 to 2005-11-28
Change Date - 2005-11-28 to 2005-12-05
Change Date - 2005-12-05 to 2005-12-12

but all the dates have gone to

2005-12-12

Why is the WHERE date='$sql_date' not working?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you're compacting the updates.. run the dates in reverse.

Why not use ADDDATE() ?
ryanlwh
Forum Commoner
Posts: 84
Joined: Wed Sep 14, 2005 1:29 pm

Post by ryanlwh »

Crew wrote:The echo shows:

Change Date - 2005-10-17 to 2005-10-24
Change Date - 2005-10-24 to 2005-10-31
Change Date - 2005-10-31 to 2005-11-07
Change Date - 2005-11-07 to 2005-11-14
Change Date - 2005-11-14 to 2005-11-21
Change Date - 2005-11-21 to 2005-11-28
Change Date - 2005-11-28 to 2005-12-05
Change Date - 2005-12-05 to 2005-12-12

but all the dates have gone to

2005-12-12

Why is the WHERE date='$sql_date' not working?
Look at the first column and compare it to the second column of the previous row :)
Post Reply