943,917 Members | Top Members by Rank

Ad:
  • MySQL Discussion Thread
  • Unsolved
  • Views: 3624
  • MySQL RSS
Feb 7th, 2008
0

Inserting data from one row and change one field

Expand Post »
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
Similar Threads
Reputation Points: 10
Solved Threads: 0
Newbie Poster
Nevgar is offline Offline
2 posts
since Feb 2008
Feb 10th, 2008
0

Re: Inserting data from one row and change one field

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.
Reputation Points: 18
Solved Threads: 20
Junior Poster
trudge is offline Offline
176 posts
since Sep 2007
Feb 10th, 2008
0

Re: Inserting data from one row and change one field

Click to Expand / Collapse  Quote originally posted by trudge ...
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
Reputation Points: 10
Solved Threads: 0
Newbie Poster
Nevgar is offline Offline
2 posts
since Feb 2008
Feb 21st, 2008
0

Re: Inserting data from one row and change one field

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
Last edited by hooray; Feb 21st, 2008 at 2:22 pm. Reason: text colour screwed things up
Reputation Points: 11
Solved Threads: 6
Junior Poster in Training
hooray is offline Offline
62 posts
since Jan 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in MySQL Forum Timeline: Foreign Keys
Next Thread in MySQL Forum Timeline: I Need Help Installing Vbulletin





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC