Grow your CSS skills. Land your dream job.

Pagination for Splitting results from mysql

  • # December 21, 2012 at 7:04 pm

    Hi everyone

    I need help with implementing a simple pagination feature to my own/custom website, and since I’m really confused I’ll explain from basics what I am trying to achieve.

    I have a simple table with 3 columns and 17 entries in my database. The results are returned in a HTML table, each entry in a separate row. Now I want to split the results to show only 5 entries per page.

    I have searched for various tutorials and codes which at the end got me confused and I’m stuck :(

    Anyone can help me to implement such feature in to my page, please?

    here is my code atm:

    < ?php

    //connect to the database
    mysql_connect (“localhost”,”***”,”***”) or die (‘Cannot connect to MySQL: ‘ . mysql_error());
    mysql_select_db (“***”) or die (‘Cannot connect to the database: ‘ . mysql_error());

    //query
    $query = mysql_query(“select title, description, image from jobs”) or die (‘Query is invalid: ‘ . mysql_error());

    //write the results
    while ($row = mysql_fetch_array($query)) {

    ?>

    < ?php echo $row; ?>

    # December 21, 2012 at 7:35 pm

    This is illustrative. Please don’t copy/paste into production code, as there is no checking of the GET request input.

    Grr… not getting to grips with this markdown malarky…

    <table border="0" cellpadding="4" cellspace="2" class="jobReviews">
    <?php
    $page = @$_GET;
    if( !isset( $page ) ) {
    $page = 1;
    }
    mysql_connect ("localhost","***","***") or die ('Cannot connect to MySQL: ' . mysql_error());
    mysql_select_db ("***") or die ('Cannot connect to the database: ' . mysql_error());
    $query = mysql_query("select title, description, image from jobs") or die ('Query is invalid: ' . mysql_error());
    $result = 0;
    while ($row = mysql_fetch_array($query)) {
    if( $result >= $page * 5 || ( $result < ( $page - 1 ) * 5 ) ) {
    $result = $result + 1;
    continue;
    }
    ?>
    <tr>
    <td>
    <p style="width:558px; margin-left:7px;">
    <strong><?php echo $row; ?></strong>
    <img src="img/gallery/thumbs/<?php echo $row; ?>" width="188" height="92" />
    <?php echo $row; ?>
    </p>
    </td>
    </tr>
    <?php
    $result = $result + 1;
    }
    ?>
    </table>
    # December 21, 2012 at 7:47 pm

    Sorry — should have mentioned you will need to give links to the other pages.

    $page will show the page they are on, while $result will show you the total number of entries.

    If $page is greater than one, you will want a link back to $page – 1.

    If $page * 5 is less than or equal to $result, you will want a link to $page + 1.

    # December 21, 2012 at 8:24 pm

    Thanks for the help, the results are now limited to 5 on the page.

    > Sorry — should have mentioned you will need to give links to the other pages.

    I have made a link like this following your examples

    < a href="">Next

    This goes to my main domain page plus /2 at the end. But page is not found (404).
    Where are the different pages defined?

    # December 21, 2012 at 8:26 pm

    Sorry I’m still a novice in this stuff.

    So I got the link like this, and it works:

    < a href="articles.php?page=">Next

    I hope that’s how it should be.

    Thanks for your help.

    # December 22, 2012 at 3:33 am

    You’ll need some conditionals. As I said above:

    > If $page is greater than one, you will want a link back to $page – 1.

    > If $page * 5 is less than or equal to $result, you will want a link to $page + 1.

    <?php
    if( $page > 1 ) {
    ?>
    <a href="articles.php?page=<?php echo $page - 1; ?>">Previous</a>
    <?php
    }
    if( $page * 5 <= $result ) {
    ?>
    <a href="articles.php?page=<?php echo $page + 1; ?>">Next</a>
    <?php
    }
    ?>
    Viewing 6 posts - 1 through 6 (of 6 total)

    You must be logged in to reply to this topic.

    Projects

    Colophon

    CSS-Tricks* is created, written by, and maintained by Chris Coyier. It is built on WordPress, hosted by MediaTemple, and the assets are served by MaxCDN. The fonts are Source Sans and Source Code Pro. It is made possible by viewers like you who subscribe to The Lodge and through advertising for products and services I like.

    *May or may not contain any actual "CSS" or "Tricks".