How to combine search text and checkboxes using sql bootstrap php

Question

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!

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Dec 20 '20 at 15:48

ffx14 · Accepted Answer · 2020-12-20T15:27:49.430

-1

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);

edited Dec 20 '20 at 15:27

answered Dec 20 '20 at 15:22

ffx14

1,825
2
14
19

1

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman Dec 20 '20 at 15:48
fbuchlak, thank you for your answer! @Dharman, thank you for your warning. As I'm a beginner in sql, I am still not familiar with sql injection-save programming. I've taken a look at the links above but I still don't understand how I could make the codes above safe. I guess I also need more time for this important topic. – Fish Dec 20 '20 at 16:31
@Fish Please read https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement and if you are new to PHP I really would recommend switching over to PDO which is much easier than mysqli. Start learning it here https://phpdelusions.net/pdo – Dharman Dec 20 '20 at 16:32
@Dharman, thanks a lot! PDO looks very interesting! – Fish Dec 20 '20 at 16:48

How to combine search text and checkboxes using sql bootstrap php

1 Answers1