Copying records

General discussions related to php

Moderators: macek, egami, gesf

User avatar
frogrocker
New php-forum User
New php-forum User
Posts: 98
Joined: Mon Jul 07, 2003 10:30 pm
Location: Manchester
Contact:

Copying records

Postby frogrocker » Tue Jul 08, 2003 8:02 am

Is there a way of copying or moving certain rows in a table ina database, to another table in the same database(with the same structure).

I want to select certain rows, depending on a session variable, and have these copied or moved to another table.

TIA

User avatar
swirlee
Moderator
Moderator
Posts: 2272
Joined: Sat Jul 05, 2003 1:18 pm
Location: A bunk in the back
Contact:

Postby swirlee » Tue Jul 08, 2003 11:00 am

frogrocker, first off I'd suggest you post questions like this to the MySQL & PHP Coding forum instead of the general forum. You'll probably get your question answered faster.

Anyway, the page in the manual you're looking for is 6.4.3.1 - INSERT ... SELECT Syntax. Essentially, all you do is tack a SELECT statement on the end of your INSERT statement. So if your tables have the exact same structure and you want to copy a row or several from table1 into table2, it'd look like this:

INSERT INTO table2 SELECT * FROM table1 WHERE ...

Of course, specify the usual WHERE statement at the end to match the rows you want to copy. Then, if you want to delete the row(s) from table1, use a plain old DELETE with the same WHERE clause.

That should do it. I'd be curious to know why you're moving rows between two identical tables, but that's your business.

User avatar
frogrocker
New php-forum User
New php-forum User
Posts: 98
Joined: Mon Jul 07, 2003 10:30 pm
Location: Manchester
Contact:

Postby frogrocker » Tue Jul 08, 2003 1:48 pm

thanks for the great responses. I forgot to look at the mysql documentation.....

the method behind the madness is that one table will be a temporary table. whilst the other will be used when the records have been confirmed. Theres probably an easier way, but im new.

User avatar
swirlee
Moderator
Moderator
Posts: 2272
Joined: Sat Jul 05, 2003 1:18 pm
Location: A bunk in the back
Contact:

Postby swirlee » Tue Jul 08, 2003 2:41 pm

Indeed, frogrocker, there is a far easier way. Just add a column called "confirmed" or something and give it the type ENUM('yes','no'). I'd recommend setting it to NOT NULL and make the default value 'no':

ALTER TABLE table1 ADD COLUMN confirmed ENUM('yes','no') NOT NULL DEFAULT 'no';

Then when the record is confirmed, just change confirmed to 'yes';

User avatar
frogrocker
New php-forum User
New php-forum User
Posts: 98
Joined: Mon Jul 07, 2003 10:30 pm
Location: Manchester
Contact:

Postby frogrocker » Tue Jul 08, 2003 11:10 pm

Yes, I did think about that, but I think the copying method will be more robust for what I am designing...

I may try it anyway


Return to “PHP General”

Who is online

Users browsing this forum: No registered users and 1 guest

cron