CodeIgniter Forums
Mysql DB move 1 column (w/ data) from one table to another seamlessly? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Mysql DB move 1 column (w/ data) from one table to another seamlessly? (/thread-11647.html)

Pages: 1 2


Mysql DB move 1 column (w/ data) from one table to another seamlessly? - El Forum - 09-17-2008

[eluser]Devon Lambert[/eluser]
Hello all CIers,

I have two separate member tables in my dev database. One table contains a large grouping of members from the live site I am working on. The other table will be the NEW and improved table made specifically to work with a CI authentication library. What I am trying to do is preserve some of the columns in the current member table and import those tables (data and all) into the NEW member table.

The database uses mysql and I am looking for an easy way to accomplish the above task.

Nice would be: Some sql query, or group of queries, to perform the transfer of a column from one table to the other seamslessly.

Best Case: Some automatic script that would do the above and also make it so that I could grab member data from the live site as it is updated. This may very well be a dream but I thought it couldn't hurt to ask around. :-)

Thank you for any help you can provide.


Mysql DB move 1 column (w/ data) from one table to another seamlessly? - El Forum - 09-17-2008

[eluser]Sumon[/eluser]
I have an idea that, you need a corn setup to transfer data between table seamslessly.


Mysql DB move 1 column (w/ data) from one table to another seamlessly? - El Forum - 09-17-2008

[eluser]yelirekim[/eluser]
I think probably most people are having a hard time understanding exactly what you're asking, but I'll see if I can't clarify and help a little:

You have two tables, both of them store information for user profiles (the word member is a little ambiguous) on your site, like username, password etc. One of the tables is currently being used live on the site, but doesn't have the functionality you want in it, so you also have another table which you're hoping to move all of the current user data to which includes extra fields and extra functionality.

The easiest way to do this would just be to simply run a for loop which moves all of the data from one table to the other, and maps the columns, like this:

Code:
$query = $this->db->get('live_users_table');

foreach($query->result() as $user)
{
    $new_user_record = array('username' => $user->username, 'password' => $user->password, 'some_other_attribute' => $user->other_attribute);
    $this->db->insert('new_users_table',$new_user_record);
}

But if you have an enormous users table, that might take a while or time out, and you could run into a messy situation with that.

If I were doing this I'd just handle all of it from the MySQL prompt by doing the following things:

1. creating a copy of the old users table:

Code:
CREATE TABLE new_users_table SELECT * FROM live_users_table;

2. Removing any columns from the table that you don't want anymore

Code:
ALTER TABLE new_users_table DROP COLUMN unneeded_user_field;

3. Adding any columns to the table which your authentication library will expect

Code:
ALTER TABLE new_users_table ADD COLUMN user_permissions INT UNSIGNED NOT NULL DEFAULT 0;

4. Use that new table to test your features out and debug, then when you're ready to switch the live site over to using the new table, take the site down, do your application updates, and rename the tables accordingly:

Code:
RENAME TABLE live_users_table TO archive_users_table;
RENAME TABLE new_users_table TO live_users_table;

Hope this helps!


Mysql DB move 1 column (w/ data) from one table to another seamlessly? - El Forum - 09-17-2008

[eluser]Devon Lambert[/eluser]
[quote author="yelirekim" date="1221714240"]
Hope this helps![/quote]

That last suggestion definitely helps. I don't know why I couldn't wrap my head around the idea of moving the columns/data from the new table to another table.

That's what forums are all about I guess...Brainstorming :-)


Mysql DB move 1 column (w/ data) from one table to another seamlessly? - El Forum - 09-17-2008

[eluser]Sumon[/eluser]
Quote:I think probably most people are having a hard time understanding exactly what you’re asking
thanks yelirekim. you got the point exactly Wink

[quote author="dnyce" date="1221703203"]Hello all CIers,
Nice would be: Some sql query, or group of queries, to perform the transfer of a column from one table to the other seamslessly.
[/quote]
this is already covered by yelirekim.

Quote:Best Case: Some automatic script that would do the above and also make it so that I could grab member data from the live site as it is updated. This may very well be a dream but I thought it couldn't hurt to ask around. :-)
For the best case, my suggestion was
Code:
I have an idea that, you need a corn setup to transfer data between table seamslessly.
Let me clarify further. There are two ways how you can grab and update data from live site. First when someone register in live site run an insert script for your table.
Code:
$new_user_record = array('username' => $user->username, 'password' => $user->password, 'some_other_attribute' => $user->other_attribute);
    $this->db->insert('new_users_table',$new_user_record);
Secondly you can set a corn script in server which run every day/hour will synchronize data between live table and new_users_table table.
Hope it help you Wink


Mysql DB move 1 column (w/ data) from one table to another seamlessly? - El Forum - 09-17-2008

[eluser]jbads[/eluser]
Corn or CRON?


Mysql DB move 1 column (w/ data) from one table to another seamlessly? - El Forum - 09-18-2008

[eluser]Crafter[/eluser]
Code:
CREATE TEMPORARY TABLE temp
        (select t2.field2, t2.field3
         from table2 t2
         where t2.field3="somevalue");

      update table1 t1, temp t
      set t1.field3=t.field3
      where t1.field2=t.field2;



Mysql DB move 1 column (w/ data) from one table to another seamlessly? - El Forum - 09-18-2008

[eluser]Sumon[/eluser]
[quote author="jbads" date="1221735282"]Corn or CRON?[/quote]

CRON. Tongue
thanks jbads


Mysql DB move 1 column (w/ data) from one table to another seamlessly? - El Forum - 09-18-2008

[eluser]drewbee[/eluser]
Not sure if you have what you need, but just so you konw you can do a subquery from an insert as well. IE

INSERT INTO new_table (col1, col2, col3) VALUES (SELECT col1, col2, col3 FROM old_table)


PS. Corn jobs are the best! Big Grin


Mysql DB move 1 column (w/ data) from one table to another seamlessly? - El Forum - 09-18-2008

[eluser]Devon Lambert[/eluser]
[quote author="drewbee" date="1221764534"]Not sure if you have what you need, but just so you konw you can do a subquery from an insert as well. IE

INSERT INTO new_table (col1, col2, col3) VALUES (SELECT col1, col2, col3 FROM old_table)


PS. Corn jobs are the best! Big Grin[/quote]

Haha, yes gotta love those Corn jobs. :-)

Thanks to everyone for this advice.

Definitely what I was looking for. I believe your way may work best Drewbee as it is the most straightforward. I'll give it a go later today and let you all know how it went.

Thanks again.