Super Simple Pagination

sgtpepper

Hello,

I am looking for a very SIMPLE way to create 'pagination' type output. Basically, I would like to use SQL LIMIT.

I would like to limit results to 10 per page...and have a hyperlink that displays

Previous 10 results ----- Next 10 results

That's all. No numbers....no alternating rows....just a simple previous and next.

I am not sure how to do it.

I have a query that limit 0,10 but how do I create the hyperlinks?

BeastRider

Pass a page number in the URL. Let's say that you are going to get the first set of records. Then you'd pass

http://www.mydomain.com/pagescript?pageid=1

Then pagescript looks like:

<?php
// clean up input
$pageid = substr($pageid, 0, 3); // or however big page max is
$pageid = EscapeShellCmd($pageid);

// build query - using 10 recs per page - but we read 11 rows to
// have a way of knowing if we are at end of file so we don't put
// out the 'next page' link
$skiprows = (($pageid - 1) * 10); // number of rows to skip in query
$query = "SELECT * FROM $table
                   LIMIT $skiprows, 11";

// connect to the database
if(!($dbh=mysql_connect ("localhost",
                         "$user",
                         "$pass")))
  die("Cannot connect to the database");

// open the connected database
if (!mysql_select_db("$user",$dbh))
  die("Cannot open the database");

// execute the query
if (!($result = @ mysql_query ($query, $dbh)))
  die("Cannot execute query against the database");

// if we got some rows back then get some numbers and output page
$numrows = mysql_num_rows($result);
if ($numrows > 0)
  {
  for ($i = 1; $i <= 10; $i++)
    {
     // last page may have less than 10 rows 
    if ($row = @ mysql_fetch_array($result))
      {
      // do all the stuff to put out the row
      } // if there are rows in array
    } // for i

  // now put out next and previous links
  // if not on page 1, then do previous
  if ($pageid > 1)
    {
    $oldpageid = $pageid - 1;
    echo "<a href=\"pagescript.php?pageid=$oldpageid\">";
    echo "Go Back to Page $oldpageid</a>";
    }

  // if rowcount less than 11, must be last page otherwise do link
  if $(numrows > 10) // there will be a next page
    {
    $newpageid = $pageid + 1;
    echo "<a href=\"pagescript.php?pageid=$newpageid\">";
    echo "Go Forward to Page $newpageid</a>";
    }
  } // if $numrows > 0
?>

You can also get a little fancier and fix up the script so that you don't get a last page with one or two orphaned entries... you make the current page 1 or 2 entries longer.

DeadlySin3

This lil tutorial from codewalkers.com might help you out.. did wonders for me!

http://codewalkers.com/tutorials/4/1.html

BeastRider

Originally posted by DeadlySin3
This lil tutorial from codewalkers.com might help you out.. did wonders for me!

This is not a very well written example. It relies upon a second query, and a field in the table defined expressly for the purpose, in order to know if you are at end of file. Neither is necessary. All that is necessary is to read one more record than the number of records you intend to display on the page. If you get that extra record there is always another page. If you don't, there are no more records. The cost for this method is one additional record per select... small. The cost for the example in the tutorital is a second query which has to count all rows.

DeadlySin3

While all thats true 😉 he did ask for a super simple method.

If you want efficient and re-usable, I suggest this tutorial - http://www.sitepoint.com/article/662/

The stuff you're looking for would probably be on page 5

BeastRider

Hmmm... no need to invoke OOP to paginate. What can be simpler than simply reading an extra record for each page? If you get the record, you have another page. If you don't, there are no more records. That is the ultimate in simple pagination... no counters, no extra queries... is there a simpler way?

sgtpepper

Beast, can you please give me an example on how to do this?

sgtpepper

Beast,

Sorry if this seems obvious, but I am confused by your pagescript example above.

Where do I define the DB and Table?

I see where I should place user and pass, but am unclear on where to get the DB and Table defined...

Cleric

In trying to make my own pagination for result sets, there were a couple of things I have found out:

1.) There is a need to select only the rows which will be displayed on the particular page. Some would generate a result set that is the entire set of records which matched the criteria and simply skip over the portion of beginning rows to get to the rows desired. This is lazy and inefficient. With a slight bit more of code, you can drastically reduce the time it takes to get result sets by using the LIMIT portion of a SELECT query.

2.) In order to select the portion of the result set that you want, you must know the total number of items which match your search criteria, the number of items per page, and the desired page to show. From these three pieces of information, you can easily select only the portion of records needed for the page. The total number of items which match your search criteria is necessary as most likely the information beind displayed is of a dynamic content, so the number of pages can possibly change.

3.) When generating the paginated result set, the result set should be queried with the "LIMIT <starting row number>, <number of rows to return>" to get the portion of records for the page. To determine the number of pages, use this snippet of PHP code:

ceil($totalNumberOfRecords / ROW_LIMIT);

Finally, the current page you are displaying is needed.

Hope this helps, and if you need more help, please reply to me and I will email a example of what I do.

Andy

BeastRider

Originally posted by sgtpepper
Beast,

Sorry if this seems obvious, but I am confused by your pagescript example above.

Where do I define the DB and Table?

I see where I should place user and pass, but am unclear on where to get the DB and Table defined...

I removed the names of my database and tables from the script for security reasons. You should already have a name for a database set up in phpMyAdmin or some such, as well as a table name. Put them in the correct place.

And, I screwed up in changing names. The line

// open the connected database
if (!mysql_select_db("$user",$dbh))
  die("Cannot open the database");

should have a database, not a user as in

// open the connected database
if (!mysql_select_db("YOUR_DB_NAME_HERE",$dbh))
  die("Cannot open the database");

DeadlySin3

I think a good way to go about doing that would be

first, make a file called config.php

and put this information into it

/* MySQL Information */
$db_host = "localhost";
$db_user = "database_user_here";
$db_pass = "database_password";
$db_name = "database_name_here";

Then,

include("config.php");
$db = mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db ($db_name) or die ("Cannot connect to database");
/* Connected to database and selected table unless you got error */

This way you seperate all of your configuration from most of the actual coding and changing the info would be easy for anyone that has very little to no experience.

BeastRider

Interestingly enough, I was using an include file for dbnames, etc., but no matter what I tried, I kept getting some sort of output prior to issuing a header when I was writing a script to read an image out of a database.

I would swear that there were/are no extra spaces, lines, etc., in the include file, but I still couldn't get it to work.

This works.

<?php
ob_start(); // turn output buffering on
// open the database and retrieve the record
(!($dbh=mysql_connect ("localhost",
                          "hard-coded-username",
                          "hard-coded-password")));
(!mysql_select_db("hard-coded-database",$dbh));
$query = "SELECT thumbnail FROM thumbs
          WHERE thumbid = $thumb";
(!($result = @ mysql_query ($query, $dbh)));
$data = @mysql_fetch_array($result);
$image = $data["thumbnail"];
header("Content-Type: image/jpeg");
echo $image;
ob_end_flush();
?>

but this does not work (get's a big red x)... using your include as the example.

<?php
include (config.php);
ob_start(); // turn output buffering on
// open the database and retrieve the record
(!($dbh=mysql_connect ("$db_host",
                          "$db_user",
                          "$db_pass")));
(!mysql_select_db("$db_name",$dbh));
$query = "SELECT thumbnail FROM thumbs
          WHERE thumbid = $thumb";
(!($result = @ mysql_query ($query, $dbh)));
$data = @mysql_fetch_array($result);
$image = $data["thumbnail"];
header("Content-Type: image/jpeg");
echo $image;
ob_end_flush();
?>

I just gave up trying to make it work. But, as a rule, I like includes.

DeadlySin3

hmm thats odd. the code i posted above is what i'm using on my blog that i'm writing.. it works great.

I love includes too :c)

BeastRider

If you are using your 'Included" routines to display an image out of a database, ie,

to first output a content header as in:

header("Content-Type: image/jpeg");

followed by data, then

If you are doing this with an include, then I would much appreciate a copy of your routine to find out what I am doing wrong. Email me, please.