How to search and filter data in a table using PHP?

Question

I want to search and filter data in a table but I don't know how to do it as I am currently learning php. This is the php script I use to search the 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;
}

?>

This is my input field search

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

This is my table in php, I want to display the data I want to search in this table

<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

You may want to use AJAX to send the request to the server and rebuild the table based on the returned data. Here's a quick hashed together example, not tested yet but might work. The entire review should explain what is going on. Made various minor corrections to the HTML and used the css I referenced in the comments to center align the form content.

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