SQL Checkbox search which displays list of items that are in my database

Question

I'm setting up a directory page that displays different workouts depending on what is searched through a text field(which works) and/or checkboxes(Doesn't work)

I don't know if it's possible without a submit button but I would like it to search for items as well as letting it search for multiple checkboxes

I've tried looking on the internet for help but I can't find anything that's related to my project.

$sqlBrowse = "SELECT * FROM workoutLibrary
                WHERE name LIKE :findInName
                OR bodyPart  LIKE :findInBodyPart
                OR muscleGroup LIKE :findInMuscleGroup
                OR equipment LIKE :findInEquipment
                ORDER BY name";

$searchTerm = '';
$search = '%';
if (isset($_POST['search'])) {
    $searchTerm = $_POST['search'];
    $search = '%' . $searchTerm . '%';
    $stmt = $conn->prepare($sqlBrowse);
    $stmt->bindParam(":findInName", $search);
    $stmt->bindParam(":findInBodyPart", $search);
    $stmt->bindParam(":findInMuscleGroup", $search);
    $stmt->bindParam(":findInEquipment", $search);
    $stmt->execute();
    $exercises = $stmt->fetchAll();
}
else if (isset($_POST['checklist'])) {
    $searchTerm = $_POST['checklist'];
    $search = '%' . $searchTerm . '%';

    $stmt = $conn->prepare($sqlBrowse);
    $stmt->bindParam(":findInEquipment", $search);
    $stmt->execute();
    $exercises = $stmt->fetchAll();
}
?>

HTML:

<form action="directory.php" method="post">
   <input type="checkbox" name="checklist[]" class="check" value="Dumbell"> Dumbbell<br>
   <input type="checkbox" name="checklist[]" class="check" value="kettlebell"> Kettlebell<br>

</form>

      <div class="col-sm-10">
          <h1 class="theTitle"> Workout Directory </h1>
            <div class="d-flex flex-wrap">
              <?php
                foreach ($exercises as $exercise) {
               ?>
                <div class="card" id="workoutCard">
                  <div class="card-header"> <?= $exercise->name ?> </div>
                  <div class="card-body">
                      <div class="thumbnail"> </div>
                        <div class="info">
                          <a id="subTitle">Body Parts:</a> <?= $exercise->bodyPart ?><BR>
                          <a id="subTitle">Muscle Group:</a> <?= $exercise->muscleGroup ?><BR>
                          <a id="subTitle">Equipment Used:</a> <?= $exercise->equipment ?>
                        </div>
                  </div>
                </div>
            <?php
            }
            ?>

My search bar works perfectly so I haven't displayed the HTML for it. I'm a beginner so I'm not sure where I've gone wrong, I'm not sure if I can have two PDO execute statements under the same name variable.

Any help or tips will be appreciated

UPDATE: One checkbox will now display all relevant info (without the checklist[]) but it doesn't work for two of them(I believe I need the checklist[] to make the two work) so my new question is, how do I make it so that :

$search = '%' . $searchTerm . '%';

displays more then the one searchTerm

score 0 · Answer 1 · answered Jun 27 '19 at 05:15

0

I assume you want the form to post back when a user checks a check box? If so, add a click event to the check boxes which posts the form, eg

<input type="checkbox" name="checklist[]" class="check" onclick='document.forms[0].submit();' value="Dumbell"> Dumbbell<br>
   <input type="checkbox" name="checklist[]" class="check" onclick='document.forms[0].submit();'  value="kettlebell"> Kettlebell<br>

answered Jun 27 '19 at 05:15

Programnik

1,449
1
9
13

I've just tried to add the onclick but it won't allow me to click multiple checkboxes and when I do only click one nothing happens. – Chan Jun 27 '19 at 05:29
In that case, add a submit button – Programnik Jun 27 '19 at 05:31
Okay I've added it, now I'm getting an error message : Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in /opt/lampp/htdocs/workoutLibrary/directory.php:35 Stack trace: #0 /opt/lampp/htdocs/workoutLibrary/directory.php(35): PDOStatement->execute() #1 {main} thrown in " $stmt->execute(); " AND Array to string conversion in " $search = '%' . $searchTerm . '%'; " – Chan Jun 27 '19 at 05:38
Why do both check boxes have the same name? Give them unique names – Programnik Jun 27 '19 at 05:43
Because in my PHP, I want to select any of the checklist checkboxes e.g. $searchTerm = $_POST['checklist']; So bascially I want to save the value of any check box under the variable searchTerm so that I can use it to search for %kettlebell% AND/OR %Dumbbell% – Chan Jun 27 '19 at 05:50
I fixed my first error with another sql select statement - Just left with that Array to string conversion error – Chan Jun 27 '19 at 05:51
Please see this [link](https://stackoverflow.com/questions/18421988/getting-checkbox-values-on-submit) for solutions to your array to string conversation. Basically the value you get from a checkbox is in an array, but you need a string for your query. – Johanne Jun 27 '19 at 07:18
@Johanne I cannot see the error on my code from looking at that thread – Chan Jun 27 '19 at 07:33
I am going to add an answer, as I do not like, how the code shows up here – Johanne Jun 27 '19 at 07:46

Johanne · Answer 2 · 2019-06-27T08:02:03.630

0

For the array to string conversion error, it is because of the checklist attribute in the form fields.

As you are using a checklist[] (which is an array) in your form, you will have to a loop to get the values:

     else if (isset($_POST['checklist'])) {

     $searchArray = $_POST['checklist'];

     foreach ($searchArray as $searchTerm)
      { 
         $search = '%' . $searchTerm . '%';
         $stmt = $conn->prepare($sqlBrowse);
         $stmt->bindParam(":findInEquipment", $search);
         $stmt->execute();
         $exercises = $stmt->fetchAll();
      }     
}

edited Jun 27 '19 at 08:02

answered Jun 27 '19 at 07:50

Johanne

89
7

Thank you :) I wasn't sure where I should put the foreach statement. I have no errors however, say I click both checkboxes -- it should show me everything under both catergories but it only shows me the info for whatever the last checkbox is.. Would I need to add another foreach around where I have my foreach ($exercises as $exercise) { – Chan Jun 27 '19 at 08:09

SQL Checkbox search which displays list of items that are in my database

2 Answers2