Comment rechercher et filtrer des données dans un tableau en utilisant PHP ?

Question

Je souhaite rechercher et filtrer des données dans un tableau mais je ne sais pas comment faire car j'apprends actuellement le php. C'est le script php que j'utilise pour rechercher les données

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

?>

Voici ma recherche de champ de saisie

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

Voici mon tableau en php, je souhaite afficher les données que je souhaite rechercher dans ce tableau

<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

Vous souhaiterez peut-être utiliser AJAX pour envoyer la requête au serveur et reconstruire la table en fonction des données renvoyées. Voici un exemple rapide et haché, non encore testé mais qui pourrait fonctionner. L’ensemble de l’examen devrait expliquer ce qui se passe. J'ai apporté diverses corrections mineures au HTML et utilisé le CSS que j'ai référencé dans les commentaires pour centrer le contenu du formulaire.

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