如何与PDO一起使用内爆

Question

我正在尝试将搜索栏与PDO中查询的复选框相结合。

当前代码如下：

$keywordfromform = $_GET["keyword"];
$keyword = "%$keywordfromform%";
$sql = 'SELECT * FROM table
WHERE column1 LIKE ?
OR column2 LIKE ?
OR column3 LIKE ?
ORDER BY column3 ASC';
$stmt = $pdo->prepare($sql);
$stmt->execute(array($keyword, $keyword, $keyword);
$entries = $stmt->fetchAll();
$count = count($entries);

使用相同的逻辑，我尝试应用内爆和in子句如下(根据本网站(：

//search bar
$keywordfromform = $_GET["keyword"];
$keyword = "%$keywordfromform%";
//checkboxes
$column1 = $_GET['checkboxes_column1'];
$implode_column1 = implode(', ', $column1);
$in str_repeat('?,', count($implode_column1) - 1) . '?';

$sql = 'SELECT * FROM table
WHERE column1 LIKE ?
OR column2 LIKE ?
OR column3 LIKE ?
AND column1 IN ($in)
ORDER BY column3 ASC';
$stmt = $pdo->prepare($sql);
$params = array_merge([$keyword], [$keyword], [$keyword], $implode_column1);
$stmt->execute($params);
$entries = $stmt->fetchAll();
$count = count($entries);

搜索栏和复选框是bootstrap中的标准选项，没有什么特别之处。

也许有人可以指出我做错了什么？

Answer 1

问题

1. $sql使用单引号而非双引号；因此$in是文字

2. count($implode_column1)是不可数的；这是一个字符串

3. $in str_repeat(...)-根据注释-需要一个分配操作员

4. array_merge(...)不起作用；$implode_column1是字符串

5. 假设您需要括号来指定假设ORs应分组在一起的操作顺序：

   (a = ... OR b = ... OR c = ...) AND a IN (...)
   

传入数据

假设您的传入数据的格式为：

$_GET['keyword']            = "html";
$_GET['checkboxes_column1'] = [
0 => "php",
1 => "css",
2 => "js",
3 => "html"
];

更新的代码

$keyword = $_GET['keyword']            ?? NULL;
$column1 = $_GET['checkboxes_column1'] ?? NULL;
// Check that the expected data was received
if(!$column1 || !$keyword || !is_array($column1)){
echo "Error: Nothing submitted";
exit;
}
$keyword      = "%{$keyword}%";
$placeholders = implode(", ", array_fill(0, count($column1), "?"));    // Prepare placeholder string
$parameters   = array_merge([$keyword, $keyword, $keyword], $column1); // Prepare array for variables to bind
$sql    = "
SELECT * FROM table
WHERE (
column1 LIKE ?
OR  column2 LIKE ?
OR  column3 LIKE ?
) AND column1 IN   ({$placeholders})
ORDER BY column3 ASC
";
$query  = $pdo->prepare($sql);
$query->execute($parameters);
$result = $query->fetchAll();
$count  = count($result);