时间戳

首页 > 解决方案 > 使用 PDO 将 MySQL 表行移动到另一个表

php - 使用 PDO 将 MySQL 表行移动到另一个表

问题描述

我试图遵循这个mysql - 将行从一个表移动到另一个表，并使用 PDO 执行“移动到存档”功能，但我失败了。

所以我创建了一个工作卡系统，为了缩短它，当工作完成时，我有一个“ARCHIVE”按钮，基本上需要将选定的工作卡从表“repairs”移动到表“archived_repairs”。这两个表完全相同，只需将其从 repairs 表中删除并移动到 archived_repairs 表中，以防我们稍后需要返回它。

这是我在 CRUD 表上使用的按钮/链接：

<td><a href="archive_repair.php?archive_id=<?php echo $row['job_number']; ?>" name="btn_archive" class="btn btn-warning" onclick="return confirm('You are about to send this Job Card to the Archive database. Please only press OK if you have invoiced this client, inserted the invoice number into ECEMS and the repair has been paid for and collected by the customer and is no longer with us....');">Archive</a></td>

以上内容很好，花花公子，并使用以下 php 代码转到我名为“archive_repair.php”的页面：

<?php
require_once "connection.php";

if (isset($_REQUEST['archive_id']))
{
    try
    {
        $job_number = $_REQUEST['archive_id'];
        $select_stmt = $db->prepare('SELECT * FROM repairs WHERE job_number =:job_number');
        $select_stmt->bindParam(':job_number', $job_number);
        $select_stmt->execute();
        $row = $select_stmt->fetch(PDO::FETCH_ASSOC);
        extract($row);
    }
    catch(PDOException $e)
    {
        $e->getMessage();
    }

}

if (isset($_REQUEST['btn_archive']))
{

    $job_number = $_REQUEST['job_number'];
    $date = $_REQUEST['date'];
    $client_full_name = $_REQUEST['client_full_name'];
    $client_email = $_REQUEST['client_email'];
    $client_phone = $_REQUEST['client_phone'];
    $item_for_repair = $_REQUEST['item_for_repair'];
    $repair_description = $_REQUEST['repair_description'];
    $hardware_details = $_REQUEST['hardware_details'];
    $diagnostic_fee = $_REQUEST['diagnostic_fee'];
    $tech_assigned = $_REQUEST['tech_assigned'];
    $current_status = $_REQUEST['current_status'];
    $technician_notes = $_REQUEST['technician_notes'];
    $admin_notes = $_REQUEST['admin_notes'];
    $invoice_status = $_REQUEST['invoice_status'];
    $invoice_number = $_REQUEST['invoice_number'];

    if (empty($invoice_status))
    {
        $errorMsg = "Please change Invoice Status Before Archiving this Job Card";
    }
    else if (empty($invoice_number))
    {
        $errorMsg = "Please Enter a SAGE Invoice Reference Before Archiving this Job Card";
    }
    else
    {
        try
        {
            if (!isset($errorMsg))
            {
                $archive_stmt = $db->prepare('INSERT INTO archived_repairs job_number=:job_number, date=:date, client_full_name=:client_full_name, client_email=:client_email, client_phone=:client_phone, item_for_repair=:item_for_repair, repair_description=:repair_description, hardware_details=:hardware_details, diagnostic_fee=:diagnostic_fee, tech_assigned=:tech_assigned, current_status=:current_status, technician_notes=:technician_notes, admin_notes=:admin_notes, invoice_status=:invoice_status, invoice_number=:invoice_number');
                $archive_stmt->bindParam(':job_number', $job_number);
                $archive_stmt->bindParam(':date', $date);
                $archive_stmt->bindParam(':client_full_name', $client_full_name);
                $archive_stmt->bindParam(':client_email', $client_email);
                $archive_stmt->bindParam(':client_phone', $client_phone);
                $archive_stmt->bindParam(':item_for_repair', $item_for_repair);
                $archive_stmt->bindParam(':repair_description', $repair_description);
                $archive_stmt->bindParam(':hardware_details', $hardware_details);
                $archive_stmt->bindParam(':diagnostic_fee', $diagnostic_fee);
                $archive_stmt->bindParam(':tech_assigned', $tech_assigned);
                $archive_stmt->bindParam(':current_status', $current_status);
                $archive_stmt->bindParam(':technician_notes', $technician_notes);
                $archive_stmt->bindParam(':admin_notes', $admin_notes);
                $archive_stmt->bindParam(':invoice_status', $invoice_status);
                $archive_stmt->bindParam(':invoice_number', $invoice_number);

                if ($archive_stmt->execute())
                {
                    $delete_stmt = $db->prepare('DELETE FROM repairs WHERE job_number =:job_number');
                    $delete_stmt->bindParam(':job_number', $job_number);
                    $delete_stmt->execute();
                    header("refresh:1;repairs.php");
                }
            }
        }
        catch(PDOException $e)
        {
            echo $e->getMessage();
        }
    }
}
?>

这是我的 connection.php 文件：

<?php
$db_host="localhost"; //localhost server 
$db_user="ecemscoz_ecemsapp"; //database username
$db_password="C3m3t3ry!@"; //database password   
$db_name="ecemscoz_ecemsapp"; //database name

try
{
 $db=new PDO("mysql:host={$db_host};dbname={$db_name}",$db_user,$db_password);
 $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOEXCEPTION $e)
{
 $e->getMessage();
}

?>

当我单击 ARCHIVE 按钮/链接时，页面只是空白（白屏），没有错误显示，没有任何内容移动到另一个数据库，也没有任何内容被删除。我自 2020 年以来才开始编写 PHP，所以在这方面还是新手，但据我了解，这应该可以工作......我是否在我的代码中遗漏了一些我没有看到的东西？

标签： phpmysqlpdo

解决方案

直接在 MySQL 中执行此操作会容易得多。

像下面这样的东西基本上应该是你所需要的。

$archive_stmt = $db->prepare("INSERT INTO archived_repairs (
    job_number,
    date,
    client_full_name,
    client_email,
    client_phone,
    item_for_repair,
    repair_description,
    hardware_details,
    diagnostic_fee,
    tech_assigned,
    current_status,
    technician_notes,
    admin_notes,
    invoice_status,
    invoice_number
) (
    SELECT
        job_number,
        date,
        client_full_name,
        client_email,
        client_phone,
        item_for_repair,
        repair_description,
        hardware_details,
        diagnostic_fee,
        tech_assigned,
        current_status,
        technician_notes,
        admin_notes,
        invoice_status,
        invoice_number 
    FROM
        repairs 
    WHERE
    job_number =:job_number )");
$archive_stmt->bindParam(':job_number', $job_number);

if ($archive_stmt->execute())
{
    $delete_stmt = $db->prepare('DELETE FROM repairs WHERE job_number =:job_number');
    $delete_stmt->bindParam(':job_number', $job_number);
    $delete_stmt->execute();
    header("refresh:1;repairs.php");
}

推荐阅读