mercredi 10 avril 2019

Using HTML checkboxes to dynamically create SQL query within PHP code

I currently have a html / PHP webpage which uses a series of user inputs to query a SQL database and return data for plotting. The user input is driven by html checkboxes, driven by data in the SQL database and created using PHP code. The entire html code is too large to put on here but below is an excerpt of the part I want to ask about...

...

<div id="AccProject" class="w3-hide">

<?php 
  $queryProject = "SELECT DISTINCT ProjID, Project, Sample, Type, Description FROM PROJECTS ORDER BY Project ASC";
  $resultProject = $dbhandle->query($queryProject) or exit("Error code ({$dbhandle->errno}): {$dbhandle->error}");
  while($row=mysqli_fetch_array($resultProject)){
    echo "<input id='".$row['ProjID']."' value='".$row['ProjID']."' name='projID[]' type='checkbox' class='chkbox'>";
    echo "<label title='".$row['Description']."&#13;&#13;Project: ".$row['Project']."&#13;Sample: ".$row['Sample']."&#13;Type: ".$row['Type']."' for='".$row['ProjID']."' class='chkbox-label'> ".$row['Project']." | ".$row['Sample']." | ".$row['Type']."</label>";
  }
?>

</div>

...

You can see that I query the SQL database, return a number of fields and then use PHP to generate a number of checkboxes. Later the in the html, I use javascript to extract the checked / unchecked state of these checkboxes and query the database again to return more data.

What I'd like to do is add a number of pre-filters which will reduce the number of checkboxes visible to the user on the page. For example, if the 'Type' field defines whether the data entry is a 'Cake', 'Biscuit' or 'Pudding', I would like to add some switches which would then amend the SQL query. I've given an example with some pseudo code below.

...

<div id="AccProject" class="w3-hide">

<label class="form-switch"><input type="checkbox" id="filterCake"><i></i> Select Cakes only </label>
<label class="form-switch"><input type="checkbox" id="filterBiscuit"><i></i> Select Biscuits only </label>
<label class="form-switch"><input type="checkbox" id="filterPudding"><i></i> Select Puddings only </label>

<?php 

  if filterCake == True
    $queryProject = "SELECT DISTINCT ProjID, Project, Sample, Type, Description FROM PROJECTS WHERE Type == 'Cake' ORDER BY Project ASC";

  if filterBiscuit == True
    $queryProject = "SELECT DISTINCT ProjID, Project, Sample, Type, Description FROM PROJECTS WHERE Type == 'Biscuit'  ORDER BY Project ASC";

  if filterPudding == True
    $queryProject = "SELECT DISTINCT ProjID, Project, Sample, Type, Description FROM PROJECTS WHERE Type == 'Pudding' ORDER BY Project ASC";


  $resultProject = $dbhandle->query($queryProject) or exit("Error code ({$dbhandle->errno}): {$dbhandle->error}");
  while($row=mysqli_fetch_array($resultProject)){
    echo "<input id='".$row['ProjID']."' value='".$row['ProjID']."' name='projID[]' type='checkbox' class='chkbox'>";
    echo "<label title='".$row['Description']."&#13;&#13;Project: ".$row['Project']."&#13;Sample: ".$row['Sample']."&#13;Type: ".$row['Type']."' for='".$row['ProjID']."' class='chkbox-label'> ".$row['Project']." | ".$row['Sample']." | ".$row['Type']."</label>";
  }
?>

</div>

...

How can I achieve this with my code?

All I need to do is change the SQL query within the PHP code (adding a WHERE clause) based on the state of the cake, biscuit or pudding checkboxes but I've been struggling to see how to do this.

Any help greatly appreciated!




Aucun commentaire:

Enregistrer un commentaire