Moving MySQL Data: 1 column to another & to different table?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Moving MySQL Data: 1 column to another & to different table?

Post by JAB Creations »

There are a couple of situations where I need to move data around in my MySQL database.

1.) A user authenticates their ip_2 data needs to be moved to ip_1. So ip_0 = registration IP, ip_1 = their previous session's IP, and ip_2 = their most current session's IP.

2.) I'd like to have newly registered members who have not yet activated their accounts their data saved to a "temporary" table. The table itself is not temporary, it's just where I want their information stored until they have activated their account. Once they activate their account I want to move the entire row from the temporary table to the permanent accounts table.

I have been browsing online and I keep seeing things that do not relate to what I'm doing so a little direction would be greatly appreciated!
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Moving MySQL Data: 1 column to another & to different table?

Post by Christopher »

It might be easier to add a 'active' or 'available' column that is set to no/false until the account is activated. Otherwise you just need to SELECT from the first table and INSERT into the second.
(#10850)
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Moving MySQL Data: 1 column to another & to different table?

Post by JAB Creations »

WOOHOO! I figured out how to move data in a sense from one column to another...

Code: Select all

$query1 = mysql_query("UPDATE public_accounts SET ip_1 = ip_2 WHERE username = '".mysql_real_escape_string($_POST['username'])."'");
What is MySQL's equivalent to PHP's semicolon? I'd like to execute more then one command per mysql_query.

I actually am interested in minimizing space my database will use. Not all accounts initially registered will be activated and while I could eventually drop those rows from the table I'd rather not have them mixed in with users who are active on my site. It's just part of how I would prefer to do things and the big kicker is that I'd learn how to move data between tables.

I'm going to try your second suggestion though I think this will take a little more time to attempt. Thanks!
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Moving MySQL Data: 1 column to another & to different table?

Post by califdon »

JAB Creations wrote:WOOHOO! I figured out how to move data in a sense from one column to another...

Code: Select all

$query1 = mysql_query("UPDATE public_accounts SET ip_1 = ip_2 WHERE username = '".mysql_real_escape_string($_POST['username'])."'");
What is MySQL's equivalent to PHP's semicolon? I'd like to execute more then one command per mysql_query.
There is none. SQL can be complex, but you can't execute more than one query at a time.
I actually am interested in minimizing space my database will use. Not all accounts initially registered will be activated and while I could eventually drop those rows from the table I'd rather not have them mixed in with users who are active on my site. It's just part of how I would prefer to do things and the big kicker is that I'd learn how to move data between tables.
I strongly advise you to slow down and learn some fundamentals about relational databases. If you go off on a tangent, you will be reinforcing bad practices and it will adversely impact your learning. Perhaps one of the worst things you could do is move data from table to table. What arborint suggested is the correct way to handle it. Relational database design is based on establishing "entities", like Persons, Things, Places, Events, Transactions. Once you have established the entities for your database, each one becomes a table. Then all the data that describes those entities will be stored in those tables. You don't just decide to split a table for this reason or that reason. If you violate the concept of an entity, you will just be asking for a lot of trouble, further down the project design.
Post Reply