conversion

[weazy]

I have a database that has two tables linked by a text field called 'name'. The first table is just a profile table that has a bunch of standard data for all our users with the name field being their username. The second table is like an activity table that tracks things the users are doing by the persons username. Table 1 has one entry per user, whereas the second could have a hundered for some users, none for other users, and anything in between for the others.

I want to change the data structure so that the two tables are connected by a auto_number primary key. Obviously in table 1 i just add the field and set it as the primary key autonumber. What is the best approach to substitute the name field in table 2 with the corresponding ID assigned automatically in table 1 for all these records that already exist?

Thanks.

[devinemke]

add an auto numbering INT PK column to both table 1 and table 2, then do:

// connect to db here

$result = mysql_query('SELECT id, name FROM table1') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result))
{
	mysql_query("UPDATE table2 SET id = '" . $row['id'] . "' WHERE name = '" . $row['name'] . "'") or exit(mysql_error());
	echo 'UPDATE ' . $row['id'] . ' OK<br>';
}

after you have verified the relational intergretity of the two tables you can (and should) delete the "name" column from table2 (it is now redundant data).

[weazy]

i will give it a try. thanks for the tip.

[weazy]

the script gets hung up if there is a name entry with a ' in it
is there a way to modify?

[devinemke]

[man]addslashes[/man]