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>
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.