将数据从一个MySQL表移动到另一个MySQL表

Question

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?

Answer 1

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

Answer 2

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;

Answer 3

We can do it just by using loop.Here is a example:

<?php
        if (isset($_POST['move'])) {

            $userid = $_POST["user_id"];
            $sql = " INSERT INTO `teachers_archive` SELECT * FROM `teachers` WHERE user_id='$userid'";

            if ($conn->query($sql) === TRUE) {
                $query = "DELETE FROM `teachers` WHERE user_id='$userid' ";
                if ($conn->query($query) === TRUE) {
                    $message = "Success!";
                    echo "<script type='text/javascript'>alert('$message');</script>";
                    echo"<script>document.location='mt.php';</script>";
                } else {
                    echo "Error: " . $query . "<br>" . $conn->error;
                }
            } else {
                echo "Error: " . $sql . "<br>" . $conn->error;
            }
        }
        ?>