dongxi7722 2015-08-11 12:22 采纳率: 0%
浏览 69
已采纳

使用PDO检查数据库插入和回滚的正确方法

I have here a code to insert the order of the customer in the orders table and insert the purchased products in that order in the purchased_products table. I want to check if the insertions were made, otherwise undo the changes with PDO rollback(). My code is:

$options = [
PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false
];    

try
{
    $connection = new PDO("mysql:host={$HOST};dbname={$DB_NAME}", $USERNAME, $PASS, $options);
}

$connection->beginTransaction();

try
{
    $sql = "INSERT INTO orders (customer_id, customer_name, order_value, order_date)
            VALUES (?, ?, ?, ?)";

    $query = $connection->prepare($sql);
    $query->execute(array
    (
        $user_id,
        $user['user_name'],
        $order_value,
        $date
    ));

    $id_of_respective_order = $connection->lastInsertId();
}
catch(PDOException $exception) 
{
    $connection->rollback();
    echo "<script>alert('An error occurred while completing your purchase. Please try again later.');</script>";
}

try
{
    $sql = "INSERT INTO purchased_products (order_id, product_name, product_price, quantity)
            VALUES (?, ?, ?, ?)";

    $query = $connection->prepare($sql);

    foreach($_SESSION['cart'] as $product)
    {
        $query->execute(array
        (
            $id_of_respective_order,
            $product['product_name'],
            $product['product_price'],
            $product['quantity']
        ));
    }
}
catch(PDOException $exception) 
{
    $connection->rollback();
    echo "<script>alert('An error occurred while completing your purchase. Please try again later.');</script>";
}

$connection->commit();

Is this way safe? I use a transaction to lock the tables and lastInsertId () to assign the ID of the order to the products that belongs to it. I check the insertions and if something went wrong undo the changes with rollback(). Is my checkout system well prepared and totally safe?

展开全部

  • 写回答

1条回答 默认 最新

  • doulu3808 2015-08-11 15:32
    关注

    It makes more sence to do all your inserts inside the same Try/Catch and then if the order insert or the order_item insert fails a single catch block will deal with the rollback and any cleanup/reporting that may be required.

    The way you had it the order insert could fail and then the order_item insert would still try and run, possibly creating items without a owning order.

    try {
        $connection = new PDO("mysql:host={$HOST};dbname={$DB_NAME}", 
                              $USERNAME, $PASS);
        $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    catch (PDOException $e ) {
        echo 'Connection failed: ' . $e->getMessage();
        exit;
    }
    
    $connection->beginTransaction();
    
    try {
        $sql = "INSERT INTO orders 
               (customer_id, customer_name, order_value, order_date)
               VALUES (?, ?, ?, ?)";
    
        $query = $connection->prepare($sql);
        $query->execute(array( $user_id,
                             $user['user_name'],
                             $order_value,
                             $date
                            )
                       );
    
        $id_of_respective_order = $connection->lastInsertId();
    
        $sql = "INSERT INTO purchased_products 
                       (order_id, product_name, product_price, quantity)
                VALUES (?, ?, ?, ?)";
    
        $query = $connection->prepare($sql);
    
        foreach($_SESSION['cart'] as $product) {
            $query->execute(array( $id_of_respective_order,
                                   $product['product_name'],
                                   $product['product_price'],
                                   $product['quantity']
                                  )
                           );
        }
    
        $connection->commit();
    
    }
    catch(PDOException $e) {
        $connection->rollBack();
    
        echo 'Order creation failed: ' . $e->getMessage();
        echo "<script>alert('An error occurred while completing your purchase. Please try again later.');</script>";
        exit;
    }
    

    展开全部

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

悬赏问题

  • ¥20 IEd中开关量采样信号通道设计
  • ¥45 字符串操作——数组越界问题
  • ¥15 Loss下降到0.08时不在下降调整学习率也没用
  • ¥15 QT+FFmpeg使用GPU加速解码
  • ¥15 为什么投影机用酷喵播放电影放一段时间就播放不下去了?提示发生未知故障,有什么解决办法吗?
  • ¥15 来个会搭建付费网站的有偿
  • ¥100 有能够实现人机模式的c/c++代码,有图片背景等,能够直接进行游戏
  • ¥20 校园网认证openwrt插件
  • ¥15 以AT89C51单片机芯片为核心来制作一个简易计算器,外部由4*4矩阵键盘和一个LCD1602字符型液晶显示屏构成,内部由一块AT89C51单片机构成,通过软件编程可实现简单加减乘除。
  • ¥15 求GCMS辅导数据分析
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部