Make a pagination using JQuery after getting data from api.php

Question

Here is my codes-

client.php

<html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<title></title>
<style>
    #show{
        background:red;
    }
</style>

</head>
<body>
<?php 
<div id="show"></div>

<script type="text/javascript" src="jquery.js"></script>
<script type="text/javascript">
    $(document).ready(function() {
        setInterval(function () {
            $('#show').load('api.php')
        });
    });
</script>
</body>
</html>

api.php

<?php 
$conn = new mysqli('localhost', 'root', '', 'ajax01');
if ($conn->connect_error) {
    die("Connection error: " . $conn->connect_error);
}
$result = $conn->query("SELECT name FROM variables");
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo $row['name'] . '<br>';
    }
}
?>

These codes are giving me the results like-

Result of above codes

I am getting the values from database and it is fetching all the data. Therefore, I need a pagination with these value. Need help.

Please explain *I need a pagination with these value.*. What kind of pagination you want? Also, what's the point of using `setInterval()` and `load()` function? Use a simple AJAX request instead. — Rajdeep Paul, Sep 10 '16 at 10:44
`setInterval()` is not an issue `load()`is calling the result of [api.php] I want a pagination that will show only one value and next page will show another. Please check the image attached with this post which is showing two results but I want one and another one in next page. — Hakik Zaman, Sep 10 '16 at 10:50
What do you mean by *next page*? Do you want to fetch the next result in the next call to `load()` function? — Rajdeep Paul, Sep 10 '16 at 10:53
I've given an answer below. Hopefully this is resolve your issue. — Rajdeep Paul, Sep 10 '16 at 13:24

score 2 · Accepted Answer · edited May 23 '17 at 11:51

Based on your comments,

I want a pagination that will show only one value and next page will show another...second result will show after click on next>

There are few things you need to consider here,

Instead of setInterval() and load() functions, simply use an AJAX request to implement your pagination functionality
Use prepared statements because that will help you in preventing SQL injection. Also, read about how you can prevent SQL injection in PHP.

Based on these above points and your below comments, the solution would be like this:

client.php:

<html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<title></title>
<style>
    #show{
        background:red;
    }
</style>

</head>
<body>
<div id="show">
    <?php

        $conn = new mysqli('localhost', 'root', '', 'ajax01');
        if ($conn->connect_error) {
            die("Connection error: " . $conn->connect_error);
        }

        // prepare query statement
        if($stmt = $conn->prepare("SELECT name FROM variables LIMIT 0, 1")){
            // execute statement
            $stmt->execute();

            // bind result variables
            $stmt->bind_result($name);

            // fetch values
            $stmt->fetch();

            // display name and pagination link
            if(isset($name) && !empty($name)){
                echo $name . '<br />';
                ?>
                <div id='link-div' style='background-color:#ffffff'>
                    <a href='' class='showmore' id='1'>Next&nbsp;&nbsp;&raquo;</a>
                </div>
                <?php
            }

            // close statement
            $stmt->close();
        }

    ?>
</div>

<script src="http://code.jquery.com/jquery-1.11.0.min.js"></script> 
<script type="text/javascript">
    $(document).ready(function() {
        $(document).on('click','.showmore',function(event){
            event.preventDefault();
            var offset = $(this).attr('id');

            $.ajax({
                type: 'POST',
                url: 'api.php',
                cache: 'false',
                data: {offset: offset},

                beforeSend: function(){
                    $('#link-div').html('<span>Loading...</span>');
                },

                success: function(data){
                    $('#link-div').remove();
                    $('#show').html(data);
                },

                error: function(jqXHR, textStatus, errorThrown){
                    // error
                }
            });
        });
    });
</script>
</body>
</html>

api.php:

<?php
    if(isset($_POST['offset'])){
        $offset = $_POST['offset'];
        $prev = $offset - 1;  // Previous link in the pagination series
        $next = $offset + 1;  // Next link in the pagination series
        $conn = new mysqli('localhost', 'root', '', 'ajax01');
        if ($conn->connect_error) {
            die("Connection error: " . $conn->connect_error);
        }

        // prepare query statement
        if($stmt = $conn->prepare("SELECT COUNT(name) FROM variables")){
            // execute statement
            $stmt->execute();

            // bind result variables
            $stmt->bind_result($total_rows);

            // fetch values
            $stmt->fetch();

            // close statement
            $stmt->close();
        }

        // prepare query statement
        if($stmt = $conn->prepare("SELECT name FROM variables LIMIT ?, 1")){
            // bind parameter
            $stmt->bind_param('i', $offset);

            // execute statement
            $stmt->execute();

            // bind result variables
            $stmt->bind_result($name);

            // fetch values
            $stmt->fetch();

            // display name and pagination link
            if(isset($name) && !empty($name)){
                echo $name . '<br />';
                ?>
                <div id='link-div' style='background-color:#ffffff'>
                    <?php
                        if($offset > 0){
                            ?>
                            <a href='' class='showmore' id='<?php echo $prev; ?>'>&laquo;Prev&nbsp;&nbsp;</a>
                            <?php
                        }
                        if($offset < $total_rows - 1){
                            ?>
                            <a href='' class='showmore' id='<?php echo $next; ?>'>Next&nbsp;&nbsp;&raquo;</a>
                            <?php
                        }
                    ?>
                </div>
                <?php
            }

            // close statement
            $stmt->close();
        }
    }
?>

+1 but after clicking on `next>` a `` link will appear. But still really thanks for you answer. — Hakik Zaman, Sep 10 '16 at 15:24
@user3311692 I've updated my answer. Please *upvote* and *accept* the answer if it resolved your issue. [How to accept answer on Stack Overflow?](http://meta.stackexchange.com/a/5235) — Rajdeep Paul, Sep 10 '16 at 15:57
"Thanks" again just one more question is it possible to hide `next>` if there is no value to show (such as with second value there is a `next>` link, there is no need of it cause there is no value to show) — Hakik Zaman, Sep 10 '16 at 16:34
@user3311692 I've further updated my answer, now it should work fine for you. — Rajdeep Paul, Sep 10 '16 at 16:55

Make a pagination using JQuery after getting data from api.php

1 Answers1