Count number of rows returned in Mysql with LIMIT Clause

Knowledge of total number of rows returned from a MySql Query is very important for pagination purpose.

But it becomes difficult to count total number of rows with "LIMIT" clause. Solution to this problem is to use "SQL_CALC_FOUND_ROWS" function which helps in getting results quickly.

Today i will show you, how to use "SQL_CALC_FOUND_ROWS" function. below is a sample code, you can see the query structure

    $query = mysql_query("select SQL_CALC_FOUND_ROWS id from book  LIMIT 0,10");
    $rows = mysql_query("SELECT FOUND_ROWS() AS `found_rows`");

Now check the result as follows

    while($res1 = mysql_fetch_assoc($query))
    {
     echo $res1['id'].'<br/>'; 
    }
    
    print_r(mysql_fetch_assoc($rows));
    

The result,

51
52
53
54
55
56
57
58
59
60
Array
(
    [found_rows] => 67
)

SQL_CALC_FOUND_ROWS and Problem with Codeigniter

"SQL_CALC_FOUND_ROWS" function does not work in codeigniter. So total number of rows returned can be counted as follows.
Here i am using codeigniter's "Active Record Caching" to avoid writing same code twice.

$this->db->start_cache();
    $this->db->select('b.*, u.username');
    $this->db->from('book b');
    $this->db->join('users u', 'b.userid = u.id');
    $this->db->where('b.cat', 'science');
$this->db->stop_cache();

$totalRows = $this->db->count_all_results();


$this->db->limit(20);

$query = $this->db->get();
$data->books = $query->result();

$this->db->flush_cache();

echo $totalRows;

Read More

building simple php pagination script in 10 steps

Pagination is simply a process of dividing your output contents in small modules and displaying them on different pages.
For example,you can see Google search results where 10 to 15 search results are shown per page.
Pagination make output more user friendly.

Process for creating pagination script in 10 steps:

Today i'll develop pagination script in 10 easy steps.So let's get started, first of all Create two php files, 

1.  pagination.php (for connection with Database and calculation) 
2.  index.php        (for pagination display)

Note: I will be using 'pagination' as database name and 'data' as table name, change it according to your database and table name. 

Step 1: Open Pagination.php file in any of your favourite text editor and connect to database

<!-------------------------pagination.php --------->
<?php
 // connect to mysql//
//step:1

$con = mysql_connect("localhost","root",""); // Enter hostname,user,password 
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
  // select database
  mysql_select_db("pagination", $con); //Provide database name which has our data for pagination.
  

Step 2: Count number of rows in table

//   count total number of rows from the desired table in the database  //
//step:2
    
  $query = mysql_query("SELECT * FROM data"); //Counting total number of rows in the table 'data',
  $total_rows = mysql_num_rows($query);

Step 3: Setup configuration

// setup configuration//  
//step:3

  $base_url = 'https://localhost/pagi/';    //Provide location of you index file  
  $per_page = 10;                           //number of results to shown per page 
  $num_links = 8;                           // how many links you want to show
  $total_rows = $total_rows; 
  $cur_page = 1;                           // set default current page to 1

Step 4: Extract information from url,when request made through pagination links

//now we will extract information from url//
//step:4
    if(isset($_GET['page']))
    {
      $cur_page = $_GET['page'];
      $cur_page = ($cur_page < 1)? 1 : $cur_page;            //if page no. in url is less then 1 or -ve
    }

Step 5: Calculate limit and offset for Querying Table

// calculate limit and offset, it'll will be used for Sql Query//
//step:5
    $offset = ($cur_page-1)*$per_page;                //setting offset
   
    $pages = ceil($total_rows/$per_page);              // no of page to be created

Step 6: Calculating the start and end page numbers for pagination links

//Calculate the start and end page numbers for pagination links//
//step:6

    $start = (($cur_page - $num_links) > 0) ? ($cur_page - ($num_links - 1)) : 1;
    $end   = (($cur_page + $num_links) < $pages) ? ($cur_page + $num_links) : $pages;

Step 7: Query the Database for displaying only 10 ['$per_page'] item per page.

//query the database with calculated OFFSET //
//step:7
    $res = mysql_query("SELECT * FROM data LIMIT ".$per_page." OFFSET ".$offset);

 mysql_close($con);
 
 // pagination.php completed // 
?>

Till now we have completed our work on pagination.php next we will work on index.php, As follows.

Step 8: Display the results in index.php

In this example table 'data' has id,title and content field , so I am going to display them as follows.

<!-- Now We are in index.php ---->
<!DOCTYPE html>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html lang="eng">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
        <title>Pagination</title>

    </head>
    <body>    
    <?php
 
    include("pagination.php"); // DON'T FORGET THIS

     //        display the results //
     //step:8
     
        if(isset($res))// results from Step 7
        {
            //creating table
            echo '<table style="width:600px; cell-padding:4px; cell-spacing:0; margin:auto;">';           echo'<th>id</th><th>title</th><th>content</th></tr>'; 
            while($result = mysql_fetch_assoc($res))
            {
              echo '<tr>';
              echo '<td>'.$result['id'].'</td>'.'<td>'.$result['title'].'</td>'.'<td>'.$result['content'].'</td>' ;
              echo '</tr>';
            }
            echo '</table>';
        }
    ?>

Step 9: Display Pagination Page Numbers

<!--//display pagination page numbers //
    //step:9 
    -->
    <div id="pagination">
        <div id="pagiCount">
            <?php
                if(isset($pages))
                {  
                    if($pages > 1)        
                    {    if($cur_page > $num_links)     // for taking to page 1 //
                        {   $dir = "first";
                            echo '<span id="prev"> <a href="'.$_SERVER['PHP_SELF'].'?page='.(1).'">'.$dir.'</a> </span>';
                        }
                       if($cur_page > 1) 
                        {
                            $dir = "prev";
                            echo '<span id="prev"> <a href="'.$_SERVER['PHP_SELF'].'?page='.($cur_page-1).'">'.$dir.'</a> </span>';
                        }                 
                        
                        for($x=$start ; $x<=$end ;$x++)
                        {
                            
                            echo ($x == $cur_page) ? '<strong>'.$x.'</strong> ':'<a href="'.$_SERVER['PHP_SELF'].'?page='.$x.'">'.$x.'</a> ';
                        }
                        if($cur_page < $pages )
                        {   $dir = "next";
                            echo '<span id="next"> <a href="'.$_SERVER['PHP_SELF'].'?page='.($cur_page+1).'">'.$dir.'</a> </span>';
                        }
                        if($cur_page < ($pages-$num_links) )
                        {   $dir = "last";
                       
                            echo '<a href="'.$_SERVER['PHP_SELF'].'?page='.$pages.'">'.$dir.'</a> '; 
                        }   
                    }
                }
            ?>
        </div>
    </div>
   
</body>
</html>

Step 10: Finally Add some css in the <head></head> section of index.php Change this CSS as it pleases you.

<!DOCTYPE html>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html lang="eng">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
        <title>Pagination</title>
        
        <style type="text/css">
            /* Double Click to Copy and paste Style in <head></head> of index.php */ 
            #pagination {
                display: block;
                text-align: center;
            }
            #pagiCount{
                display: inline-block;
                margin: auto;
            }
            #pagiCount a, #pagiCount strong {
                display: block;
                width: 50px;
                background: rgb(0, 53, 148);
                float: left;
                margin-right: 5px;
                color: #fff;
                text-align: center;
                text-decoration: none;
                font-size:20px;
                line-height: 29px;
            }
            #pagination strong , #pagination a:hover {
                background:rgb(130, 171, 245);
                color:#000;
                font-size:22px;
            }
            tr:nth-child(even) {
                background: #CCC
            }
        </style>

And finally our pagination script is ready to use.

Download Pagination Database used in above code and import in your phpMyAdmin for experimenting purpose

Read More