display from database

[ryany]

Just like the forum threads.
display XX number of posts. then you have a "next Page, Previous page" link ... is there any simple tutorial on this out there?

I've tried looking through showthread, but its making me a lil giddy.

[Dan615]

NOTE: I'm not responsible for anything that happens because of this script. I was too tired to check for any sort of errors, so just take it as a "get the gist" kinda thing.

This is how most of those page things work, I think.

First of all, you want to know how many posts there will be per page, and what page you're on. This information will be passed through the query string.

You also want to be connected to MySQL, since you're gonna be getting stuff from the database.

(works with the newest version of PHP, and remember, this is a bare bones script)

<?

$mysql_hostname = "hostname";
$mysql_username = "username";
$mysql_password = "password";
$mysql_database = "database";

@mysql_connect($mysql_hostname, $mysql_username, $mysql_password) or die(mysql_error());
@mysql_select_db($mysql_database);

(integer)$perpage = (isset($_GET['perpage']) ? $_GET['perpage'] : 25);    // 25 would be your default amount per page
(integer)$page = (isset($_GET['page']) ? $_GET['page'] : 1);        // default page is 1

// more to come...

?>

SQL lets you select a range of rows in the database using the "LIMIT lower,number" command. It takes "number" rows starting at "lower".

You mulpily the page number by the number per page to get the lower limit. Then you can run the query.

(replace // more to come... with this)

$lower = ($page - 1) * $perpage; // subtract 1 from page, or else you'll always start at 25! (figure the math out yourself ;)

$result = @mysql_query("SELECT * FROM your_table LIMIT $lower,$perpage") or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
    // display your information... like $row['post'] or something
} 

The "Next" and "Previous" stuff is simple (this goes after the above statement):

$max = @mysql_query("SELECT COUNT(*) AS posts FROM your_table") or die(mysql_error()); // get the number of rows
$pages = ceil($max['posts'] / $perpage); // the number of pages are the number of posts divided by the amount per page

echo "<br><br>";
if ($page != 1) {
    echo "<a href=\"$_SERVER[PHP_SELF]?page=" . ($page - 1) . "&perpage=$perpage\">Previous</a>&nbsp;";
}

if ($page != $pages) {
    echo "<a href=\"$_SERVER[PHP_SELF]?page=" . ($page + 1) . "&perpage=$perpage\">Next</a>";
} 

So the finished script would be...

<?

$mysql_hostname = "hostname";
$mysql_username = "username";
$mysql_password = "password";
$mysql_database = "database";

@mysql_connect($mysql_hostname, $mysql_username, $mysql_password) or die(mysql_error());
@mysql_select_db($mysql_database);

(integer)$perpage = (isset($_GET['perpage']) ? $_GET['perpage'] : 25);    // 25 would be your default amount per page
(integer)$page = (isset($_GET['page']) ? $_GET['page'] : 1);        // default page is 1

$lower = ($page - 1) * $perpage; // subtract 1 from page, or else you'll always start at 25! (figure the math out yourself ;)

$result = @mysql_query("SELECT * FROM your_table LIMIT $lower,$perpage") or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
    // display your information... like $row['post'] or something
}

$max = @mysql_query("SELECT COUNT(*) AS posts FROM your_table") or die(mysql_error()); // get the number of rows
$pages = ceil($max['posts'] / $perpage); // the number of pages are the number of posts divided by the amount per page

echo "<br><br>";
if ($page != 1) {
    echo "<a href=\"$_SERVER[PHP_SELF]?page=" . ($page - 1) . "&perpage=$perpage\">Previous</a>&nbsp;";
}

if ($page != $pages) {
    echo "<a href=\"$_SERVER[PHP_SELF]?page=" . ($page + 1) . "&perpage=$perpage\">Next</a>";
}

@mysql_close();

?>

Questions?

P.S. For all you people big on compatibility, this is just an example , and I'm too tired to double check my code...

[ryany]

Thanks I'll try it out.

[Dan615]

If you want i'll show you how to make the page list at the bottom, like

< Prev : 1 2 [ 3 ] 4 5 : Next >

[ryany]

Thanks for your advice! Very Very Very much appreciated. Just what i wanted and it went smoothly

[ryany]

Originally posted by Dan615
If you want i'll show you how to make the page list at the bottom, like

< Prev : 1 2 [ 3 ] 4 5 : Next >

Sure! That would be awesome. Thanks

[Dan615]

I designed this after what I see as vB's multiple page thing. Since I don't have a copy of vB, I had to go with what I saw here and on other vB forums...and I think I did a pretty good job of rebuilding it. This probably isn't how the vB team did it, they probably found a much more efficient way to do things than I did...but that's because it's their job

What it does is displays a maximum of 5 page links at one time. If there are more than five links, than it puts the ... before the prev and next buttons. You'll see how it works

This is a modified version of what I made for my site. If you're gonna have some extra stuff tacked to the end of the query string, I can change it to put the rest of the query string on the end.

Note: works in the newest versions of PHP...if you want backward compatibility, you can put $HTTP_GET_VARS in place of $_GET...put you'll have to say global $HTTP_GET_VARS in the beginning of the function (i think, never went back to those once I got the new version).


As for the function's arguments, you call it like so:

echo build_page_list("thepage.php", $rows, 25);

This assumes that earlier in the script you found out the number of rows. Here it is build into a script:

<?

// connect to MySQL and all that crap...

$numberof = @mysql_query("SELECT COUNT(index_name) AS rows FROM table_name");

$perpage = 25;

echo build_page_list($_SERVER['PHP_SELF'], $numberof[rows], $perpage);

?>

I've tested it a bunch of times, just not with this modified version (I only deleted lines that had to do with the site, so it should work...)

And, of course, the function:

<?

function build_page_list($base_url, $total_items, $perpage) {
    (integer)$current_page = (!isset($_GET['page']) || !$_GET['page'] ? 1 : $_GET['page']);
    $num_pages = ceil($total_items / $perpage);

    $page_list = "Pages ($num_pages): ";

    if ($num_pages > 1) {
        if ($num_pages > 5 && $current_page > 3) {
            $page_list .= "<a href=\"$base_url&page=1\">&lt;&lt; First</a> <b>...</b> ";
        }
        if ($current_page != 1) {
            $page_list .= "<a href=\"$base_url&page=" . ($current_page - 1) . "\">&lt; Prev</a> ";
        }

        // I was tired of copying and pasting the same line, so I just threw it in here, make it a quick eval
        $quick_sequential_link = "\$page_list .= (\$i == \$current_page ? \"<b>\" : \"<a href=\\\"\$base_url&page=\$i\\\">\") . \"[ \$i ]\" . (\$i == \$current_page ? \"</b> \" : \"</a> \");";

        if ($num_pages <= 5) {
            for ($i = 1; $i <= $num_pages; $i++) {
                eval($quick_sequential_link);
            }
        } else {
            if ($current_page <= 3) {
                for ($i = 1; $i <= 5; $i++) {
                    eval($quick_sequential_link);
                }
            } elseif ($current_page >= ($num_pages - 2)) {
                for ($i = ($num_pages - 4); $i <= $num_pages; $i++) {
                    eval($quick_sequential_link);
                }
            } else {
                $start = $current_page - 2;
                $end = $current_page + 2;
                for ($i = $start; $i <= $end; $i++) {
                    eval($quick_sequential_link);
                }
            }
        }

        if ($current_page != $num_pages) {
            $page_list .= " <a href=\"$base_url&page=" . ($current_page + 1) . "\">Next &gt;</a>";
        }
        if ($num_pages > 5 && $current_page <= ($num_pages - 3)) {
            $page_list .= " <b>...</b> <a href=\"$base_url&page=$num_pages\">Last &gt;&gt;</a>";
        }
    }

    return $page_list;
}

?>