在帶有數組的 IN 語句之後，在 WHERE 語句中使用更多佔位符 (Use more placeholders in WHERE statement after IN statement with arrays)

問題描述

在帶有數組的 IN 語句之後，在 WHERE 語句中使用更多佔位符 (Use more placeholders in WHERE statement after IN statement with arrays)

我需要將更多佔位符添加到以下查詢中。這可能嗎？我不知道從哪裡開始，我在 Internet 上找不到任何選項。

現在查詢：

$in2  = str_repeat('?,', count($arrayid) ‑ 1) . '?';
$sql2 = "SELECT COUNT(id) AS totalacc FROM account WHERE id IN ($in2) ";
$stmt2  = $mysqli‑>prepare($sql2);
$types2 = str_repeat('i', count($arrayid));
$stmt2‑>bind_param($types2,...$arrayid);
$stmt2‑>execute();
$stmt2‑>bind_result($row['totalacc']);
    while($stmt2‑>fetch()) $totalacc = $row['totalacc']; 

我的目標查詢：

$countname1 = '(Hallo)';
    $countname = trim(filter_var("%{$countname1}%", FILTER_SANITIZE_STRING));
$in2  = str_repeat('?,', count($arrayid) ‑ 1) . '?';
$sql2 = "SELECT COUNT(id) AS totalacc FROM account WHERE id IN ($in2) AND name LIKE ?";
$stmt2  = $mysqli‑>prepare($sql2);
$types2 = str_repeat('i', count($arrayid));
$stmt2‑>bind_param($types2,s,...$arrayid,$countname); // Will never work, but how to do this? 
$stmt2‑>execute();
$stmt2‑>bind_result($row['totalacc']);
    while($stmt2‑>fetch()) $totalacc = $row['totalacc']; 

參考解法

方法 1:

You just need to append the extra values to your array of parameters and string of types. This should work (untested):

$countname1 = '(Hallo)';
$countname = trim(filter_var("%{$countname1}%", FILTER_SANITIZE_STRING));
$in2  = str_repeat('?,', count($arrayid) ‑ 1) . '?';

$sql2 = "SELECT COUNT(id) AS totalacc FROM account WHERE id IN ($in2) AND name LIKE ?";
$stmt2  = $mysqli‑>prepare($sql2);

$types2 = str_repeat('i', count($arrayid));
$types2 .= "s"; //append "s" to the end of the $types2 string.
$arrayid[] = $countname; //append the value of $countname to the array of parameters

$stmt2‑>bind_param($types2, ...$arrayid); 
$stmt2‑>execute();
//...etc

方法 2:

The easiest solution would be to use PDO instead of mysqli. This would be so much easier.

If you are stuck with mysqli then you can achieve a similar thing by simply ignoring the types and appending your result to the array.

$countname1 = '(Hallo)';
$countname = "%{$countname1}%";
$in2 = str_repeat('?,', count($arrayid) ‑ 1) . '?';
$sql2 = "SELECT COUNT(id) AS totalacc FROM account WHERE id IN ($in2) AND name LIKE ?";
$stmt2 = $mysqli‑>prepare($sql2);

$arrayid[] = $countname;
$stmt2‑>bind_param(str_repeat('s', count($arrayid)), ...$arrayid);
$stmt2‑>execute();
$stmt2‑>bind_result($totalacc);
$stmt2‑>fetch();

You could even write a function to abstract from all of this code.

(by user15259803、ADyson、Dharman)

參考文件

1. Use more placeholders in WHERE statement after IN statement with arrays (CC BY‑SA 2.5/3.0/4.0)