首页  >  问答  >  正文

如何使用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>

P粉287726308P粉287726308318 天前491

全部回复(1)我来回复

  • P粉300541798

    P粉3005417982023-12-30 12:42:58

    您可能希望使用 AJAX 将请求发送到服务器并根据返回的数据重建表。以下是一个快速散列在一起的示例,尚未经过测试,但可能有用。整个评论应该说明正在发生的事情。对 HTML 进行了各种细微的更正,并使用我在评论中引用的 css 来居中对齐表单内容。

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

    回复
    0
  • 取消回复