CODEWITHSUNDEEP
phptwitter-bootstrappagination
8yt3c0d3
8yt3c0d3

Reputation: 549

Bootstrap Pagination using PHP & MySQL

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>

Upvotes: 9

Views: 43424

Answers (6)

user6174374
user6174374

Reputation:

For new users...

$ppcompletexxkc = "yes";

$restt = $db->prepare('SELECT COUNT(*) FROM shop WHERE complete = :complete');
$restt->execute(array(':complete' => $ppcompletexxkc 
                               ));
$total = $restt->fetchColumn();



$adjacents = 3;
$targetpage = "category.php"; //your file name
$limit = 1; //how many items to show per page
$page = $_GET['page'];

if($page){ 
$start = ($page - 1) * $limit; //first item to display on this page
}else{
$start = 0;
}

/* Setup page vars for display. */
if ($page == 0) $page = 1; //if no page var is given, default to 1.
$prev = $page - 1; //previous page is current page - 1
$next = $page + 1; //next page is current page + 1
$lastpage = ceil($total/$limit); //lastpage.
$lpm1 = $lastpage - 1; //last page minus 1


$lksmttba = $db->prepare('SELECT * FROM shop WHERE complete = :complete ORDER BY id ASC LIMIT :start ,:limit ');
        $lksmttba->execute(array(':complete' => $ppcompletexxkc,
                              ':start' => $start,
                              ':limit' => $limit 
                               ));



/* CREATE THE PAGINATION */

$pagination = "";
if($lastpage > 1)
{ 
$pagination .= "<div class='pagination1'> <ul>";
if ($page > $counter+1) {
$pagination.= "<li><a href=\"$targetpage?page=$prev\">prev</a></li>"; 
}

if ($lastpage < 7 + ($adjacents * 2)) 
{ 
for ($counter = 1; $counter <= $lastpage; $counter++)
{
if ($counter == $page)
$pagination.= "<li><a href='#' class='active'>$counter</a></li>";
else
$pagination.= "<li><a href=\"$targetpage?page=$counter\">$counter</a></li>"; 
}
}
elseif($lastpage > 5 + ($adjacents * 2)) //enough pages to hide some
{
//close to beginning; only hide later pages
if($page < 1 + ($adjacents * 2)) 
{
for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
{
if ($counter == $page)
$pagination.= "<li><a href='#' class='active'>$counter</a></li>";
else
$pagination.= "<li><a href=\"$targetpage?page=$counter\">$counter</a></li>"; 
}
$pagination.= "<li>...</li>";
$pagination.= "<li><a href=\"$targetpage?page=$lpm1\">$lpm1</a></li>";
$pagination.= "<li><a href=\"$targetpage?page=$lastpage\">$lastpage</a></li>"; 
}
//in middle; hide some front and some back
elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
{
$pagination.= "<li><a href=\"$targetpage?page=1\">1</a></li>";
$pagination.= "<li><a href=\"$targetpage?page=2\">2</a></li>";
$pagination.= "<li>...</li>";
for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
{
if ($counter == $page)
$pagination.= "<li><a href='#' class='active'>$counter</a></li>";
else
$pagination.= "<li><a href=\"$targetpage?page=$counter\">$counter</a></li>"; 
}
$pagination.= "<li>...</li>";
$pagination.= "<li><a href=\"$targetpage?page=$lpm1\">$lpm1</a></li>";
$pagination.= "<li><a href=\"$targetpage?page=$lastpage\">$lastpage</a></li>"; 
}
//close to end; only hide early pages
else
{
$pagination.= "<li><a href=\"$targetpage?page=1\">1</a></li>";
$pagination.= "<li><a href=\"$targetpage?page=2\">2</a></li>";
$pagination.= "<li>...</li>";
for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; 
$counter++)
{
if ($counter == $page)
$pagination.= "<li><a href='#' class='active'>$counter</a></li>";
else
$pagination.= "<li><a href=\"$targetpage?page=$counter\">$counter</a></li>"; 
}
}
}

//next button
if ($page < $counter - 1) 
$pagination.= "<li><a href=\"$targetpage?page=$next\">next</a></li>";
else
$pagination.= "";
$pagination.= "</ul></div>\n"; 
}


echo $pagination;

    while($readpostv=$lksmttba->fetch(PDO::FETCH_ASSOC)){ 

          echo' '.$readpostv['img1'].'';
        }
echo $pagination;

Upvotes: 0

Felipe Rugai
Felipe Rugai

Reputation: 1388

I was facing the same situation, followed an article and adjusted it to my use. Here's what you need for your code.

Tested with Bootstrap v3.3.5.


First of all: You should change your mysql_query() function. This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used. Read more here.

That being said, let's move to our PHP code.

PHP (handles the MySQL queries and generates the pagination HTML)
Note: you can change your target page via $targetpage variable.

//PAGINATION//
$sql = mysqli_query("select * from collection"); 
$total = mysql_num_rows($sql);

$adjacents = 3;
$targetpage = "$_SERVER['PHP_SELF']"; //your file name
$limit = 10; //how many items to show per page
if(isset($_GET['page']))
{
    $page = $_GET['page'];
}else{
    $page = 0;
}

if($page){ 
    $start = ($page - 1) * $limit; //first item to display on this page
}else{
    $start = 0;
}
/* Setup page vars for display. */
    if ($page == 0) $page = 1; //if no page var is given, default to 1.
    $prev = $page - 1; //previous page is current page - 1
    $next = $page + 1; //next page is current page + 1
    $lastpage = ceil($total/$limit); //lastpage.
    $lpm1 = $lastpage - 1; //last page minus 1

$sql2 = "SELECT * FROM collection";
$sql2 .= " order by date limit $start ,$limit ";
$sql_query = mysqli_query($sql2);

/* CREATE THE PAGINATION */

$pagination = "";
if($lastpage > 1)
{ 
    $pagination .= "<ul class='pagination'>";
    if ($page > $counter+1) {
        $pagination.= "<li><a href=\"$targetpage?page=$prev\"><</a></li>"; 
    }

    if ($lastpage < 7 + ($adjacents * 2)) 
    { 
        for ($counter = 1; $counter <= $lastpage; $counter++)
        {
            if ($counter == $page)
                $pagination.= "<li><a href='#' class='active'>$counter</a></li>";
            else
                $pagination.= "<li><a href=\"$targetpage?page=$counter\">$counter</a></li>"; 
        }
    }
    elseif($lastpage > 5 + ($adjacents * 2)) //enough pages to hide some
    {
        //close to beginning; only hide later pages
        if($page < 1 + ($adjacents * 2)) 
        {
            for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
            {
                if ($counter == $page)
                    $pagination.= "<li><a href='#' class='active'>$counter</a></li>";
                else
                    $pagination.= "<li><a href=\"$targetpage?page=$counter\">$counter</a></li>"; 
            }
            $pagination.= "<li>...</li>";
            $pagination.= "<li><a href=\"$targetpage?page=$lpm1\">$lpm1</a></li>";
            $pagination.= "<li><a href=\"$targetpage?page=$lastpage\">$lastpage</a></li>"; 
        }
        //in middle; hide some front and some back
        elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
        {
            $pagination.= "<li><a href=\"$targetpage?page=1\">1</a></li>";
            $pagination.= "<li><a href=\"$targetpage?page=2\">2</a></li>";
            $pagination.= "<li>...</li>";
            for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
            {
                if ($counter == $page)
                    $pagination.= "<li><a href='#' class='active'>$counter</a></li>";
                else
                    $pagination.= "<li><a href=\"$targetpage?page=$counter\">$counter</a></li>"; 
            }
            $pagination.= "<li>...</li>";
            $pagination.= "<li><a href=\"$targetpage?page=$lpm1\">$lpm1</a></li>";
            $pagination.= "<li><a href=\"$targetpage?page=$lastpage\">$lastpage</a></li>"; 
        }
        //close to end; only hide early pages
        else
        {
            $pagination.= "<li><a href=\"$targetpage?page=1\">1</a></li>";
            $pagination.= "<li><a href=\"$targetpage?page=2\">2</a></li>";
            $pagination.= "<li>...</li>";
            for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; 
            $counter++)
            {
                if ($counter == $page)
                    $pagination.= "<li><a href='#' class='active'>$counter</a></li>";
                else
                    $pagination.= "<li><a href=\"$targetpage?page=$counter\">$counter</a></li>"; 
            }
        }
    }

    //next button
    if ($page < $counter - 1) 
        $pagination.= "<li><a href=\"$targetpage?page=$next\">></a></li>";
    else
        $pagination.= "";
    $pagination.= "</ul>\n"; 
}

Now that we have the pagination set up, just call it wherever you want:

HTML

 <div class="row">
     <div class="col-md-12 text-center">
          <?php echo $pagination ?>
     </div>
 </div>


Your <a> elements will receive href=targetPage?page=pageNumber


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

Hope that helps!

Upvotes: 4

Wojciech Grzebieniowski
Wojciech Grzebieniowski

Reputation: 158

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.

Upvotes: 6

njwin07
njwin07

Reputation: 103

I recently did something similar with bootstrap , i used responsive datatables to achieve this. here is the link here

things i got trouble with,

  1. use latest api from google
  2. html , css and js files are provided by the site
  3. they provide pagination and responsivness all that you want
  4. the most important datatables accept data in array format , so when you echo the data from php use jason object and bind all the data in an array and then the rows you want to fill

Upvotes: 2

Ankit Verma
Ankit Verma

Reputation: 136

 $page_no=$_POST['page_no'];//page number  
 $limit=$_POST['limit'];//number of data

 $limit1 = $page_no*$limit; //calculate the limit
 $start = $limit1-$limit; //calculate the start point 
 $sql = "select * from example  limit $start,$limit";// query

use the code it will help

Upvotes: 4

CChoma
CChoma

Reputation: 1154

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.

Upvotes: 5

Related Questions