I 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 "
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

Whenever you have a field with multiple values, it is a good indicator to create a new table for that data. In this case, use the ID field to tie former names to each other. Split the query result on the comma, strip off leading/trailing spaces, and insert what's left into a new table.

You can strip the quote marks using your programming language or SQL. But beware of how you do the INSERT - you should probably be using quoted text to correctly capture things like apostrophes in names (O'Reilly). Check your programming language to see what is available.

Depending on your version of MySQL yes, you can do an INSERT ... (SELECT...)) query to accomplish that part of your problem.

Thanks for the advice. I am very new to php/MySql, so I am not sure how to "split the query result on the comma" or how to insert what's left into a new table.

If you (or anyone else for that matter) can give me some sample code, it would be a great help!

Nevgar

I can help with the splitting the code after each comma, I had a similar problem to this recently and came up with this solution (I couldnt put it in code tags because then it wouldnt highlight the bits that need changing):

//you will need code up here to get the former names into a variable called $former
$query=mysql_query("SELECT former_name, user_ID FROM name_table");
while($row=mysql_fetch_array($query))
{
$former=$row['former_name'];
$id=$row['user_ID'];

//this removes the quotes
$former = str_replace('"', "", $former);

//this creates and array called $keys and each value is
//one of the former names that is split by the commas
$former = preg_split('/,/' , $former, -1 );

//this is an array loop that will put the separate values into a new table
foreach($former as $value)
{
//put some code here to enter values into database eg:
$query=mysql_query("INSERT INTO new_table (`user_ID`, `name`) VALUES ('$id', '$value') ");
}

}

I have highlighted the code that you might need to change to suit your database.

I hope this helps

Hooray