Home > Archive > PHP DB > February 2007 > Re: [PHP-DB] Trying to add primary key to existing database.
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: [PHP-DB] Trying to add primary key to existing database.
|
|
| Christopher Blöcker 2007-02-04, 6:58 pm |
| hehe, ok
i would write a script that reads the existing data from the table and
inserts it into a new one that has the same structure PLUS a primary key
something like:
original_table: name, description, whatever
new_table: id, name, description, whatever
where id is primary key and auto_increment
then
<?
if ($link=mysql_connect("host","user","pw")
{
mysql_select_db("the_correct_database);
$result=mysql_query("SELECT * FROM original_table");
while (list($name,$description,$whatever) = mysql_fetch_row($result))
{
mysql_query("INSERT INTO new_table (name,description,whatever) VALUES
($name,$description,$whatever)"); //that should add the id automatically
}
mysql_close($link)
?>
then you can drop the original_table and rename the new_table to
original_table
i don't know if there's a better, more simple solution and i hope i did
not forget anything important and it works this way
Chris
| |
|
| Christopher Blöcker wrote:
> hehe, ok
> i would write a script that reads the existing data from the table and
> inserts it into a new one that has the same structure PLUS a primary key
>
> something like:
> original_table: name, description, whatever
> new_table: id, name, description, whatever
>
> where id is primary key and auto_increment
>
> then
>
> <?
> if ($link=mysql_connect("host","user","pw")
> {
> mysql_select_db("the_correct_database);
> $result=mysql_query("SELECT * FROM original_table");
> while (list($name,$description,$whatever) = mysql_fetch_row($result))
> {
> mysql_query("INSERT INTO new_table (name,description,whatever) VALUES
> ($name,$description,$whatever)"); //that should add the id automatically
> }
> mysql_close($link)
> ?>
>
> then you can drop the original_table and rename the new_table to
> original_table
>
> i don't know if there's a better, more simple solution and i hope i did
> not forget anything important and it works this way
Good idea but you can put that all into one step and leave php out
altogether:
insert into table (f1, f2, f3) select f1,f2,f3 from other_table;
See http://dev.mysql.com/doc/refman/4.1...ert-select.html
(Nice bonus - this is sql standard so it works across a lot of different
db's).
--
Postgresql & php tutorials
http://www.designmagick.com/
|
|
|
|
|