Best way for multi option query

[ZOR]

Before I start, what is the best way to construct select query statements where say 10 options of inclusion are present. eg, user wants a list containing any of 10 option boxes selected, do I have to do a chain of IF/ELSE/THEN statements followed by X amount of select statements, or is there a magic way round? Thanks

 

[jpadie]

if all of your option boxes are called "option[]" in the html (and all have values set) then their values are provided to you as an array in the superglobal $_POST['option']. if you need to get their values you could do something like

$query = "";
foreach ($_POST['option'] as $option):
  $query .= ",'$option'";
endforeach;
$sql = "select from tablename where column IN (".ltrim($query,",").")";

which would be similar to an OR query across you results. if you use the table column as a home for a delimited list then you would have to do something like

$query = "";
$colname ="";
$delimiter=";";
foreach ($_POST['option'] as $option):
  $query = empty($query)
              ? "$colname like '%{$option}{$delimiter}%' "
              : $query . " OR $colname like '%{$option}{$delimiter}%' "; 
endforeach;
$sql = "select from tablename where $query";

 

[KarveR]

jpadie, does your top option ignore empty variables ?

ZOR, when you say 10 boxes, are these say e.g. 10 order numbers from 1 column (see jpadies answer(1)) or could they be order number, part number , type, name etc - e.g different columns from the table?

if so you'd need to build your query testing for emtpy/unset values and only including those items which have been filled/set.



______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[jpadie]

jpadie, does your top option ignore empty variables ?

yes, if they are checkboxes as unchecked controls will not be submitted.

it does not protect against form spoofing but that is a different topic.

 

[ZOR]

Thanks both. The 10 options are checkboxes. Any of which or all couls get selected. They select whether certain products are to be included in the viewing of past orders held in the database.

What happens if the query contains a chain of session variables where some are empty? Would the query pass through with some variables filled and some not. The session variables being suitably filled with what they should have in them related to values in the checkboxes. ie not checked no data in variable. Thanks