时间戳

首页 > 解决方案 > 要更改什么，以便一次完成多个插入并且更快

php - 要更改什么，以便一次完成多个插入并且更快

问题描述

问题是我想一次查询将购物车的所有内容一次插入到数据库表中ordersdetail以提高速度。

在我下面的代码中，插入现在使用准备好的语句处于 for 循环中。下面的脚本正在运行，但现在每次迭代都会进行一次插入。

我认为如果我把整个购物车放在一个数组中会更快，但我卡住了。我无法解决问题。任何帮助表示赞赏。

剧本

include_once '../../includes/db_connect.php'; 

class Item{
    var $productid;
    var $productnaam;
    var $productomschrijving;
    var $productprijs_excl;
    var $productprijs_incl;
    var $product_btw_tarief;
    var $quantity;
    var $lesuren;
}

session_start();

$order_id = $_SESSION["order_id"];
$klantid = $_SESSION["klantid"];

$insert_stmt = $mysqli->prepare('INSERT INTO ordersdetail (order_id, productid, productnaam, productomschrijving, productprijs_incl, product_btw_tarief, aantal, subtotaalexcl, subtotaal, klantid, lesuren) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');
$insert_stmt->bind_param('iissddiddid', $order_id, $productid, $productnaam, $productomschrijving, $productprijs_incl, $product_btw_tarief, $aantal, $subtotaalexcl, $subtotaal, $klantid, $lesuren);                   

$cart = $_SESSION ['cart'];

    for($i = 0; $i < count($cart); $i++){

        $productid = $cart[$i]->productid;
        $productnaam = $cart[$i]->productnaam;
        $productomschrijving = $cart[$i]->productomschrijving;
        $productprijs_incl = $cart[$i]->productprijs_incl;
        $product_btw_tarief = $cart[$i]->product_btw_tarief;
        $subtotaalexcl = $cart[$i]->productprijs_excl * $cart[$i]->quantity;
        $subtotaal = $cart[$i]->productprijs_incl * $cart[$i]->quantity;
        $aantal = $cart[$i]->quantity;
        $lesuren = $cart [$i]->lesuren * $cart[$i]->quantity;

        $insert_stmt->execute();
    }// End for loop
 $insert_stmt->close();

编辑

$cart = unserialize (serialize ($_SESSION ['cart']));

编辑于：

$cart = $_SESSION ['cart'];

@缺口。bind 语句在prepare 语句之后立即移出循环。

标签： phpmysqli

解决方案

经过快速检查，似乎不太容易让准备好的语句转义查询，因此另一种方法是在没有准备好的语句的情况下转义数据，这是一个快速实现：

$order_id = $_SESSION["order_id"];
$klantid = $_SESSION["klantid"];


$cart = unserialize (serialize ($_SESSION ['cart']));
$insert_values = '';
for($i = 0; $i < count($cart); $i++){

    $productid = $cart[$i]->productid;
    $productnaam = $cart[$i]->productnaam;
    $productomschrijving = $cart[$i]->productomschrijving;
    $productprijs_incl = $cart[$i]->productprijs_incl;
    $product_btw_tarief = $cart[$i]->product_btw_tarief;
    $subtotaalexcl = $cart[$i]->productprijs_excl * $cart[$i]->quantity;
    $subtotaal = $cart[$i]->productprijs_incl * $cart[$i]->quantity;
    $aantal = $cart[$i]->quantity;
    $lesuren = $cart [$i]->lesuren * $cart[$i]->quantity;

    //You can escape them during assigning, I did it here to easily see the data types
    //i
    $order_id = (int)$order_id;
    //i
    $productid = (int)$productid;
    //s
    $productnaam = mysqli_real_escape_string($mysqli, $productnaam);
    //s
    $productomschrijving = mysqli_real_escape_string($mysqli, $productomschrijving);
    //d
    $productprijs_incl = (double) $productprijs_incl;
    //d
    $product_btw_tarief = (double) $product_btw_tarief;
    //i
    $aantal = (int)$aantal;
    //d
    $subtotaalexcl = (double)$subtotaalexcl;
    //d
    $subtotaal = (double)$subtotaal;
    //i
    $klantid = (int) $klantid;
    //d
    $lesuren = (double) $lesuren;

    $insert_values .= "($order_id, $productid, $productnaam, $productomschrijving, $productprijs_incl, $product_btw_tarief, $aantal, $subtotaalexcl, $subtotaal, $klantid, $lesuren),";

}// End for loop

//trim trailing ,
rtrim($insert_values, ",");

$sql = "INSERT INTO ordersdetail (order_id, productid, productnaam, productomschrijving, productprijs_incl, product_btw_tarief, aantal, subtotaalexcl, subtotaal, klantid, lesuren) VALUES " . $insert_values;

$mysqli->query($sql);

警告您应该添加有关要插入的数据的更多验证

推荐阅读