How to paginate your SQL querys

How to paginate your SQL querys

On this post you will learn how to paginate an SQL query instead of showing all the rows, in order to avoid wasting time on the page load and at the same time save bandwidth.

Basically, we have to limit the SQL query and generate new queries similar to the original that gather the rest of rows to paginate.

Maybe this is not the best way to do this, but at least I will try to show you my own way.

If you have any suggestion, go ahead to the comments, I would be really grateful :)

  1. The first thing that we need is to include the mysql handler (or connection), and the pagination class.

    1. require_once('mysql_connection.php');
    2. include_once('pagination.class.php');

  2. How many items we will show per page?

    1. $items = 10;

  3. By default we are in the first page

    1. $page = 1;

  4. If the current page number is defined on the url ($_GET['page']), we will generate the SQL query fragment that will limit the rows according to the current page number..

    1. if(isset($_GET['page']) and is_numeric($_GET['page']) and $page = $_GET['page'])
    2.          $limit = " LIMIT ".(($page-1)*$items).",$items";
    3.       else
    4.          $limit = " LIMIT $items";

  5. We will generate the SQL query to fetch all the rows, and at the same time the auxiliar query to gather the total rows (this is to limit the pagination)

    1. $sqlStr = "SELECT * FROM registros";
    2. $sqlStrAux = "SELECT count(*) as total FROM registros";

  6. Fetching all the rows

    1. $aux = Mysql_Fetch_Assoc(mysql_query($sqlStrAux));

  7. We will execute the SQL query, adding the $limit variable (generated on the step 4) this is for bring only the corresponding rows to the current page.

    1. $query = mysql_query($sqlStr.$limit, $mysql);

  8. Now, we need to verify that the query has returned rows. If not, we need to show a message to warn the user about the empty result.

    1. if($aux['total']>0){
    2.          $p = new pagination;
    3.          $p->Items($aux['total']);
    4.          $p->limit($items);
    5.          $p->target("paginate_query.php");
    6.          $p->currentPage($page);
    7.          $p->show();
    8.          echo '<ul>';
    9.          while($row = mysql_fetch_assoc($query)){
    10.                echo "<li>{$row['id']} - {$row['nombre']}</li>";
    11.             }
    12.          echo '</ul>';
    13.          $p->show();
    14.  
    15.       }else
    16.          echo "Rows not found in the DB.";
External Links
There are 25 comments. for this entry.