Paged Recordsets

furious5

I have been struggling with paged recordsets over the past couple of weeks and have got to a point where everything is now almost working.

At the moment all my results are being shown on one page, I have set the limit to 1 result per page. E.g at the moment the first result and second result are all shown on the same page. Instead 1 result should be on one page and the other result on the other page.

Here is the code, could anyone suggest any amendments?

<?php

include("datapager.php");

include("functions.php");

echo "<html>\n<head><title>Datapager Example</title></head>\n<body>\n";

// connect to some database

$conn = mysql_connect();
mysql_select_db("jobclever");

// the name of the table to get all results from in this example

$table = "job";

// the sql statement to query

$sql = "SELECT * FROM $table";
$sql_result = mysql_query($sql) or die('SQL Error');
$num_rows = mysql_num_rows($sql_result);

// create a new datapager passing it the connection and the sql

$dp = new datapager($conn, $sql);

// check to see if the page variable has been set

if( !isset( $page ) ){
$page = 1;
}

// set the number of results to get for each page

$pagesize = 1;

// execute the query with the current page and page size and return a mysql result

$res = $dp->execute($page, $pagesize);

// if the result is valid, use it like any other mysql result to output the records

if( $res ){
$fieldcount = mysql_num_fields($res);
}

if ($num_rows == 0){
echo 'Sorry your search did not return any results. Please return to the search page <a href=search.php>here</a>';
} else {

// output a header with information on the current page, number of pages and number of records

echo "<table align=center><tr><th colspan=$fieldcount>Showing page ".$dp->page." of ".$dp->pagecount;
echo ". Records ".((($dp->page -1) $dp->pagesize)+1)." to ".$dp->page $dp->pagesize." of ".$dp->recordcount."</th></tr><tr></table>\n";

while ($row = mysql_fetch_array($sql_result)){
$jobid = $row["jobid"];
$date= $row["date"];
$title = $row["title"];
$desc = $row["description"];
$location = $row["location"];
$salary = $row["salary"];
$industry = $row["industry"];

print "<table><tr><td>Job ID: $jobid</td></tr>";
print "<tr><td>Date:";
echo fixDate($date); 
Print "</td></tr>";
print "<tr><td>Title: $title</td></tr>";
print "<tr><td>Description: $desc</td></tr>";
print "<tr><td>Location: $location</td></tr>";
print "<tr><td>Salary: $salary</td></tr>";
print "<tr><td>Industry: $industry</td></tr></table>";
print "To apply please click <a href=result_details.php?jobid=$jobid>here</a>\n";
	}

// output a footer containing links to show previous / next and all other pages of results

echo "<tr><td colspan=$fieldcount><table width='100%'><tr>\n";
echo "<td width='33%' align=left>".$dp->prevpage("<a href='testdatapager.php?page=%page%'>Previous</a>\n");
echo "</td><td width='33%' align=center>".$dp->pagelinks("<a href='testdatapager.php?page=%page%'>%page%</a>", "%page%");
echo "</td>\n<td width='33%' align=right>".$dp->nextpage("<a href='testdatapager.php?page=%page%'>Next</a>");
echo "</td></tr></table></td></tr></table>\n";
}
?>

cgraz

You need to run 2 queries.

The first query you use to get a count on how many total results there are. The 2nd query you use to output the results. The trick is that the 2nd query MUST have the LIMIT command to work. This outputs x amount of results per page.

Have a look at this thread. It's 100% commented.

Cgraz

furious5

Ok I have played around with that code and added a second query, which returns the results and got it to limit the results to the per page variable.

My problem now, is that if I have two pages and want to look at the second page when I click the link it takes me to the second page, however it shows the same result as page1.

Any ideas how to overcome this?

BeastRider

Take a look at the code I posted in this thread. It does exactly what you want, at ten records per page, but I bet you can change it to one.

http://www.phpbuilder.com/board/showthread.php?s=&postid=10355168#post10355168

furious5

Thanks for that, but I really want to try and amend my existing code I have already.

I have posted a couple of messages on this before, and every time I discuss the problem someone suggests another lot of code to use.

Which means time spent understanding the code and implementing it, I am now on to my 3rd set of code and it all seems to be working apart from the links to display the results.

I think this is probably to do with the second sql statement, either its placement in the code or the construct I have used. But my PHP skills are fairly rusty so was looking for someone else to cast their eye over it and give me their opinion.

leatherback

Hi,

You want to add an offset to your second query (The one that gets your data).

This tell the database to start retrieving your data not from the first result, but fomr the $offset result in your database.

Abviously, the offset is different for each page you display, so add a little piece of code to create the link:


  if ( $NumHits > $maxresult )
    { //There are more then 1 page to show, create links
       // $numhist is from the first query: maxresult is your maximum number of results to be displayed

//calculate the number of pages, 

$pages = ceil($NumHits/$maxresult);


for ( $i = 1; $i <= $pages ; $i++)
   {
   $buildoffset = ( ($i-1) * $maxresult ) ;

   $Navig .= "<a href=\"YourPage.php?offset=$buildoffset>[$i]</a>";
   }

You also want to change your first query a bit, to use the offset, after you have defined the first part of the second wuery (The satndard "select * from database where ..." you add:


if (isset($offset))
      {
      $query .=" limit $offset, $maxresult";
      }
      else
      {
      $query .=" limit $maxresult";
      }

Hope htis helps

furious5

Ok thanks for that.

I will give that a try when I get home this evening.

furious5

I looked at the code again when I got home.

There doesn't appear to be any need for the offset, the SQL was returning the first recordset because the $page was not being passed to the page when the link was hit. Thus it assumed that it was the first page.

My next problem is integrating a search page in to code.

I have built a search page with 3 variables: $title, $location, $sector

How would I go about constructing the SQL to search the database and return paged results?