huge table update

kidgeek_dfw

I have a 70,000 row table of zipcodes, lats, longs, etc...

I've added new fields to include CBSA data...

Now I'm trying to update my zipcode table with the new cbsa data.

So I'm doing a Select on the CBSA table, grabing each row of CBSA data by Zipcode
And looping though the current zipcode table to add data to the new fields...

Here's the problem...

I get about 1000 rows out and then the update says done... but there is still another 69,000 rows to go...

I'm not sure what is wrong, if it is my php.ini, httpd.conf, or my.cnf...
I've tried I think everything...

Any Ideas?

Weedpacket

Have you checked or adjusted PHP's timeout setting?

kidgeek_dfw

I copied both tables onto another server and then ran the following...
This is half way done and has been running now for 3 hours...

$sql = "SELECT * FROM cbsa";
$result = @($sql,$connection) or die("Couldn't execute query.");
while ($row = mysql_fetch_array($result)) {
$ZIPCode = $row['ZIPCode'];
$CSACode = $row['CSACode'];
$CSAName = $row['CSAName'];
$CBSACode = $row['CBSACode'];
$CBSAName = $row['CBSAName'];
$CBSAType = $row['CBSAType'];
$DivCode = $row['DivCode'];
$DivName = $row['DivName'];

$sql2 = "UPDATE zipcodes2
SET
CSACode = \"$CSACode\",
CSAName = \"$CSAName\",
CBSACode = \"$CBSACode\",
CBSAName = \"$CBSAName\",
CBSAType = \"$CBSAType\",
DivCode = \"$DivCode\",
DivName = \"$DivName\"
WHERE ZIPCode = '$ZIPCode'
";

$result2 = @($sql2,$connection) or die("Couldn't update data.");

echo $ZIPCode.'<br>';

}