I want to search and filter data in a table but I don't know how to do it as I am currently learning php. This is the php script I use to search the data
<?php
require('./conn.php');
if (isset($_POST['search'])) {
$valueToSearch = $_POST['query'];
// search in all table columns
// using concat mysql <function></function>
$query = "SELECT * FROM `user_2` WHERE CONCAT(`firstname`, `lastname`) LIKE '%" . $valueToSearch . "%'";
$search_result = filterTable($query);
} else {
$query = "SELECT * FROM `user_2`";
$search_result = filterTable($query);
}
// function to connect and execute the query
function filterTable($query)
{
$connect = mysqli_connect("localhost", "root", "", "info");
$filter_Result = mysqli_query($connect, $query);
return $filter_Result;
}
?>
This is my input field search
<form action="index.php" method="post" enctype="multipart/data-form">
<table align="center">
<tr>
<td>
Search: <input type="text" name="query"> <input type="submit" value="search" name="search">
</td>
</tr>
</table>
</form>
This is my table in php, I want to display the data I want to search in this table
<table align="center" border="5" cellspacing="0" width="500">
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Update</th>
<th>Delete</th>
</tr>
<?php
$sql = "SELECT * FROM user_2";
$stmt = $conn->prepare($sql);
$stmt->execute();
foreach ($stmt as $result) : ?>
<tr>
<td align="center"><?php echo $result['firstname'] ?></td>
<td align="center"><?php echo $result['lastname'] ?></td>
<td align="center">
<a href="./edit.php?user2_id=<?php echo $result['user2_id'] ?>">Edit</a>
</a>
</td>
<td align="center"> <a href="./delete.php?user2_id=<?php echo $result['user2_id'] ?>" onclick="return confirm('Are you sure you want to delete this user?')">
Delete</td>
</tr>
<?php endforeach; ?>
</table>
P粉3005417982023-12-30 12:42:58
You may want to use AJAX to send the request to the server and rebuild the table based on the returned data. Here's a quick hashed together example, not tested yet but might work. The entire review should explain what is going on. Made various minor corrections to the HTML and used the css I referenced in the comments to center align the form content.
<?php
#require 'conn.php';
/*
For handling the AJAX request that filters the table according
to the data entered into the text field by the user.
*/
if( $_SERVER['REQUEST_METHOD']=='POST' && isset( $_POST['query'] ) ){
ob_clean();
/*
it is very important that you mitigate SQL injection
so always use a prepared statement.
The sql has a placeholder for the like clause parameter.
Bind the placeholder as a string type using a variable of your choice.
Create that variable with the %word% style syntax
Execute the statement and bind th results - it is easier & better to name the columns explicitly rather than ALL.
*/
$sql='select `firstname`,`lastname`,`user2_id` from `user_2` where concat( `firstname`, `lastname` ) like ? ';
$stmt=$connect->prepare( $sql );
$stmt->bind_param('s',$query);
$query='%' . $_POST['query'] . '%';
$stmt->execute();
$stmt->store_result();
$stmt->bind_result( $firstname, $lastname, $user2_id );
/*
process the recordset and print out new HTML
which will be used by the ajax callback to
rebuild the table display.
*/
header('Content-Type: text/html');
while( $stmt->fetch() ){
printf('
<tr>
<td>%1$s</td>
<td>%2$s</td>
<td><a href="./edit.php?user2_id=%3$s">Edit</a></td>
<td><a href="./delete.php?user2_id=%3$s">Delete</a></td>
</tr>',
$firstname,
$lastname,
$user2_id
);
}
exit();
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Document</title>
<style>
form{
width:500px;
float:none;
margin:auto;
border:1px solid grey;
padding:1rem;
box-sizing:border-box;
display:flex;
flex-direction:row;
justify-content:center;
align-content:center;
}
input{
margin:auto 0.1rem;
padding:0.5rem;
}
table{
width:500px;
margin:1rem auto;
float:none;
border:1px solid grey;
border-collapse:none;
}
tr{
margin:0.5rem;
}
td{
border:1px dotted grey;
margin:0.25rem;
padding:0.5rem;
text-align:center;
}
</style>
</head>
<body>
<form method='post' name='search'>
<label>Search: <input type='text' name='query' /></label>
<input type='submit' value='search' name='search' />
</form>
<table id='users'>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Update</th>
<th>Delete</th>
</tr>
<tbody>
<?php
$sql = "SELECT * FROM user_2";
$stmt = $conn->prepare( $sql );
$stmt->execute();
foreach( $stmt as $result ) :
?>
<tr>
<td align="center"><?php echo $result['firstname'] ?></td>
<td align="center"><?php echo $result['lastname'] ?></td>
<td align="center">
<a href="./edit.php?user2_id=<?php echo $result['user2_id'] ?>">Edit</a>
</td>
<td align="center">
<a href="./delete.php?user2_id=<?php echo $result['user2_id'] ?>" onclick="return confirm('Are you sure you want to delete this user?')">Delete</a>
</td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<script>
/*
create a simple event handler that fires off an ajax
request and rebuilds the displayed HTML with the returned
data.
*/
document.querySelector('input[type="submit"]').addEventListener('click',e=>{
e.preventDefault();
let fd=new FormData( document.forms.search );
fetch( location.href,{ method:'post', body:fd } )
.then(r=>r.text())
.then(html=>{
document.querySelector('table#users tbody').innerHTML=html
})
});
</script>
</body>
</html>