如何进一步过滤搜索步骤的结果？

Question

I have a table student_tb that stores student_name and class_name

student_id | student_name | class_name
   1       |       John   |     1
   2       |       Herry  |     2
   3       |       Peter  |     1
   4       |       Tom    |     2

I created a form to search student name in the database as follows:

<form style="text-align: center;" method='POST'>
    <input type="text"   name='input_text' id='input_text' placeholder="Input text to search" required>   
     <button type="submit" name='search' value="search" ">Search</button>
</form> 

<div class="container" style="text-align: center;">
    <?php 
    if(isset($_POST['search'])){
    $sql  = "SELECT * FROM student_tb WHERE student_name LIKE '%".$_POST['input_text']."%' "; 
    $result = mysqli_query($conn, $sql);
    while($row = mysqli_fetch_assoc($result)){?>
        <ul>
          <li><h3><?php echo $row["student_name"] . $row["class_name"]; ?></h3></li>            
        </ul>
     <?php }}?>
</div>

Assume that, I will add two check boxes which are used to further filter the result from searching step. Could you guide me how to implement it when I click the check box? For example, I type 'T', the searching result is Peter 1 and Tom 2. After that, I click the check box class 1, the result only remains Peter 1. Thank you so much

<div id="filter" style="text-align: center;">
<input type="checkbox" id="class1" name="class1" value="class1">
    <label for="class1">Class 1</label>
<input type="checkbox" id="class2" name="class2">
    <label for="class2">Class2</label>
</div>

Answer 1

Simply define these checkboxes as array (name="class[]") , give them a values equal to your class_name IDs and check them while building your SQL query:

<form style="text-align: center;" method='POST'>
    <input type="text" name='input_text' id='input_text' placeholder="Input text to search" required>
    <div id="filter" style="text-align: center;">
        <input type="checkbox" id="class1" name="class[]" value="1">
        <label for="class1">Class 1</label>
        <input type="checkbox" id="class2" name="class[]" value="2">
        <label for="class2">Class2</label>
    </div>
    <button type="submit" name="search" value="search">Search</button>
</form>

<div class="container" style="text-align: center;">
    <?php
        if (isset($_POST['search'])) {
            dump($_POST);
            $sql = "SELECT * FROM student_tb WHERE student_name LIKE '%" . $_POST['input_text'] . "%' ";
            if(isset($_POST['class'])) {
                foreach ($_POST['class'] AS $k => $v) {
                    if ($k == 0) {
                        $sql .= " AND (class_name = " . $v;
                    } else {
                        $sql .= " OR class_name = " . $v;
                    }
                }
                $sql .= ")";
            }
            $result = mysqli_query($conn, $sql) or die(mysqli_error($conn));
            while ($row = mysqli_fetch_assoc($result)) {
                ?>
                <ul>
                    <li><h3><?php echo $row["student_name"] . $row["class_name"]; ?></h3></li>
                </ul>
            <?php }
        } ?>
</div>

Built SQL query:

SELECT
    *
FROM
    student_tb
WHERE
    student_name LIKE '%T%'
    AND (class_name = 1 OR class_name = 2)

Note that if you send more than one checkbox, you must combine them with OR clause in order to filter data properly.

UPDATE: Here is the script (HTML & PHP) with ability to:

• search by string;

• to apply checkbox filter with automatic submit;

• to keep all values after submit;

• to reset form;

---

<form name="searchForm" method="post">
    <input type="hidden" name="formSubmit" value="1">
    <input
        type="text"
        name='input_text'
        id='input_text'
        placeholder="Input text to search"
        required
        value="<?php if (isset($_POST['input_text'])) echo $_POST['input_text'] ?>">
    <div id="filter">
        <input
            type="checkbox"
            id="class1"
            name="class[0]"
            value="1"
            onclick="this.form.submit()" <?php if (isset($_POST['class'][0])) echo $_POST['class'][0] ? 'checked' : ''; ?>>
        <label for="class1">Class 1</label>
        <input
            type="checkbox"
            id="class2"
            name="class[1]"
            value="2"
            onclick="this.form.submit()" <?php if (isset($_POST['class'][1])) echo $_POST['class'][1] ? 'checked' : ''; ?>>
        <label for="class2">Class2</label>
    </div>
    <button type="submit" name="search" value="search">Search</button>
    <button type="reset" name="reset" value="reset" onclick="window.location.href = '<?php echo $_SERVER['PHP_SELF']; ?>'">Reset</button>
</form>

<div class="container">
    <?php
        if (isset($_POST['formSubmit'])) {
            dump($_POST);
            $sql = "SELECT * FROM student_tb WHERE student_name LIKE '%" . $_POST['input_text'] . "%' ";
            if (isset($_POST['class'])) {
                $c = 0;
                $sql .= " AND (";
                    foreach ($_POST['class'] AS $k => $v) {
                        if($c == 0) $sql .= "class_name = " . $v;
                        else        $sql .= " OR class_name = " . $v;
                        $c++;
                    }
                $sql .= ")";
            }
            $result = mysqli_query($conn, $sql) or die(mysqli_error($conn));
            while ($row = mysqli_fetch_assoc($result)) {
                ?>
                <ul>
                    <li><h3><?php echo $row["student_name"] . $row["class_name"]; ?></h3></li>
                </ul>
            <?php }
        } ?>
</div>

The change is a bit static and can be improved for sure, but you will get the idea.