dpr77335 2013-10-27 15:42
浏览 273
已采纳

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

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?

  • 写回答

3条回答 默认 最新

  • dongsuyou6938 2013-10-27 15:54
    关注

    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

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
  • dourao1877 2013-10-27 15:55
    关注

    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;
    
    评论
  • drn61317 2018-05-21 01:16
    关注

    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;
                }
            }
            ?>
    
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥20 Next.JS 静态导出路由
  • ¥15 我做一个对中文文本情感分析的项目 我用了CNN,和keras框架 报的错误我一直处理不好
  • ¥15 unity使用bakery打光烘焙所遇到的问题。
  • ¥99 二维有限元方法求解,泊松方程
  • ¥15 我需要在PC端 开两个抖店工作台客户端.(语言-java)
  • ¥15 有没有哪位厉害的人可以用C#可视化呀
  • ¥15 可以帮我看看代码哪里错了吗
  • ¥15 设计一个成绩管理系统
  • ¥15 PCL注册的选点等函数如何取消注册
  • ¥15 问一下各位,为什么我用蓝牙直接发送模拟输入的数据,接收端显示乱码呢,米思齐软件上usb串口显示正常的字符串呢?