如何使用PHP的PDO调用MySQL存储过程实现数组批量插入

原逐行插入代码 #

$connect = new PDO("mysql:host=;dbname=;charset=utf8", "username", "password");

$query = "
INSERT INTO siparis 
(product_id, p_code, p_name, p_type, p_quantity, p_listprice, p_netprice, p_total, preorderno, yetkili) 
VALUES (:pid, :p_code, :p_name, :dekorA, :p_quantity, :p_listprice, :p_netprice, :p_total, :preorderno, :yetkili)
";

for($count = 0; $count<count($_POST['hidden_p_code']); $count++)
{
    $data = array(
        ':pid'  =>  $_POST['hidden_pid'][$count],
        ':p_code'   =>  $_POST['hidden_p_code'][$count],
        ':p_name'   =>  $_POST['hidden_p_name'][$count],
        ':dekorA'   =>  $_POST['hidden_dekorA'][$count],
        ':p_quantity'   =>  $_POST['hidden_p_quantity'][$count],
        ':p_listprice'  =>  $_POST['hidden_p_listprice'][$count],
        ':p_netprice'   =>  $_POST['hidden_p_netprice'][$count],
        ':p_total'  =>  $_POST['hidden_p_total'][$count],
        ':preorderno'   =>  $_POST['hidden_preorderno'][$count],
        ':yetkili'  =>  $_POST['hidden_yetkili'][$count]
    );
    $statement = $connect->prepare($query);
    $statement->execute($data);
}

创建的MySQL存储过程 #

DELIMITER //
CREATE PROCEDURE NEWLIST(
    IN pid int(11),
    IN p_code varchar(100),
    IN p_name varchar(100), 
    IN dekorA varchar(100),
    IN p_quantity varchar(100),
    IN p_listprice varchar(100),
    IN p_netprice varchar(100),
    IN p_total varchar(100),
    IN preorderno int(11),
    IN yetkili varchar(10)
)
BEGIN
INSERT INTO siparis(product_id, p_code, p_name, p_type, p_quantity, p_listprice, p_netprice, p_total, preorderno, yetkili) VALUES( pid, p_code, p_name, dekorA, p_quantity, p_listprice, p_netprice, p_total, preorderno, yetkili);
END //
DELIMITER ;

调整后可运行的PDO调用代码 #

$query = "CALL NEWLIST(:pid, :p_code, :p_name, :dekorA, :p_quantity, :p_listprice, :p_netprice, :p_total, :preorderno, :yetkili)";

for($count = 0; $count<count($_POST['hidden_p_code']); $count++)
{
    $data = array(
        ':pid'  =>  $_POST['hidden_pid'][$count],
        ':p_code'   =>  $_POST['hidden_p_code'][$count],
        ':p_name'   =>  $_POST['hidden_p_name'][$count],
        ':dekorA'   =>  $_POST['hidden_dekorA'][$count],
        ':p_quantity'   =>  $_POST['hidden_p_quantity'][$count],
        ':p_listprice'  =>  $_POST['hidden_p_listprice'][$count],
        ':p_netprice'   =>  $_POST['hidden_p_netprice'][$count],
        ':p_total'  =>  $_POST['hidden_p_total'][$count],
        ':preorderno'   =>  $_POST['hidden_preorderno'][$count],
        ':yetkili'  =>  $_POST['hidden_yetkili'][$count]
    );
    $statement = $connect->prepare($query);
    $statement->execute($data);
}

经调整后代码已可正常运行，感谢@Professor Abronsius的建议。

内容的提问来源于stack exchange，提问作者muhtarkator