[RESOLVED] updating fields in table

[ali_p]

I have 2 tables that i am working with, one called locations which cointains
id
location_code
location
and the other is candidates, which amonst other things contains
location_code1
location_code2
location_code3
The purpose of this is that candidates state where they are willing to work (in these 3 fields ).
Now the three canidate fields contain the location code, which could be IE for Republic of Ireland NI for Northern Ireland UK for England etc.

What i am trying to do is to put the full location name into the candidates table.
This is what i'm currently working with.

$sql="SELECT *
	  FROM location, candidates
	  WHERE candidates.location_code1 = location.location_code";
$result = mysql_query($sql, $conn)or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
	//give a name to the fields
	$location_code=$row['location_code'];
	$location1=$row['location'];
}

$sqla="UPDATE candidates
	   SET location_code1 = '$location1'
	   WHERE location_code1= '$location_code'";
$result = mysql_query($sqla, $conn)or die(mysql_error());

I will need to repeat the process for the other 2 fields but i just want to get the first one right and working.
Can anyone shed anylight my way please.
🙂

[devinemke]

any particular reason that you want to store the full location name rather than just the code? seems like this simply adds a lot of extra bloat to your data and defeats one a the main purposes of a relational data structure. in fact you should not even be using the code, you should use the location_id.

anyway, here's the basic idea:

$result = mysql_query('SELECT * FROM locations') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result)) {$locations[$row['location_code']] = $row['location'];}

$result = mysql_query('SELECT * FROM candidates') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
	$sql = "UPDATE candidates SET 
	location_code1 = '" . $locations[$row['location_code1']] . "',
	location_code2 = '" . $locations[$row['location_code2']] . "',
	location_code3 = '" . $locations[$row['location_code3']] . "' 
	WHERE candidate_id = '" . $row['candidate_id'] . "'";

mysql_query($sql) or exit(mysql_error());	
}

[ali_p]

Thats smashing thank you!
I understand about the extra code required, its just that the tables are coming from another source, ( not created by me ) and i just have to work with it.

:o