Marco Bagiacchi Marco Bagiacchi - 28 days ago 12
HTML Question

PHP - Select sql from html checkbox

I have an html form, with some checkbox.
My form with 4 checkbox

I want to write an sql to include my checkbox.


For example on my Database:

Column: Stato

Options: 0,1,2,3
Where:


  • 0 = Aperto,

  • 1 = Stampato,

  • 2 = Bloccato,

  • 3 = ❤ (Favorite),



So, if they are all selected you must show me all kinds, otherwise not.


How can I write sql?

This is my checkbox:

<div id="radioset">
<input type="checkbox" id="radio1" name="aperto" checked="checked"><label for="radio1">Aperto</label>
<input type="checkbox" id="radio2" name="stampato" checked="checked"><label for="radio2">Stampato</label>
<input type="checkbox" id="radio3" name="bloccato" checked="checked"><label for="radio3">Bloccato</label>
<input type="checkbox" id="radio4" name="favorite" checked="checked"><label for="radio4">&#9829</label>
</div>


And this is my PHP sql:

$input=$_POST['input'];
$id=$_POST['id'];
$tipo=$_POST['tipo'];
$numero1=$_POST['numero1'];
$numero2=$_POST['numero2'];
$data1=date('d/m/Y', strtotime($_POST['from']));;
$data2=date('d/m/Y', strtotime($_POST['to']));;

$sql="SELECT [Id Ord] AS [ID], [Tipo Ord] AS [Tipo], [N Ord] AS [Numero], [Data Ord] AS [Data], [Ragione Sociale], [Indirizzo], [TotImp] AS [IMPORTO TOTALE], [TotIva] AS [IMPORTO IVA] FROM [Ordini] WHERE [Id Ord] LIKE '$id' OR [Tipo Ord] LIKE '$tipo' OR [Data Ord] BETWEEN #$data1# AND #$data2#";

$rs = $con->execute($sql);


Thanks!

Answer

Firstly, define your checkboxes as an array (don't use the terminology "radio" - radio buttons are quite different!), and change the values so they match the database records:

<div id="checkboxes">
  <input type="checkbox" id="checkbox1" name="options[]" value="0" checked="checked"><label for="checkbox1">Aperto</label>
  <input type="checkbox" id="checkbox2" name="options[]" value="1" checked="checked"><label for="checkbox2">Stampato</label>
  <input type="checkbox" id="checkbox3" name="options[]" value="2" checked="checked"><label for="checkbox3">Bloccato</label>
  <input type="checkbox" id="checkbox4" name="options[]" value="3" checked="checked"><label for="checkbox4">&#9829</label>
</div>

Then you can use a for each loop to loop through them, and if the option is present (checkboxes values don't even exist in the postback if the box wasn't checked) then add a bit of SQL to include that option in the search:

$optionsSQL = "";

foreach($_POST["options"] as $index => $option) {
  if ($optionsSQL = "") $optionsSQL = "AND Stato IN ("; //if it's the first detected option, add the IN clause to the string
  $optionsSQL .= $option.",";
}

//trim the trailing comma and add the closing bracket of the IN clause instead
if ($optionsSQL != "") 
{
  $optionsSQL = rtrim($optionsSQL, ","); 
  $optionsSQL .= ")";
}

Then lastly, append the $optionsSQL string to your query:

$sql="SELECT [Id Ord] AS [ID], [Tipo Ord] AS [Tipo], [N Ord] AS [Numero], [Data Ord] AS [Data], [Ragione Sociale], [Indirizzo], [TotImp] AS [IMPORTO TOTALE], [TotIva] AS [IMPORTO IVA] FROM [Ordini] WHERE [Id Ord] LIKE '$id' OR [Tipo Ord] LIKE '$tipo' OR [Data Ord] BETWEEN #$data1# AND #$data2#".$optionsSQL;

N.B. Since this is a quick example I have not taken steps here to guard against SQL injection. You should look into how to guard against this - a malicious user could tamper with the option values and try to submit problematic data. You should validate each option value before you use it in a SQL statement.