i want to search and filter the data in my table but i dont know how to do it im currently studying php. this is my php script to search 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;
}
?>
and 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 and i want to show in this table the data i want to search
<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>
CodePudding user response：
what is not working is this condition in line 3
isset($_POST['search'])
what you can do instead,
isset($_POST['query'])
do note isset() will treat empty string as true. use !empty() instead.
CodePudding user response：
You likely would want to use AJAX to send the request to the server and rebuild the table based upon the returned data. The following is a quickly hashed together example that has not been tested but that might be of use. The comments throughout should illustrate what is happening. Various minor corrections to the HTML were made and the css I referenced in a comment was used to align the form contents centrally.
<?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>