I am trying to search my landing page record with search filter (In Procedural method rather than PDO or OPP). Its working fine but I am facing one issue that when I open filter page, no record is displaying until I apply search. Where as I want when ever I open search page, all record should be display here even before applying search filter. and when I apply search, then it should filter record. Here is my code
<form method="post" action="search1.php?go" id="searchform">
<input type="text" name="name"> <br>
<input type="text" name="address">
<input type="submit" name="submit" value="Search">
</form>
<?php
if(isset($_POST['submit'])){
if(isset($_GET['go'])){
$name = $_POST['name'];
$address = $_POST['address'];
require_once "config.php";
$sql="SELECT id, name, address, marks FROM student_record Where name = '$name' && address = '$address'";
//-run the query against the mysql query function
$result = mysqli_query($conn, $sql);
//-create while loop and loop through result set
if(mysqli_num_rows($result) > 0){
while($row=mysqli_fetch_array($result)){
$Id =$row['id'];
$Name =$row['name'];
$Address=$row['address'];
$Marks=$row['marks'];
//-display the result of the array
echo "<table class='table table-bordered table-striped table-hover '>";
echo "<thead>";
echo "<tr>";
echo "<th>#</th>";
echo "<th>Name</th>";
echo "<th>Address</th>";
echo "<th>Marks</th>";
echo "</tr>";
echo "</thead>";
echo "<tbody>";
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['address'] . "</td>";
echo "<td>" . $row['marks'] . "</td>";
echo "</tr>";
echo "</tbody>";
echo "</table>";
}
} else {
echo "<p>No matches found</p>";
}
Just to answer your question, you would need to run the query regardless of form submission and the add the WHERE condition when the form is submitted. So using your basic example, the 2 if(isset( conditions would wrap the WHERE query condition. Also note that <table> and <thead> would NOT be in the WHILE loop.
<form method="post" action="search1.php?go" id="searchform">
<input type="text" name="name"> <br>
<input type="text" name="address">
<input type="submit" name="submit" value="Search">
</form>
<?php
require_once "config.php";
$sql="SELECT id, name, address, marks FROM student_record";
if(isset($_POST['submit'])){
if(isset($_GET['go'])){
$name = $_POST['name'];
$address = $_POST['address'];
$sql .= " WHERE name = '$name' && address = '$address'";
}
}
//-run the query against the mysql query function
$result = mysqli_query($conn, $sql);
//-create while loop and loop through result set
if(mysqli_num_rows($result) > 0){
echo "<table class='table table-bordered table-striped table-hover '>";
echo "<thead>";
echo "<tr>";
echo "<th>#</th>";
echo "<th>Name</th>";
echo "<th>Address</th>";
echo "<th>Marks</th>";
echo "</tr>";
echo "</thead>";
echo "<tbody>";
while($row=mysqli_fetch_array($result)){
//-display the result of the array
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['address'] . "</td>";
echo "<td>" . $row['marks'] . "</td>";
echo "</tr>";
}
echo "</tbody>";
echo "</table>";
} else {
echo "<p>No matches found</p>";
}
?>
Please do consider to use prepared statements to query values against the database.
Something also to consider would be Javascript based filtering. If you’re going to pull all the records anyway, you might as well just hold onto them and not send multiple requests to the database for subsets of the same data.
Say that works well. But as there are no longer those IF conditions surrounding where $name and $address are set I would set them like so to avoid undefined index error.
$name = (!empty($_POST['name']) ? $_POST['name'] : '');
$address = (!empty($_POST['address']) ? $_POST['address'] : '');
$sql = "SELECT id, name, address, marks FROM student_record WHERE (name = '$name' OR '$name' = '') AND (address = '$address' OR '$address' = '')";
Just noting this query might harder to convert to prepared statements.
Just noting (if you haven’t noticed) that your original query required Name AND Address to get a match. With this version suggested by Lara, only ONE of those inputs are required, so if there were 2 students with the name itsumarnazir with different addresses they both would be shown if you searched by name only.
i will prefer this version because I can also apply limit in my sql query where as limit is not working in version which you earlier suggested. Although that way look more professional.
<?php
require_once "config.php";
$sql="SELECT id, name, address, marks FROM student_record";
if(isset($_POST['submit'])){
if(isset($_GET['go'])){
$name = $_POST['name'];
$address = $_POST['address'];
$sql="SELECT id, name, address, marks FROM student_record Where name = '$name' || address = '$address' limit 2";
}
}
This query is for OR operator instead of the AND operator. Both have different terms and conditions OR operator return if either one of the conditions is true, On the other side AND operator return true only if both conditions are true it might also cause some unwanted matches. How to avoid this problem If there are duplicate names and addresses in the database?
with name and address one will search one complete row from database with all the columns or selected one in sql query. As we always use primary key, then i dont think so, duplication of name and Address will create any problem
Hi @itsumarnazir, I agree using name and address as criteria filter can help you search one complete row whatever is selected from the database in SQL. But still I would not admire using names and addresses as primary keys because they are not unique; they could have repetitive values. And it will shorten the flexibility of the search filter. For example, what if someone wants to search by some other values like gender, age, grade or something else? Therefore, I suggest using a different approach for creating a dynamic query that can filter data from the database based on multiple criteria entered by the user. One possible approach is to use prepared statements and bind parameters, as suggested by @m_hutley m_hutley. This approach can help you avoid SQL injection attacks, handle different inputs, and use various operators to create complex conditions.
I’m glad you agree with me. Using patient number as the main search key is a good practice, as it is unique and immutable. It can also help you retrieve the relevant data faster and easier. However, you might also want to consider adding some other filter criteria, such as date, diagnosis, or treatment, to make your search more specific and accurate. You can use the same approach as I suggested before, using prepared statements and bind parameters, to create a dynamic query that can handle different inputs.