update database problem

[mackin]

Hi, I am trying to use this code to take all the unique town names from one table and populate a new table with those towns.

 

this code doesnt add town names to the new table and deletes any that are there with blank space - any clues?

 

 

mysql_select_db($database_contractors, $contractors);
$query_Recordset3 = "SELECT DISTINCT est_town FROM hotels";
$res3 = mysql_query($query_Recordset3, $contractors) or die(mysql_error());





$num_rows = mysql_num_rows($res3);
echo $num_rows . "<br>";

while( $row = mysql_fetch_assoc($res3) ) {

  $town = $row['est_town'];
  echo $town . "<br />" ;

  mysql_query('UPDATE town_coords SET town_name = '.$town.'');

}

[Psycho]

NEVER run queries in loops! Anyway, you state you are trying to populate (i.e. INSERT) records into the second table. BUt, you are using an UPDATE query. An UPDATE query will 'update' existing records. Since, you didn't provide a WHERE clause on the update query it is attempting to update ALL the records in the table. And, apparently, the value you are using is an empty string. So, all the records are being updated with an empty string.

 

Based upon what you are trying to do, however, this should be a ONE-TIME operation. Once you have a table with the unique towns you should use the primary key in that table as a foreign key in the hotels table. Then you would never store the town names in the hotels table.

 

To accomplish the import of the unique town names into the new table you should first set the 'town_name' to be a unique field. Then you need just one query to import the all the new values

INSERT INTO town_coords (town_name)
SELECT DISTINCT est_town FROM hotels

 

[spiderwell]

threads like this remind me how much I need to improve my SQL knowledge.