Copying an old mysql column to a new column?

[extrovertive]

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?

[tomfmason]

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   

[GingerRobot]

Out of interest, why does it sleep for 1 second after 20 entries? What purpose does that serve?

[tomfmason]

[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.

[Barand]

[code]<?php
$sql = "UPDATE mytable SET
        firstnamea = firstname,
        firstnameb = lastname";

mysql_query($sql);
?>[/code]