Search with multiple select

I have a form with 2 fields one of which is a multiple select:
<form id="form1" name="form1" method="get" action="wagesList.php"><table>
<tr>
<th>
Select Week:</th>
<td><select name="Week[]" multiple="multiple" id="Week">
<?php
while(!$week->atEnd()) { //dyn select
?>
<option value="<?php echo($week->getColumnVal("Week")); ?>"><?php echo($week->getColumnVal("Week")); ?></option>
<?php
$week->moveNext();
} //dyn select
$week->moveFirst();
?>
</select></td>
<td>&nbsp;</td>
</tr>
<tr>
<th>Travel Expert:</th>
<td><select name="expert" id="expert">
<?php
while(!$expert->atEnd()) { //dyn select
?>
<option value="<?php echo($expert->getColumnVal("TravelExpert")); ?>"><?php echo($expert->getColumnVal("TravelExpert")); ?></option>
<?php
$expert->moveNext();
} //dyn select
$expert->moveFirst();
?>
</select></td>
<td><input name="submit" type="submit" class="managerbutton" id="submit" value="Submit" /></td>
</tr>


</table>
</form>

I am trying to search the database for records that match (both fields are varchar). The expert column is optional, but the Week column has to have a value. This is my code:
<?php
$commission = new WA_MySQLi_RS("commission",$horizon_i,0);
$commission->setQuery("SELECT Commissions.*, Request.TravelExpert FROM Request JOIN Commissions ON Request.ID=Commissions.RequestID WHERE Request.TravelExpert LIKE ? AND Commissions.Week=?");
$commission->bindParam("c", "".$_GET['expert'] ."", "-1"); //colname
$commission->bindParam("s", "".$_GET['Week'] ."", "-1"); //colname1
$commission->execute();
?>
If there is one value submited for 'Week' it works but if multiple are it doesn't. How do I get it to search multiple values?

Try:

<?php

$commission = new WA_MySQLi_RS("commission",$horizon_i,0);

$commission->setQuery("SELECT Commissions.*, Request.TravelExpert FROM Request JOIN Commissions ON Request.ID=Commissions.RequestID WHERE Request.TravelExpert LIKE ? AND Commissions.Week IN ?");

$commission->bindParam("c", "".$_GET['expert'] ."", "-1"); //colname

$commission->bindParam("sl", "".implode(",",$_GET['Week']) ."", "-1"); //colname1

$commission->execute();

?>

That works perfectly if there are more that one values in the week field eg.
?Week[]=117&Week[]=217&expert=&submit=Submit
but if there is only one eg. ?Week[]=117&expert=&submit=Submit
it gives me this error:

SELECT Client.ID AS ClientID, Client.Title, Client.FirstName, Client.Surname FROM Client JOIN Request ON Client.ID=Request.ClientID JOIN Commissions ON Request.ID=Commissions.RequestID WHERE Request.TravelExpert LIKE ? AND Commissions.Week IN ?

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?' at line 1

Try adding the parenthasis like:

$commission->setQuery("SELECT Commissions.*, Request.TravelExpert FROM Request JOIN Commissions ON Request.ID=Commissions.RequestID WHERE Request.TravelExpert LIKE ? AND Commissions.Week IN (?)");

Perfect thanks

I have a new issue with this. I now need to do the same search:
$wagespaid = new WA_MySQLi_RS("wagespaid",$horizon_i,1);
$wagespaid->setQuery("SELECT SUM(Amount) AS sAmount FROM WagesPaid WHERE WagesPaid.Weeks IN (?)");
$wagespaid->bindParam("sl", "".implode(",",$_GET['Week']) ."", "-1");
$wagespaid->execute();

However the WagesPaid.Weeks field in the database is a comma separated field with multiple week numbers. So I have multiple week numbers in the url to search and multiple weeks (separated by commas) in the database to search on. Is this possible?

There is a built in search library in the code, but we don't have it exposed through the extension. That is probably the cleanest solution.

A quick fix would be to write some code to loop through it like:

<?php

$wagespaid = new WA_MySQLi_RS("wagespaid",$horizon_i,1);

$wagespaid->setQuery("SELECT SUM(Amount) AS sAmount FROM WagesPaid WHERE 1=0");

for ($x=0; isset($_GET['Week']) && $x<sizeof($_GET['Week']); $x++) {

  $wagespaid->Statement = $wagespaid->Statement . " OR Concat(',',WagesPaid.Weeks,',') LIKE '%,".intval($_GET['Week'][$x]).",%'";

}

$wagespaid->bindParam("sl", "".implode(",",) ."", "-1");

$wagespaid->execute();

?>