[Resolved] sql headache!

[owaring]

i run a phpbb system, i hve just migrated all of the data to a new server and database.

for some reason all user birthdays are now a day early!

not a problem i thought "i'll just write a simple script to add 1 to each db entry"

i figured (in rough code!!(cause its been a very long day!))

sql="SELECT `date` FROM users WHERE `user_id` = *"
$result = mysql_query($sql)
$row = mysql_fetch_array($result)

foreach ($row $user_id as &$date)
     {
     $date = $date++
     sql="ALTER `date` to '$date' WHERE `user_id` = $user_id "
     }

before getting as far as the foreach i tried the sql, and did a print_r() of the result but it only returned data for the first user

obviously my syntax in the first sql query is out, could someone please point me the right way!!

thanks

owaring

[eoghain]

Well to select every row in a table you just don't specifiy a where clause:

SELECT `date` FROM users

But there is no reason to do anything outside of a single sql query:

UPDATE users
SET date = date_add(date, INTERVAL 1 day)

check your database manual for the correct syntax to add 1 day to a date, but that is all you really need to do.

[owaring]

thanks eoghain

UPDATE users
SET date = date_add(date, INTERVAL 1 day)

having looked at the data in db the birthdays dont seem to be stored as dates, all are 4 char ints ie 4913 i couldnt find any date format that matched this so i figure it must reference form some point in the code.

any way with a fresh head this morning i came up with the following this morning.
Which worked a treat.

thanks for the help

$database="*********";	
	$db = mysql_select_db($database)
			or die ("Couldn't select database.");		//error message//	


$sql = "SELECT  `user_id` , `user_birthday`
	FROM `phpbb_users`";


$userresult = mysql_query($sql)   

    or die ("Couldn't execute user query."); 


while($row = mysql_fetch_array($userresult))
{
	if($row['user_birthday'] != 999999)//999999 being the default 
	{
	$bidet = $row['user_birthday'] + 1;

mysql_query("UPDATE `phpbb_users` SET `user_birthday` = '$bidet' 
WHERE `user_id` = '".$row 'user_id']."' LIMIT 1 ") 			or die ("Couldn't bidet query!!");

echo $row['user_id']." = ".$row['user_birthday']."/".$bidet."<br>";
}

}

[eoghain]

Great if that worked for you. But you do know that you can work with the data in your database directly and not have to write a php script to do the work don't you?

Cause what you just did could have been completed again with a single query:

UPDATE phpbb_users 
SET user_birthday = user_birthday + 1
WHERE user_birthday != 999999