如何使用PHP搜索和筛选表格中的数据？-PHP中文网问答

我想搜索和过滤表中的数据，但我不知道该怎么做，因为我目前正在学习 php。这是我用于搜索数据的 php 脚本

<?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;
}

?>

这是我的输入字段搜索

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

这是我在 php 中的表格，我想在这个表格中显示我想要搜索的数据

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

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