How would you approach this task?

The site has a filter for the selection of the characteristics. - brand, color, style, etc.

The characteristics for filtering are checked by checkboxes and then Ajax sends to the server a list of checked and unchecked checkboxes. The page (php) on the server, on the basis of the MARKED CHECKBOXES, forms a query to the database (MySql) and receives the number (count) of goods that have the characteristics marked in the checkboxes. The result is returned to the browser and a window is displayed: X products found. Actually, as in Yandex Market.

I wrote the code

Checkboxes (only example. Actually there will be more checkboxes)

<div id="checkboxes"> <label><input type="checkbox" value="Zara" >zara</label> <label><input type="checkbox" value="Nike" >nike</label> <label><input type="checkbox" value="Adidas" >Adidas</label> </div> <div id="color"> <label><input type="checkbox" value="red" >red</label> <label><input type="checkbox" value="black" >black</label> <label><input type="checkbox" value="blue" >blue</label> </div> <div id="style"> <label><input type="checkbox" value="classic" >classic</label> <label><input type="checkbox" value="modern" >modern</label> </div>

Mysql query

  $brand = $_POST['brand']; $color = $_POST['color']; $style = $_POST['style']; $Query = "SELECT * FROM items WHERE brand IN ('" . implode("','", $brand) . "') AND color IN ('" . implode("','", $color) . "') AND style IN ('" . implode("','", $style) . "')"; $QueryResult = mysqli_query($connection , $Query); while($QueryRow = mysqli_fetch_assoc($QueryResult)){ ?> <div><p><?php echo $QueryRow['cost'];?></p></div> <div><img src="<?php echo $QueryRow['img']; ?>"></div> <?php } } ?>

The problem is as follows. if I select at least one parameter in each group, then everything works, but if at least one group is without a checked checkbox, it gives an error .... I thought about using If, but then I would have to consider all possible options of checked and unchecked checkboxes, that's a lot ..

also tried to shove IF directly into the sql query

 $Query = "SELECT * FROM items WHERE brand IN ('" . implode("','", $brand) . "') ". if(!empty($color)){AND color IN ('" . implode("','", $color) . "')} if(!empty($style)){AND style IN ('" . implode("','", $style) . "')} ." ";

but also does not work

Can someone help with solving this problem?

  • Get an array of checkbox names. run through the array, check whether the corresponding. checkbox, if yes, then do `$ sql. =" and field_name_sootv_sekboksu in (?,?,?) "and bind the parameters to the query. The field name is of course also taken from the array, do not code the blocks for each case with your hands - Mike
  • an array with the names of checkboxes - that is, we get an array in an array, right? - pavel
  • an array of what you want to put in it. I assumed stupidly brand => brand, color => color, ... true if the field names in the database and checkboxes match - then there is nothing to compare there - Mike

2 answers 2

Just add to each array in the generated query text some fake:

 "... WHERE brand IN ('" . implode("','", array_merge($brand, array("fake_value")) . "') AND ..."

    There is one typical course in cases like you. In order not to go through all the possible options, make it a simpler way.

    When compiling a SQL query, they write the main part and add WHERE 1 = 1 to it. The rest of the queries are added using IF statements.

    Sort of:

     $strSql = "SELECT id, name FROM table WHERE 1 = 1 "; if(выбран фильтр по выпадающему списку городов) $strSql .= " and city = " . переменная с городом ; if(выбран фильтр по дате начала) $strSql .= " and date_begin => " . переменная с датой начала ;

    And so on.

    Your specific option is left to you to think for yourself.

    If something is not clear - see, for example, this question: Set the condition for the output of the AND operator