Pagination and Search

General PHP Help
#1

How do i get a search and pagination onto my page.

Here is my code

<!DOCTYPE html>
<html lang="en">
<head>





    <meta charset="UTF-8">
    <title>Dashboard</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
    <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/umd/popper.min.js"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
    <style>
        .wrapper{
            width: 450px;
            margin: 0 auto;
        }
        table tr td:last-child{
            width: 120px;
        }
    </style>
    <script>
        $(document).ready(function(){
            $('[data-toggle="tooltip"]').tooltip();   
        });
    </script>

<meta name="viewport" content="width=device-width, initial-scale=1.0">

</head>
<body>
    <div class="wrapper">
        <div class="container-fluid">
            <div class="row">
                <div class="col-md-12">
                    <div class="mt-5 mb-3 clearfix">
                        <h2><center>Employees Details</h2>
                        <center><a href="create.php" class="btn btn-success"><i class="fa fa-plus"></i> Add New Employee</a>
                    </div>
                    <?php
                    // Include config file
                    require_once "config.php";
                    
                    // Attempt select query execution
                    $sql = "SELECT * FROM employees";
                    if($result = mysqli_query($link, $sql)){
                        if(mysqli_num_rows($result) > 0){
                            
                                while($row = mysqli_fetch_array($result)){
                                    echo "<tr>";
                                        
                                        echo "Customer Name<br>" . $row['name'] . "<br>";
                                        echo "Address<br>" . $row['address'] . "<br>";
                                        echo "Salary <br>" . $row['salary'] . "<br>";
                                        echo "<td>";
                                            echo '<a href="read.php?id='. $row['id'] .'" class="mr-3" title="View Record" data-toggle="tooltip"><span class="fa fa-eye"></span></a>';
                                            echo '<a href="update.php?id='. $row['id'] .'" class="mr-3" title="Update Record" data-toggle="tooltip"><span class="fa fa-pencil"></span></a>';
                                            echo '<a href="delete.php?id='. $row['id'] .'" title="Delete Record" data-toggle="tooltip"><span class="fa fa-trash"></span></a>';
                                        echo "</td>";
                                    echo "</tr>";
                                }
                                echo "</tbody>";                            
                            echo "</table>";
                            // Free result set
                            mysqli_free_result($result);
                        } else{
                            echo '<div class="alert alert-danger"><em>No records were found.</em></div>';
                        }
                    } else{
                        echo "Oops! Something went wrong. Please try again later.";
                    }
 
                    // Close connection
                    mysqli_close($link);
                    ?>
                </div>
            </div>        
        </div>
    </div>
</body>
</html>
#2

Well, here are some basic ideas to get you started. First, you need to set the number of rows of data you are wanting on a page. Let’s say 20. Next, you need to create a way for the user to select what to search for. Let’s say you want to search based on salary, you would need an input field so the user could enter that amount. Once a query is created from the user inputs, you need to have a way to move forward and backward in the pagination process.

In the search section, you can input data such as address and search using the LIKE command.
Here is an example.
SELECT * FROM employees WHERE address LIKE “%Washington Street%”;
You would need to replace the street, city, state, country into the LIKE section.

For pagination, you would need a starting point and a limit. For example:
SELECT * FROM employees LIMIT 20 OFFSET 20;
This would take the next 20 items starting at the 20th one. You would need to keep track of the current page number and the number of items per page. Let’s call them $page and $count for an example.
SELECT * FROM employees LIMIT $count OFFSET $page*$count;
Note that this is just an example as the page times count is not that simple, just a first example.

It is fairly easy to create the page number times the max per page count and place that into a button.
You can create previous and next buttons with those numbers already in place. Then, when the user selects the next page or the previous one, just read the value and use it in your query.

Well, there are some starting points for you to think about. Experiment with these and show us updated code when you start making progress or if you get stuck. Good luck.

#3

I have a GitHub repository that uses pagination that might also help → https://github.com/Strider64/Miniature01282021

#4

Before you start, you need to organize your code to make writing, testing and debugging easier. In general, your code should be laid out in this order -

  1. Initialization
  2. Post method form processing - not used in this application
  3. Get method business logic, that knows how to get/produce the data needed for the dynamic portions of the page - most of the php logic needed for this application will go here. This code will store its result/output in appropriately named php variables, which you can echo/print_r/var_dump for debugging purposes.
  4. Html document. This should only contain simple php logic to test and use the result from the above sections of code OR just use an existing templating system.

For searching, you would use a get method form to let you enter/select what to search for. The search form would be ‘sticky’ so that the user can modify an existing search, without needing to keep re-entering/selecting values. The search logic is independent from the pagination logic, i.e. if a new search is entered, pagination starts over at the first page. The reverse is not true. Pagination links must include any existing get parameters so that the search values will ‘automatically’ carry over between pagination pages.

Pagination involves two queries. The first one is to get the total count of matching rows. This is used to calculate the total number of pages. This is then used to both limit the requested page number and to control the production of pagination links. The second query is to retrieve the logical page of data. Both of these queries must have the same FROM, any JOIN, any WHERE, and any HAVING terms. To avoid repetition and mistakes, build this common part of the query in a php variable, then just use this variable when building each query. The first query would use SELECT COUNT(*), followed by the common part of the query. The second query would SELECT the list of columns you want, followed by the common part of the query, followed by any ORDER BY term (most data retrieval queries should have an order by term to get the data into a specific order for display), followed by a LIMIT term. Use a prepared query when supplying any external, unknown, dynamic values to the query when it gets executed, such as the search values, limit values, …

Sponsor our Newsletter | Privacy Policy | Terms of Service