user3051460 user3051460 - 4 months ago 13
PHP Question

How to further filter the result from searching step?

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

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.