在循环中用准备好的语句插入几行 [英] INSERT several rows with a prepared statement in a loop
问题描述
使用数组部分插入时,sqlserv php错误
Sqlserv php error when inserting using array portion
阵列准备
foreach ($data as $rs) {
$params []="({$rs->hd},'{$rs->dvn}',{$rs->mth},{$rs->yr},{$rs->stid},{$rs->prcd},'{$rs->prnm}',{$rs->prte},{$rs->ssl},{$rs->clsk},1)";
}
插入语句:
$SqlInsert="insert into SQl_test (Ss_Hq_cd,Ss_division,Ss_month,Ss_yr,Ss_stk_Id,Ss_prod_cod,Ss_prod_name,ss_prod_rate,Ss_Sale,Ss_Cl_stk,ss_tran_stat) values(?,?,?,?,?,?,?,?,?,?,?) ";
$stmt = sqlsrv_query( $conn, $SqlInsert,$params);
色情:
语句准备/执行中的错误.\ n"
Error in statement preparation/execution.\n"
Array([0] => Array([0] => 22018 [SQLSTATE] => 22018 [1] => 245 [代码] => 245 [2] => [Microsoft] [ODBC Driver for SQL服务器] [SQL Server]将varchar值'(757,'MAIN',12,2018,100899,1250,'xyz',0,100,45,1)'转换为数据类型int时转换失败.[message] => [Microsoft] [用于SQL Server的ODBC驱动程序13] [SQL Server]将varchar值'(757,'MAIN',12,2018,100899,1250,'xyz',0,100,45,1)'转换为时,转换失败数据类型为int.))
Array ( [0] => Array ( [0] => 22018 [SQLSTATE] => 22018 [1] => 245 [code] => 245 [2] => [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Conversion failed when converting the varchar value '(757,'MAIN',12,2018,100899,1250,'xyz',0,100,45,1)' to data type int. [message] => [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Conversion failed when converting the varchar value '(757,'MAIN',12,2018,100899,1250,'xyz',0,100,45,1)' to data type int. ) )
推荐答案
$params
的每个数组元素都只是创建为字符串...
Each array element of $params
is just created as a string...
$params []="({$rs->hd},'{$rs->dvn}',{$rs->mth},{$rs->yr},{$rs->stid},{$rs->prcd},'{$rs->prnm}',{$rs->prte},{$rs->ssl},{$rs->clsk},1)";
您可能打算将其创建为数组...
You probably meant to create this as an array...
$params []=[$rs->hd,$rs->dvn,$rs->mth,$rs->yr,$rs->stid,$rs->prcd,$rs->prnm,$rs->prte,$rs->ssl,$rs->clsk,1];
然后您将循环运行INSERT,一次将每个数据数组一次传递给查询...
You would then run the INSERT in a loop, pass each array of data one at a time to the query...
$SqlInsert="insert into SQl_test (Ss_Hq_cd,Ss_division,Ss_month,Ss_yr,Ss_stk_Id,Ss_prod_cod,Ss_prod_name,ss_prod_rate,Ss_Sale,Ss_Cl_stk,ss_tran_stat) values(?,?,?,?,?,?,?,?,?,?,?) ";
foreach ( $params as $param ) {
$stmt = sqlsrv_query( $conn, $SqlInsert,$param);
}
使用mysqli-您还可以在循环之前准备INSERT并仅对循环中的每一行数据执行它,SQL Server中可能有类似的事情,但是我所不擅长的领域.
With mysqli - you would also prepare the INSERT before the loop and just execute it with each row of data in the loop, there is probably a similar thing in SQL Server, but not my area of knowledge.
这篇关于在循环中用准备好的语句插入几行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!