how can i search and filter the data in table using php?

Question

i want to search and filter the data in my table but i dont know how to do it im currently studying php. this is my php script to search 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;
}

?>

and 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 and i want to show in this table the data i want to search

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

you can use jquery datatables to search data from table. https://datatables.net/ — Bhavik, Dec 15 '22 at 12:45
fyi: The `table` within the form is overkill - the effect of aligning the contents centrally could be better accomplished using some basic css ( flexbox for instance ) — Professor Abronsius, Dec 15 '22 at 13:21
Your code is vulnerable to SQL injection - use `prepared statements` rather than embedding user supplied data directly in the sql. — Professor Abronsius, Dec 15 '22 at 13:23
Also - your existing markup is slightly incorrect. You have 2 closing `` elements next to one another ( edit ) and no closing `` after the `delete` link — Professor Abronsius, Dec 15 '22 at 15:55
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) — Dharman, Dec 15 '22 at 18:20

score 0 · Answer 1 · answered Dec 15 '22 at 16:36

You likely would want to use AJAX to send the request to the server and rebuild the table based upon the returned data. The following is a quickly hashed together example that has not been tested but that might be of use. The comments throughout should illustrate what is happening. Various minor corrections to the HTML were made and the css I referenced in a comment was used to align the form contents centrally.

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

score -1 · Answer 2 · answered Dec 15 '22 at 15:52

-1

what is not working is this condition in line 3

isset($_POST['search'])

what you can do instead,

isset($_POST['query'])

do note isset() will treat empty string as true. use !empty() instead.

answered Dec 15 '22 at 15:52

Nur'Azim Zahurin

34
6

how can i search and filter the data in table using php?

2 Answers2