CODEWITHSUNDEEP
phpmysqldatabase
user2418857
user2418857

Reputation:

Move data from one MySQL table to another

I am trying to move data from one database (registrations) to another when the user clicks a button named delete. (I want to move the data to a table named archived)

Here is what i have tried (found from Google):

 $result=mysql_query("Insert Into archived (select * from registrations WHERE id=$id") ;
 $row = mysql_fetch_array($result);

This doesn't move it... can anyone help?

Upvotes: 5

Views: 17760

Answers (2)

peterm
peterm

Reputation: 92785

Firstly you're missing one parenthesis, which you don't have to use in this case at all

Change your query string to

Insert Into archived (select * from registrations WHERE id=$id)
                     ^                                        ^

or to just

Insert Into archived select * from registrations WHERE id=$id

Here is SQLFiddle demo

Secondly INSERT doesn't return a resultset so you shouldn't use mysql_fetch_array().

Thirdly if your intent was to move not just to copy data then you also need to delete the row that you copied afterwards.

Now you can put it all in a stored procedure 

DELIMITER $$
CREATE PROCEDURE move_to_archive(IN _id INT)
BEGIN
    START TRANSACTION;
    INSERT INTO archived 
    SELECT * 
      FROM registrations 
     WHERE id = _id;
    DELETE
      FROM registrations 
     WHERE id = _id;
    COMMIT;
END$$
DELIMITER ;

Sample usage:

CALL move_to_archive(2);

Here is SQLFiddle demo

Upvotes: 7

Filipe Silva
Filipe Silva

Reputation: 21657

The query you are attempting just copies the information from one table to the other. You then have to delete it from the first table:

INSERT INTO archived 
SELECT * FROM registrations WHERE id = $id;

DELETE FROM registrations WHERE id = $id;

Upvotes: 1

Related Questions