>我有这个数组JSON POST请求到一个PHP文件。
Array
(
[user_id] => 1
[date] => 2014-12-05
[time] => 12:00
[description] => lol
[friends] => "12","9"
[PHPSESSID] => 5ae7c3e6339c528e7804020dd0f0cdbb
)
我尝试使用单个 sql 查询将值 (12 | 1) 和 (9 | 1) 添加到 mysql 表中
桌子:
u_id | f_id
1 | 12
1 | 9
到目前为止,我拥有的:
$friendarray = $_POST['Friends'];
foreach( $friends as $friendsarray ) {
$values[] = "(" . $u_id . "," . $friendsarray . ")";
}
$query = "INSERT INTO up2_friends_to_users (u_id , f_id ) VALUES ".implode(',',$values);
$stmt = $db->prepare($query);
$result = $stmt->execute();
如您所见,这根本不起作用。我尝试实现这样的事情:
$query_params = array(
':u_id' => $_POST['user_id'],
':f_id' => $friendid,
然后想像这样发送:
$stmt = $db->prepare($query);
$result = $stmt->execute($query_params);
是否可以像这样创建包含多行的单个查询?
感谢RobP的回答:
$friendsarray = explode(',',$_POST['friends']);
$placeholders = [];
for($i=0, $len=count($friendsarray); $i < $len; $i++) {
$placeholders[i] .= "(:u_id".$i.", :f_id".$i.")"; // entries like "(:u_id0, :f_id0)"
}
$query = "INSERT INTO up2_friends_to_users (u_id , f_id ) VALUES ".implode(",", $placeholders);
$stmt = $db->prepare($query);
for($i=0, $len=count($placeholders); $i < $len; $i++) {
$stmt->bindParam(':u_id'.$i, $_POST['user_id']);
$nextFriend = $friendsarray[$i];
$stmt->bindParam(':f_id'.$i,trim($nextFriend,'"'));
}
$result = $stmt->execute();
现在f_id始终为空。
我同意最好的策略是像您尝试的那样使用单个查询。对于长列表,这将要快得多,特别是如果您没有将所有单独的插入包装到单个提交中。这应该有效:
$friendarray = $_POST['Friends'];
$placeholders = [];
$user_id = $_POST[`user_id`];
for($i=0, $len=count($friendarray); $i < $len; $i++) {
$placeholders[$i] = "(:u_id".$i.", :f_id".$i.")"; // entries like "(:u_id0, :f_id0)"
}
$query = "INSERT INTO up2_friends_to_users (u_id , f_id ) VALUES ".implode(",", $placeholders);
$stmt = $db->prepare($query);
for($i=0, $len=count($placeholders); $i < $len; $i++) {
// each binding must use a separate variable, not an array element
$stmt->bindParam(':u_id'.$i, $user_id);
// use your favorite escape function on the value here
$nextFriend = $db->real_escape_string($friendarray[$i]);
$stmt->bindValue(':f_id'.$i, $nextFriend);
}
编辑:从中学到了一些新东西 只有变量可以通过引用传递 - php。无法将数组元素作为第二个参数传递给绑定参数!上面发布的解决方法。
这样做:
$query = "INSERT INTO up2_friends_to_users (u_id , f_id ) VALUES (:u_id, :f_id)";
$stmt = $db->prepare($query);
$stmt->bindParam(':u_id', $_POST['user_id'];
$stmt->bindParam(':f_id', $friendid);
foreach ($_POST['Friends'] as $friendid) {
$stmt->execute();
};
bindParam
绑定到引用,因此每次执行查询时,它都将使用循环当前迭代中的 $friendid
值。
也许是这样的(使用问号参数)?
$values = array();
foreach ($_POST['Friends'] as $friendid) {
$values[] = $u_id;
$values[] = $friendid;
}
$conn = new PDO($dsn, $user, $password);
$query = 'INSERT INTO up2_friends_to_users (u_id , f_id ) VALUES '
. trim(str_repeat('(?, ?),', count($values / 2)), ',');
$conn->prepare($query)->execute($values);