Digg Style pagination Class

Question

I am new to PHP and have been trying to build my first project these last few days, it's working mostly as a blog - just so I get a feeling on how to query a database and whatnot.

I now want to be able to paginate the results on my page, this is what I have so far:

<?php
include_once 'inc/functions.inc.php';
include_once 'inc/db.inc.php';

$db = new PDO(DB_INFO, DB_USER, DB_PASS);

$id = (isset($_GET["id"])) ? (int) $_GET["id"] : NULL;

$e = retrievePosts($db, $id);

$fulldisp = array_pop($e);
?>
<div id="blogposts">
    <?php
    if ($fulldisp == 1) {
        ?>
        <span class="postdate"><?php echo $e["date"] ?></span>
        <span class="posttitle"><?php echo $e["title"] ?></span>
        <br/>
        <br/>
        <span class="postbody">
            <?php echo $e["body"] ?>
        </span>
        <?php
    }//end if
    else {
        foreach ($e as $entry) {
            ?>
            <div class="postholder">
                <span class="postdate"><?php echo $entry["date"] ?></span>
                <span class="posttitle">
                    <a href="?id=<?php echo $entry['id'] ?>"><?php echo $entry['title'] ?></a>
                </span>
                <br/>
                <br/>
                <span class="postbody">
        <?php echo $entry["resume"] ?>
                </span>
            </div>
                    <?php
                }//end foreach
            }//end 
            ?>
</div>

And this my function:

<?php

function retrievePosts($db, $id = NULL) {
    if (isset($id)) { //se foi fornecido ID
        $sql = "SELECT title, body, date
            FROM blog
            WHERE id=?
            LIMIT 1";
        $stmt = $db->prepare($sql);
        $stmt->execute(array($_GET["id"]));

        $e = $stmt->fetch(); //guardar em array
        $fulldisp = 1; //uma só entrada
    } 
    else 
    {
        $sql = "SELECT id, title, resume, date
            FROM blog
            ORDER BY date DESC";   

        foreach($db->query($sql) as $row) 
        {
            $e[] = array("id" => $row["id"], "title" => $row["title"], "resume" => $row["resume"], "date" => $row["date"]);
        }
        $fulldisp = 0; //multiplas entradas
    }
    //funçoes só retornam um valor, inserimos fulldisp no fim de $e e separamos
    //os valores de novo no index.php
    array_push($e, $fulldisp);
    return $e;
}

?>

The way this works is, the page is built depending on the URL:

If there is an id there, it displays the content of only that ID.

If there isn't an id in the URL, it is now displaying all the entries in the page.

I now want to be able to paginate these entries, so after having a look around Stackoverflow, it seems the most popular solution is to use the Digg Style Pagination Class, documented here: http://mis-algoritmos.com/digg-style-pagination-class

I've downloaded the class, but I can't figure out how to get this working properly, I'm confused by the fact that the class doesn't have a DB query (I'd expect this to use LIMIT), I was wondering if anyone here on Stack could help me implement this class on my code, or if there is a well documented source on how to do this.

Answer 1

function retrievePosts($db, $id = NULL, $page = 1, $rpp = 5) {
    if (isset($id)) { //se foi fornecido ID
        $sql = "SELECT title, body, date
            FROM blog
            WHERE id=?
            LIMIT 1";
        $stmt = $db->prepare($sql);
        $stmt->execute(array($id));

        $e = $stmt->fetch(); //guardar em array
        $fulldisp = 1; //uma só entrada
    }
    else
    {
        if(!is_numeric($page)) {
            $page = 1;
        }

        if(!is_numeric($rpp)) {
            $rpp = 5;
        }

        $sql = "SELECT id, title, resume, date
            FROM blog
            ORDER BY date DESC
            LIMIT ?
            OFFSET ?
        ";

        $stmt = $db->prepare($sql);
        $stmt->execute(array($page, $rpp * $page));

        foreach($stmt->fetch() as $row)
        {
            $e[] = array("id" => $row["id"], "title" => $row["title"], "resume" => $row["resume"], "date" => $row["date"]);
        }
        $fulldisp = 0; //multiplas entradas
    }
    //funçoes só retornam um valor, inserimos fulldisp no fim de $e e separamos
    //os valores de novo no index.php
    array_push($e, $fulldisp);
    return $e;
}

$rpp - records per page

Also I think:

$stmt->execute(array($_GET["id"]));

should be:

$stmt->execute(array($id));