Bagaimana untuk mencari dan menapis data dalam jadual menggunakan PHP?

Question

Saya ingin mencari dan menapis data dalam jadual tetapi saya tidak tahu bagaimana untuk melakukannya kerana saya sedang belajar php. Ini adalah skrip php yang saya gunakan untuk mencari 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;
}

?>

Ini adalah carian medan input saya

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

Ini adalah jadual saya dalam php, saya ingin memaparkan data yang saya ingin cari dalam jadual ini

<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

Anda mungkin mahu menggunakan AJAX untuk menghantar permintaan kepada pelayan dan membina semula jadual berdasarkan data yang dikembalikan. Berikut ialah contoh cincang bersama-sama, belum diuji lagi tetapi mungkin berfungsi. Keseluruhan ulasan harus menjelaskan apa yang sedang berlaku. Membuat pelbagai pembetulan kecil pada HTML dan menggunakan css yang saya rujuk dalam ulasan untuk menyelaraskan kandungan borang di tengah-tengah.

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