Wie suche und filtere ich Daten in einer Tabelle mit PHP?

Question

Ich möchte Daten in einer Tabelle suchen und filtern, weiß aber nicht, wie das geht, da ich gerade PHP lerne. Dies ist das PHP-Skript, das ich zum Durchsuchen der Daten verwende

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

?>

Das ist meine Eingabefeldsuche

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

Dies ist meine Tabelle in PHP, ich möchte die Daten anzeigen, die ich in dieser Tabelle durchsuchen möchte

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

Answer 1

您可能希望使用 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>