Splitting data and inserting into new rows (or table)

[Nevgar]

I am fairly new to php / MySQL and have a problem with a query and an insert.
I have a database that maybe isn't the best design - it includes a field "Former Names" which is varchar 255.
It contains a list of names separated by commas. e.g.: "Svea Jarl, Apollo III "
There may be up to half a dozen or so different names in the one field.
(I know, I know - bad design - that's why I want to change it)
I would like to
a) strip off the quote marks;
b) Insert the whole row into a new row;
c) Assign the id number (auto increment field called idCruise);
d) Have the first 'former name' become the 'Name' field;
e) Have the original 'Name' field either go into former names or into an "also known as" field (which exists);
f) Then repeat b through e for each former name;

Is my best bet to create a new table, indexing on idCruise and containing one field for each former name and then running a select / insert query based on the contents of that table?
?maybe renaming the Name field to "Current Name"?

How do I avoid an endless loop where the new rows don't get checked then the rows that are inserted from that check get checked ..... ad nauseum?

Thanks for any help anyone can offer.

Nevgar

[dlite922]

I am fairly new to php / MySQL and have a problem with a query and an insert.
I have a database that maybe isn't the best design - it includes a field "Former Names" which is varchar 255.
It contains a list of names separated by commas. e.g.: "Svea Jarl, Apollo III "
There may be up to half a dozen or so different names in the one field.
(I know, I know - bad design - that's why I want to change it)
I would like to
a) strip off the quote marks;
b) Insert the whole row into a new row;
c) Assign the id number (auto increment field called idCruise);
d) Have the first 'former name' become the 'Name' field;
e) Have the original 'Name' field either go into former names or into an "also known as" field (which exists);
f) Then repeat b through e for each former name;

Is my best bet to create a new table, indexing on idCruise and containing one field for each former name and then running a select / insert query based on the contents of that table?
?maybe renaming the Name field to "Current Name"?

How do I avoid an endless loop where the new rows don't get checked then the rows that are inserted from that check get checked ..... ad nauseum?

Thanks for any help anyone can offer.

Nevgar

now i'm SERIOUSLY dizzy from reading that. people should read their own posts before hitting submit.

Okay, Guy

Why don't you tell me what exactly your needs are for the database. Don't explain to me how you should do it. Just what you need stored.

For example you could say I have a list of persons that want to go on vacation, each person has multiple "former" names or nicknames.

Plain.... old....english.

Don't get technical on me, that's my job.

Now try again and i'll try to help.

oh and uh... welcome to TSDN!