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!
Moving MySQL Data: 1 column to another & to different table?
Moderator: General Moderators
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
- 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?
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)
- 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?
WOOHOO! I figured out how to move data in a sense from one column to another...
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!
Code: Select all
$query1 = mysql_query("UPDATE public_accounts SET ip_1 = ip_2 WHERE username = '".mysql_real_escape_string($_POST['username'])."'");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!
Re: Moving MySQL Data: 1 column to another & to different table?
There is none. SQL can be complex, but you can't execute more than one query at a time.JAB Creations wrote:WOOHOO! I figured out how to move data in a sense from one column to another...What is MySQL's equivalent to PHP's semicolon? I'd like to execute more then one command per mysql_query.Code: Select all
$query1 = mysql_query("UPDATE public_accounts SET ip_1 = ip_2 WHERE username = '".mysql_real_escape_string($_POST['username'])."'");
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.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.