Update address info in database with zipcode database

Table to table comparison

[Drunk N Japan]

I have been working on this query today and I am not sure if I have hosed it beyond working. I have 2 tables 1 with member address and the other with a zip code data base. I want to select the rows with incorrect zip code to city/state, and correct them with the data from the zip code database. After reading some of the other postings this is what I have come up with.

dbx_connect();
$sql = 'SELECT * FROM `members-2007`';
$result = mysql_query($sql)
or die ("Couldn't execute query.");
echo mysql_num_rows($result),"<br/";

//put results into an array
while ($row= mysql_fetch_array($result)){
//find errors\

//temporary variables for easier use
$tempzip = $row['Zip_Code'];
echo $tempzip,"<br/>";
$tempstate = $row['State'];
echo $tempstate,"<br/>";
$tempcity = $row['City'];
echo $tempcity,"<br/>";
zip_connect();

//check the zipcode database for errors
$query = "SELECT * FROM zipcode WHERE zip = '$tempzip' AND State = '$tempstate' AND City = '$tempcity' ";
$check = mysql_query($query);
$state = mysql_num_rows($check);
echo $state,"<br/>";
if (!$state)
// if there is no answer then Update the Members
// database from the zipcode database
{
$change = "UPDATE `members-2007` SET
State = (SELECT State FROM `zipcode` WHERE zip = '$tempzip'),
City = (SELECT City from `zipcode` WHERE zip = '$tempzip'),
WHERE Zip_Code = '$tempzip'";
$zipped = mysql_query($change) or die ("couldn't update database");
echo $row['First_Name']," ",$row['Last_Name']," ",$row['Address']," ",$row['City']," ",$row['State']," ",$row['Zip_Code']," Invalid City/State ","<br/>";
}
}//end row while

Thanks for all the help

[Drunk N Japan]

City = (SELECT City from `zipcode` WHERE zip = '$tempzip'),

You should remove the comma at the end and it will work fine.

I tried it and it worked.

Good job, nice looking code.

Sometimes it is the small things like :;, that get you.

Have a great one pal and remember it's five o'clock somewhere.

[eelixduppy]

hehe...glad you got everything resolved, Drunk_N_Japan :)

[Drunk N Japan]

Sometimes an issue may seem huge and like you cannot figure it out, but if you step outside, have a refreshment, comeback and take a look at what you are doing, you can often figure out what you are doing. I can say that the forums did help because the code I was using for the update part I did find in a forum here.

{
$change = "UPDATE `members-2007` SET
State = (SELECT State FROM `zipcode` WHERE zip = '$tempzip'),
City = (SELECT City from `zipcode` WHERE zip = '$tempzip'),
WHERE Zip_Code = '$tempzip'";
$zipped = mysql_query($change) or die ("couldn't update database");
echo $row['First_Name']," ",$row['Last_Name']," ",$row['Address']," ",$row['City']," ",$row['State']," ",$row['Zip_Code']," Invalid City/State ","<br/>";
}
}//end row while

It was in a more subtle and easier to understand form I just added a comma where one didn't need to be. It was kinda nice to answer my own question though.

I do appreciate everything you guys do because sometimes I swear I couldn't survive without you. Everyday I build my own knowledge base and I hope to be able to dispense the sage advice that y'all do one day in the future.

Keep up the good work,

Drunk_N_Japan (Sober_N_America)