How to prepare a few queries(filter product)?

Good day, this problem with filters, is a form of filtering:
<form method="POST" action="/catalog">
 <p class="filter_name">City</p>
 <ul class="filter_box">
 <li class="" style="width: 85%;"><input name="city[]" type="checkbox" value="Minsk" style="display:none" >Minsk</li>
<li class="" style="width: 85%;"><input name="city[]" type="checkbox" value="Grodno" style="display:none" >Grodno</li>
<li class="" style="width: 85%;"><input name="city[]" type="checkbox" value="Brest" style="display:none" >Brest</li>
<li class="" style="width: 85%;"><input name="city[]" type="checkbox" value="Vitebsk" style="display:none" >Vitebsk</li>
<li class="" style="width: 85%;"><input name="city[]" type="checkbox" value="Gomel" style="display:none" >Gomel</li>
<li class="" style="width: 85%;"><input name="city[]" type="checkbox" value="Mogilev" style="display:none" >Mogilev</li>
 <div class="clearfix"></div>
</ul>
 <p class="filter_name">Parameter2</p>
 <ul class="filter_box">
 <li class="" ><input name="param2[]" type="checkbox" value="Value1" style="display:none" >Value1</li>
 <li class="" ><input name="param2[]" type="checkbox" value="Value2" style="display:none" >Value2</li>
 <li class="" ><input name="param2[]" type="checkbox" value="Value3" style="display:none" >Value3</li>
 <li class="" ><input name="param2[]" type="checkbox" value="Значение4" style="display:none" >Значение4</li>
 <div class="clearfix"></div>
</ul>

 <p class="filter_name">Parameter3</p>
 <ul class="filter_box">
 <li class="" ><input name="param3[]" type="checkbox" value="Value1" style="display:none" >Value1</li>
 <li class="" ><input name="param3[]" type="checkbox" value="Value2" style="display:none" >Value2</li>
 <li class="" ><input name="param3[]" type="checkbox" value="Value3" style="display:none" >Value3</li>
 <li class="" ><input name="param3[]" type="checkbox" value="Значение4" style="display:none" >Значение4</li>
 <div class="clearfix"></div>
</ul>
 <p class="filter_name">Parameter4</p>
 <ul class="filter_box">
 <li class="" ><input name="status[]" type="checkbox" value="1" style="display:none" >Yes</li>
 <li class="" ><input name="status[]" type="checkbox" value="0" style="display:none" >No</li>
 <div class="clearfix"></div>
</ul>

 <div class="more_filter_btn">
 <input type="submit" class="btn" name="filter" id="moreFilterBtn" value="Filter">
</div>
 </form>

Processor:
<?php

 function addWhere($where, $add, $and = true) {
 if ($where) {
 if ($and) $where .= "AND $add";
 else $where .= "OR $add";
}
 else $where = $add;
 return $where;
}
 if (!empty($_POST["filter"])) {
 $where = "";
 if ($_POST["city"]) {
$ids=$_POST["city"];
 $incity = str_repeat('?,', count($ids) - 1) . '?';
 $where = addWhere($where, 'city IN ('. $incity .')');
}
 if ($_POST["param2"]) {
 $ids = $_POST["param2"];
 $inparam2 = str_repeat('?,', count($ids) - 1) . '?';
 $where = addWhere($where, 'param2 IN ('. $inparam2 .')');
}
 if ($_POST["param3"]) {
 $ids = $_POST["param3"];
 $inparam3 = str_repeat('?,', count($ids) - 1) . '?';
 $where = addWhere($where, 'param3 IN ('. $inparam3 .')');
}
 if ($_POST["status"]) $where = addWhere($where, "`status` IN (".htmlspecialchars(implode(",", $_POST["status"])).")");
 $sql = "SELECT * FROM `trfilters`";
 if ($where) $sql .= "WHERE $where";
 $stmt = $db->prepare($sql);
$stmt->execute($ids);
$filters = $stmt->fetchAll();
foreach ($filters as $filter) { ?> 

<?php }
}


?>

When choosing param2 and param3 error
Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in

Tell me how in the $stmt->execute($ids); pass all the parameters $ids.
Perhaps, I not correctly wrote, the first time the filter is doing, thanks in advance for your help.
April 4th 20 at 00:33
1 answer
April 4th 20 at 00:35
Solution
Your code to read of course it has a lesson, it would be worth to format. I think the point is that if you select multiple, param2 and param3 for example, you $stmt->execute pass $ids only from $_POST['param3']
if ($_POST["param2"]) {
 $ids = $_POST["param2"];
if ($_POST["param3"]) {
 $ids = $_POST["param3"];
I understand, but how to transfer everything? - Cale.Kihn commented on April 4th 20 at 00:38
Well, for example
$all_ids = [];
if ($_POST["city"]) {
$ids=$_POST["city"];
 $all_ids = array_merge($all_ids, $ids);
}
if ($_POST["param2"]) {
 $ids = $_POST["param2"];
 $all_ids = array_merge($all_ids, $ids);
}
- Adalberto.Waelchi58 commented on April 4th 20 at 00:41
@Adalberto.Waelchi58, exactly, that I'm a fool) thank you!
and do not tell me how to implement a filter for the city, the essence is this:
in column sity need to put an array of cities, for example added Город1 and Город4
In the filter chose Город3 and Город4 and how to make a query that would output a string, which has at least 1 city match? - Cale.Kihn commented on April 4th 20 at 00:44
I your code difficult to understand how you have it all implemented. What is the format of the table lie the towns? In one column? Maybe once there are several, is in a separate table to keep? - Adalberto.Waelchi58 commented on April 4th 20 at 00:47
@Adalberto.Waelchi58in this format ["Город1","City2"] to lie, so, and as if in a separate display as one row to do the search? - Cale.Kihn commented on April 4th 20 at 00:50
so as you have (assuming that the format of such), you can try city LIKE '%City 3% OR city LIKE '%City 4%,' but I never told you that, and so do not suggest it, because with a few thousand rows, the query will run for a very long time.
In such cases, however, is to have a separate table for cities cities(city_id, city_name), to attach the records cities_to_items(item_id, city_id)
and select like so
SELECT DISTINCT i.id i.* FROM items i LEFT JOIN cities_to_items cti ON cti.item_id = i.WHERE id cti.city_id IN (list IDE cities)
DISTINCT need to write that have multiple cities, do not occur - Adalberto.Waelchi58 commented on April 4th 20 at 00:53
@Adalberto.Waelchi58, in the first variant, for some reason only displays if one value in the search(I did), but with the second option I do not understand how to do it with my filters, and the first for me, because rows will be a maximum of 100 - Cale.Kihn commented on April 4th 20 at 00:56
Well, if you really write a filter, it would be worthwhile to delve deeper into in the mysql, the filter is a complicated thing)
And about only shows one - here it is for you to suffer, we must look at what kind of data is stored which query you get, etc., to carry out debugging. Time works with one, it will work with multiple - Adalberto.Waelchi58 commented on April 4th 20 at 00:59

Find more questions by tags PHPMySQL