user3311692 user3311692 - 2 months ago 10
Javascript Question

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

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.

Answer

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();
        }
    }
?>
Comments