How to combine search text and checkboxes using sql bootstrap php

What I have so far: I’m working on a database based website. So far, I have a search-text-form with bootstrap and a submit button in index.php. In search.php, I get an output according to the word I typed into the search-text-form.

The Literature_table in the background looks like this:

| ID | Author | Genre   | Title |
| -- | ------ | --------|-------|
| 1  | Smith  | romance | aaaaa |
| 2  | Lee    | comedy  | bbbbb |
| 3  | Baker  | comedy  | ccccc |

I can currently search for the Author name and title in the search-text-form.

What I want to do: I want to add checkboxes under the search-text-box, where I can choose the genre. The genre is stored as a ‘set’ in the database. Now how can I add those checkboxes to the search-box-form I already have? I only want one single submit button and checking the checkboxes is optional for the search.

Codes

index.php

<form class="form-horizontal" action="search.php">
<fieldset>

  <div class="form-group">
    <label class="col-md-4 control-label" for="keyword">Free text</label>
    <div class="col-md-4">
      <input id="keyword" name="keyword" type="search" class="form-control input-md">
    </div>
  </div>

  <div class="form-group">
    <label class="col-md-3 control-label" for="checkboxes_Genre ">Genre</label>
    <div class="col-md-8">
      <label class="checkbox-inline" for="checkboxes_Genre -0">
        <input type="checkbox" name="checkboxes_Genre " id="checkboxes_Genre -0" value="comedy">
        comedy
      </label>
      <label class="checkbox-inline" for="checkboxes_romance -1">
        <input type="checkbox" name="checkboxes_romance" id="checkboxes_romance -1" value="romance">
        romance
      </label>
    </div>
  </div>

  <div class="form-group">
    <label class="col-md-3 control-label" for="submit"></label>
    <div class="col-md-4">
      <button id="submit" name="submit" class="btn btn-primary">Submit</button>
    </div>
  </div>

  </fieldset>
  </form>

and search.php:

$keywordfromform = $_GET["keyword"];
$sql = "SELECT * FROM Literature_table
        WHERE CONCAT_WS('', Author, Title)
        LIKE '%" . $keywordfromform . "%' ";
$result = $mysqli->query($sql);

I experimented with several codes but nothing works. I also tried to resolve this by adding an ‘AND’-condition without results. I’m new to sql etc. in general and would appreciate your help!

Answer

You should store values from form in array. You need to change input name.

<div class="form-group">
    <label class="col-md-3 control-label" for="checkboxes_Genre ">Genre</label>
        <div class="col-md-8">
            <!-- use "checkboxes[]" as name for array -->
            <label class="checkbox-inline" for="checkboxes_Genre0">
                <input type="checkbox" name="checkboxes[]" id="checkboxes_Genre0" value="comedy"> 
                comedy
            </label>
            <label class="checkbox-inline" for="checkboxes_Genre1">
                <input type="checkbox" name="checkboxes[]" id="checkboxes_Genre1" value="romance"> 
                romance
            </label>
        </label>
    </div>
</div>

For select, you need to use “IN” operator.

$keywordfromform = $_GET["keyword"];
$genres = $_GET['checkboxes'] ?? [];
$inOrTrueStatement = !empty($genres) ? "Genre IN ('" . implode("', '", $genres) . "')" : "TRUE";

// SELECT * FROM Literature_table WHERE CONCAT_WS('', Author, Title) LIKE 'keyword' AND Genre IN ('value1', 'value2', 'value3')
$sql = "SELECT * FROM Literature_table
        WHERE CONCAT_WS('', Author, Title)
        LIKE '%" . $keywordfromform . "%' 
        AND " . $inOrTrueStatement; // Genre IN ('xyz') or TRUE (if no genre is selected)

$result = $mysqli->query($sql);

Advertisement

Answer