By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,965 Members | 1,687 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,965 IT Pros & Developers. It's quick & easy.

Splitting data and inserting into new rows (or table)

P: 1
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
Feb 13 '08 #1
Share this Question
Share on Google+
1 Reply


dlite922
Expert 100+
P: 1,584
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!
Feb 14 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.