extrovertive Posted August 20, 2006 Share Posted August 20, 2006 I have created a new column in my MySQL database.Example:mytable (firstname, lastname, middlename)then I added the columns firstnamea and firstnameb to my table.mytable (firstname, lastname, middlename, firstnamea, firstnameb)Now, my table has like many hundred rows. It's a pain to go into phpmyadmin and copy each old firstname and lastname column values to the new ones.What's a PHP and MySQL way to copy old column values into new ones? Quote Link to comment Share on other sites More sharing options...
tomfmason Posted August 20, 2006 Share Posted August 20, 2006 Try this.I wrote this on the fly so you may want to check it for syntax errors. It should give you a basic idea.[code]<?php$sql = mysql_query("SELECT * FROM `yourtable`") or die(mysql_error());if (!$sql) { echo "Something went wrong";}else{ $i = 0; while ($rw = mysql_fetch_assoc($sql)) { $firstname = $rw['firstname']; $lastname = $rw['lastname']; $somethingelse = $rw['somethingelse']; $q = mysql_query("INSERT INTO `yourtable`(`firstnameb`, `lastnameb`, `somethingelseb`) VALUES ('$firstname', '$lastname', '$somethingelse')") or die(mysql_error()); /*this is a counter (provided by AndyB. This will tell the script to rest after each set of twenty enteries*/ $i++; if ($i == 20) { sleep(1); $i = 0; } } echo "The new table was updated sucessfuly";}?>[/code]Hope this helps,Tom Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted August 20, 2006 Share Posted August 20, 2006 Out of interest, why does it sleep for 1 second after 20 entries? What purpose does that serve? Quote Link to comment Share on other sites More sharing options...
tomfmason Posted August 20, 2006 Share Posted August 20, 2006 [quote author=extrovertive link=topic=104920.msg418789#msg418789 date=1156052980]Now, my table has like many hundred rows. [/quote]He can change the number of entries. I may have been wrong but I thought that it would be good to give the script a rest. You know instead of just update hundreds of rows all at once. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 20, 2006 Share Posted August 20, 2006 [code]<?php$sql = "UPDATE mytable SET firstnamea = firstname, firstnameb = lastname";mysql_query($sql);?>[/code] Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.