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

时间:2022-09-15 22:19:38

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)

当用户单击名为delete的按钮时,我试图将数据从一个数据库(注册)移动到另一个数据库。(我想将数据移动到一个名为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?

这个不动它……谁能帮忙吗?

3 个解决方案

#1


4  

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

这是SQLFiddle演示

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

其次,INSERT不返回resultset,所以不应该使用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

这是SQLFiddle演示

#2


0  

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;

#3


0  

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;
            }
        }
        ?>

#1


4  

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

这是SQLFiddle演示

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

其次,INSERT不返回resultset,所以不应该使用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

这是SQLFiddle演示

#2


0  

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;

#3


0  

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;
            }
        }
        ?>