I am writing a filter for point output from MySql database. There is a table of 20 columns ( info_id, info_text, info_data, info_department, info_author, info_leader , etc.). The task to display data based on several criteria at once. For input, use the html form with select multiple = "yes".

Example of use : select the 3rd department ( info_department ), 15 authors ( info_author ), who have a head Andrei Sidorov ( info_leader ), and display their texts in a table.

The essence of the problem : the filter gives an error in the only case - if 2 conditions are info_department and info_leader simultaneously. In other cases, it works as it should.
Mistake:

Fatal error: Call to a member function fetch_assoc () on a non-object;

Debug showed that info_leader fulfills the condition $check == 0 , although it should according to the logic of $check > 0 .

Tell me where to look and where made a mistake?

 if (isset($_POST['filter'])) { $selected_department = array(); $selected_department = $_POST['info_department']; $selected_author = array(); $selected_author = $_POST['info_author']; $selected_leader = array(); $selected_leader = $_POST['info_leader']; $selected_guarantor = array(); $selected_guarantor = $_POST['info_guarantor']; $selected_status = array(); $selected_status = $_POST['info_status']; $sql = 'SELECT * FROM info'; if (count($selected_department) > 0 && $check > 0) { $sql .= ' AND info_department IN ("' . implode('", "', $selected_department).'")'; $check = 1; } else if (count($selected_department) > 0 && $check == 0 ) { $sql .= ' WHERE info_department IN ("' . implode('", "', $selected_department).'")'; $check = 1; } else { $check = 0; } if (count($selected_author) > 0 && $check > 0) { $sql .= ' AND info_author IN ("' . implode('", "', $selected_author).'")'; $check = 1; } else if (count($selected_author) > 0 && $check == 0) { $sql .= ' WHERE info_author IN ("' . implode('", "', $selected_author).'")'; $check = 1; } else { $check = 0; } if (count($selected_leader) > 0 && $check > 0) { $sql .= ' AND info_leader IN ("' . implode('", "', $selected_leader).'")'; $check = 1; } else if (count($selected_leader) > 0 && $check == 0) { $sql .= ' WHERE info_leader IN ("' . implode('", "', $selected_leader).'")'; $check = 1; } else { $check = 0; } if (count($selected_guarantor) > 0 && $check > 0) { $sql .= ' AND info_guarantor IN ("' . implode('", "', $selected_guarantor).'")'; $check = 1; } else if (count($selected_guarantor) > 0 && $check == 0) { $sql .= ' WHERE info_guarantor IN ("' . implode('", "', $selected_guarantor).'")'; $check = 1; } else { $check = 0; } if (count($selected_status) > 0 && $check > 0) { $sql .= ' AND info_status IN ("' . implode('", "', $selected_status).'")'; $check = 1; } else if (count($selected_status) > 0 && $check == 0) { $sql .= ' WHERE info_status IN ("' . implode('", "', $selected_status).'")'; $check = 1; } else { $check = 0; } $sql .= " ORDER BY info_id DESC"; $result = $connect->query($sql); while($array = $result->fetch_assoc()) {
  • And what does the SQL query look like when sampling two conditions? - cyadvert

2 answers 2

The problem is in logic.
The $check variable set to 1 during the processing of $seected_department is reset to zero after passing the $selected_author check. Therefore, in $selected_leader , $check==0 triggered.
Those. in theory, such a problem should be in any case, where the selected criteria are not consecutive.

Also, I would suggest a slightly different scheme for creating such queries: store all WHERE elements in an array, and then merge them. In this case, you do not need to remember what has already been passed, and do not need to figure out WHERE to put or AND .

Like this:

 $whereArr = Array(); if (count($selected_department) > 0) { $whereArr[] = 'info_department IN ("' . implode('", "', $selected_department).'")'; } if (count($selected_author) > 0) { $whereArr[] = 'info_author IN ("' . implode('", "', $selected_author).'")'; } if (count($selected_leader) > 0) { $whereArr[] = 'info_leader IN ("' . implode('", "', $selected_leader).'")'; } if (count($selected_guarantor) > 0) { $whereArr[] = 'info_guarantor IN ("' . implode('", "', $selected_guarantor).'")'; } if (count($selected_status) > 0) { $whereArr[] = 'info_status IN ("' . implode('", "', $selected_status).'")'; } $sql = 'SELECT * FROM info'; if (Count($whereArr)>0) { $sql .= ' WHERE ' . implode(' AND ', $whereArr); } $sql .= " ORDER BY info_id DESC"; $result = $connect->query($sql);
  • Thanks so much for solving with array. Everything worked as required by the task. - Kanzafarov S.

I usually prefer to add a true condition to the query, like '1 = 1' and quietly add through AND no longer worrying about anything:

 $parm=array('info_department','info_author','info_leader','info_guarantor','info_status'); $sql="select * from info where 1=1"; foreach($parm as $k) { if(array_key_exists($k,$_POST) && is_array($_POST[$k]) && count($_POST[$k])>0) { $sql.=' AND '.$k.' IN("'. implode('","', str_replace(array('"','\\'),array('',''),$_POST[$k]) ).'")'; } }

str_replace I str_replace quotes and backslash from the incoming data, in order to avoid SQL injection.