Several multiple select (HTML) search with PHP and SQL

Question

I'm stuck in a piece of code that I can't make it work. I need help. Here it goes:

I want to construct a search with several multiselect, PHP and SQL. In the exemple I'll use 02 (two) multiselects:

<form action="" method="post">
<!-- first select with its options-->
<select name="search[field1]" multiple="multiple">
  <option value="All">-- All --</option>
  <option value="value11">Value 11</option>
  <option value="value12">Value 12</option>
  <option value="value13">Value 13</option>
  <option value="value14">Value 14</option>
</select>
  
<!-- second select with its options -->
<select name="search[field2]" multiple="multiple">
  <option value="All">-- All --</option>
  <option value="value21">Value 21</option>
  <option value="value22">Value 22</option>
  <option value="value23">Value 23</option>
  <option value="value24">Value 24</option>
</select>
  
<!-- input submit -->
<input type="submit" value="Search" name="submit"/>
  
</form><!-- \. end form -->

Now, I submit the form to the PHP which it's in the same page as the form:

<?php

#first I check if the form was submitted
if(isset($_POST['submit'])){

  #catch the values from the multiple select forms
  $mapping = array (
      'field1' => 'Name_of_the_table_field_1' , 
      'field2' => 'Name_of_the_table_field_2'
  );

  $stmt = "select * from [DATA_BASE].[dbo].[TABLE] ";
  $operator = " AND ";

  if (isset($_POST['search'])){
    $stmt = $stmt . "where ";
    foreach ($_POST['search'] as $key=>$val) {
      $stmt .= $mapping[$key] ." =(case when ".$val." ='All' then ".$mapping[$key]."    else  '".$val."' end)" . $operator; 
    }
}

var_dump($stmt);


?>

Here, I stuck! I don't know how to catch multiple values from the two multiple select at the same time and parsing them to a SQL search.

E.g.: the search should return sth like "select * from [DATA_BASE].[dbo].[TABLE] where Name_of_the_table_field_1 = (case when Name_of_the_table_field_1 = 'All' then Name_of_the_table_field_1 else 'value11' AND "value12" end) AND Name_of_the_table_field_2 = (case when Name_of_the_table_field_2 = 'All' then Name_of_the_table_field_2 else 'value21' AND "value23" end)

I could handle that by treating the multiselects one by one, but this would become a big chunk of code at the end, also it would be very difficult to understand.

I hope you guys have understood me, I really need help!

Thanks in advance!!!

PS.: the code is catching the first option select in each multiselect, but it's not holding the others.

score 0 · Accepted Answer · answered Feb 24 '15 at 05:37

I just did the following to solve my problem. I think it's the dummy way of doing things with multiple selects, but it was what I could do.

My form:

<form action="" method="post">
  <select name="select1[]" multiple>
    <option value="value1">Value1</option>
    <option value="value2">Value2</option>    
    <option value="value3">Value3</option>
  </select>
  
  <select name="select2[]" multiple>
    <option value="value1">Value1</option>
    <option value="value2">Value2</option>    
    <option value="value3">Value3</option>
  </select>
  
  <select name="select2[]" multiple>
    <option value="value1">Value1</option>
    <option value="value2">Value2</option>    
    <option value="value3">Value3</option>
  </select>
  
  <input type="submit" name="search" value="Search!"/>
  
</form>

if(isset($_POST['search'])){
    $select1 = implode(", ",$_POST['select1']);
    $select2 = implode(", ",$_POST['select2']);
    $select3 = implode(", ",$_POST['select3']);
    $select4 = "'" .implode("', '",$_POST['select4']). "'";
    $select5 = "'" .implode("', '",$_POST['select5']). "'";


    $query = "select * from [DATABASE].[dbo].[TABLE] where Column1 in ($select1) and Column2 in ($select2) and Column3 in ($select3) and Column4 in ($select4) and Column5 in ($select5)";

$exec = sqlsrv_query($conn, $query);

[...] so on

This is just a generic example, but if you specify the paremeters of the form and database, it will work.

Thank you so much!

Several multiple select (HTML) search with PHP and SQL

1 Answers1