Bootstrap Pagination using PHP & MySQL

Question

I am kind of new to Bootstrap, I am trying to implement pagination on one of the sections in my page to represent the data properly. Can anyone please help?

Here is a snapshot of how the code looks right now. How can I implement pagination so that only 10 records are displayed? Thanks.

<section class="success" id="all-confessions">
    <div class="container">
        <div class="row">
            <div class="col-lg-12 text-center">
                <h2>All Confessions</h2>
                <hr class="star-light">
            </div>
        </div>
        <div class="row">
            <div class="row text-left">
                <?php
                $allconfession = mysql_query("SELECT * FROM collection ORDER BY date DESC");

                while($result2 = mysql_fetch_array($allconfession)) {
                    $id = $result2['id'];
                    ?>
                    <div class="col-md-3 col-md-offset-1">
                        <h5>#<?php echo $id; ?></h5>
                    </div>
                    <div class="col-md-10 col-md-offset-1">
                        <p class="para-confess">
                            <?php
                            echo $result2['type'] ." from ". $result2['college'] ." of ". $result2['department'] ." confessed ". $result2['confession']; 
                            ?>
                        </p>
                        <div class="text-left">
                            <?php
                            if(isset($_COOKIE['uname'])) {
                                ?>
                                <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
                                    <input type="text" name="cid" style="display: none;" value="<?php echo $id; ?>">
                                    <button type="submit" class="btn btn-success fa fa-thumbs-up" name="like"> Cool</button>
                                    <button type="submit" class="btn btn-warning fa fa-thumbs-down" name="dislike"> WTF</button>
                                </form>
                                <?php
                            }
                            ?>
                        </div>
                        <div class="text-right">
                            <i class="fa fa-thumbs-o-up"> 
                                <?php 
                                $likes = mysql_query("SELECT COUNT(*) FROM activity WHERE cid = $id AND ld = 1");
                                $alikes = mysql_fetch_row($likes);
                                echo $alikes[0]; 
                                ?> 
                            </i> &nbsp;
                            <i class="fa fa-thumbs-o-down"> 
                                <?php 
                                $dislikes = mysql_query("SELECT COUNT(*) FROM activity WHERE cid = $id AND ld = 0");
                                $adislikes = mysql_fetch_row($dislikes);
                                echo $adislikes[0]; 
                                ?> 
                            </i>
                        </div>
                        <hr/>
                    </div>
                    <?php
                }
                ?>
            </div>
        </div>
    </div>
</section>

Answer 1

Firstly, please learn something about PDO http://php.net/manual/en/book.pdo.php . In my solution i assume you're using PDO.

First thing you need to do is determine how many rows there actually is in DB.

$nbOfResults = $pdo->query('select count(*) from collection')->fetchColumn();

Then set some limit of entities per page.

$entitiesPerPage = 10; 

Now let's determine how many pages there should be. First I'll divide number of results by entitiesPerPage. Let's say there're 202 results. Dividing it by 10 ( entities per page ) will result with 20 pages ( casted to int ). However there're still 2 entities left. That's why i have to check modulo and add one more page if needed.

$nbOfPages = intval($nbOfResults / $entitiesPerPage);

if( ($entitiesPerPage % $nbOfResults) !== 0 ) {
    $nbOfPages += 1
}

Now we're ready to build a pagination. But first we need to have a variable that holds current page.

$currentPage = $_GET['page']; 

Or in more elegant way.

$currentPage = filter_input(INPUT_GET, 'page', FILTER_SANITIZE_NUMBER_INT);

However, if there isn't any page let's assume we're on the first page.

if(!$currentPage) { $currentPage = 1 }

Ok, now it's time for an array holding pagination info.

$pagination = [];

if ($currentPage !== 1) {
    $pagination[] = [
        'page' => 'Previous',
        'link' => '?page=' . ($currentPage - 1),
        'active' => false,
    ];
}

for($i = 1; $i <= $nbOfPages; $i++) {
   $pagination[] = [
      'page' => $i,
      'link' => '?page=' . $i,
      'active' => ( $i === $currentPage ),
   ];
}

if ($currentPage !== $nbOfPages) {
    $pagination[] = [
        'page' => 'Next',
        'link' => '?page=' . ($currentPage + 1),
        'active' => false,
    ];
}

And finally the query to get the results on the current Page.

$query = 'SELECT * FROM collection ORDER BY date DESC LIMIT ? OFFSET ?';
$sth = $dbh->prepare($query);
$sth->execute(array($entitiesPerPage, ( $currentPage - 1 ) * $entitiesPerPage)));

Now all you have to do is loop through the $pagination variable and print proper bootstrap HTML.

Answer 2

You're pretty far off.

At the very minimum, you need to do the following.

1. You need to create a variable that determines the number of items to show per page.
2. You need to take that variable and multiply it by the page number to get the number of records to offset in your query.
3. You need to offset the query results using the number you got from the calculation above and also limit the query to the number of items to show.
4. You need to count the number of total items and divide it by the number of items per page to get the total number of pages to show in the pagination.

Here is some simple pagination code that I've used many times in conjunction with Bootstrap.

http://www.a2zwebhelp.com/php-mysql-pagination

Just omit the styles and apply Bootstrap classes to the elements. But you cannot just add static html and expect it to work without any kind of backend logic to it. Bootstrap only provides a way to style the pagination, but you have to build it.