Jump to content


Photo

Copying an old mysql column to a new column?


  • Please log in to reply
4 replies to this topic

#1 extrovertive

extrovertive
  • Members
  • PipPipPip
  • Advanced Member
  • 235 posts

Posted 20 August 2006 - 05:49 AM

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?

#2 tomfmason

tomfmason
  • Staff Alumni
  • Advanced Member
  • 1,696 posts
  • Locationstealing your wifi

Posted 20 August 2006 - 08:02 AM

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.

<?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";
}
?>

Hope this helps,
Tom   

Traveling East in search of instruction, and West to propagate the knowledge I have had gained.

current projects: pokersource

My Blog | My Pastebin | PHP Validation class | Backtrack linux


#3 GingerRobot

GingerRobot
  • Staff Alumni
  • Advanced Member
  • 4,086 posts
  • LocationUK

Posted 20 August 2006 - 08:57 AM

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

#4 tomfmason

tomfmason
  • Staff Alumni
  • Advanced Member
  • 1,696 posts
  • Locationstealing your wifi

Posted 20 August 2006 - 09:12 AM

Now, my table has like many hundred rows.


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.

Traveling East in search of instruction, and West to propagate the knowledge I have had gained.

current projects: pokersource

My Blog | My Pastebin | PHP Validation class | Backtrack linux


#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 20 August 2006 - 10:22 AM

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

mysql_query($sql);
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users