(yet) another question about recordset pagination....

Cyber_type

03-10-2008, 07:44 PM

Hello everyone.
So i have a script who displays thumbnails from images, those are stored in a mysql table. (thanks codingForums).:thumbsup:

<?php
require_once 'functions.php';

$page = 0;
$limit = 4;
//connect to the database
$link = mysql_connect("localhost", "root", "pass")
or die("Could not connect: " . msql_error());
mysql_select_db("dados", $link)
or die (mysql_error());

$ImageDir = "images/";
$ImageThumb = $ImageDir . "thumbs/";
?>

<html>
<head>
<title>Welcome to our Photo Gallery</title>
</head>
<body>
<p align="center">Click on any image to see it full sized.</p>
<table align="center">
<tr>
<td align="center">Image</td>
<td align="center">Caption</td>
<td align="center">Uploaded By</td>
<td align="center">Date Uploaded</td>
</tr>

<?php
//get the thumbs

$sql = "SELECT SQL_CALC_FOUND_ROWS " .
"* FROM images " .
"LIMIT $page, $limit";
$result = mysql_query($sql)
or die(mysql_error() . $sql);

echo "<br>rows in db: </br>" . mysql_result($result, 0);

while ($rows = mysql_fetch_array($result)) {
extract($rows);
echo "<tr>\n";
echo "<td><a href=\"".$ImageDir . $image_id . ".jpg\">";
echo "<img src=\"" . $ImageThumb . $image_id . ".jpg\" border=\"0\">";
echo "</a></td>\n";
echo "<td>" . $image_caption . "</td>\n";
echo "<td>" . $image_description . "</td>\n";
echo "<td>" . $image_date . "</td>\n";
echo "</tr>\n";
}
echo paginate($result);
?>

</table>
</body>
</html>

In my book i have found a function that paginate recorsets.

<?php
function paginate($limit=4) {
global $admin;

$sql = "SELECT FOUND_ROWS();";
$result = mysql_query($sql)
or die(mysql_error());
$row = mysql_fetch_array($result);
$numrows = $row[0];
$pagelinks = "<div class=\"pagelinks\">";
if ($numrows > $limit) {
if(isset($_GET['page'])){
$page = $_GET['page'];
} else {
$page = 1;
}
$currpage = $_SERVER['PHP_SELF'] . "?" . $_SERVER['QUERY_STRING'];
$currpage = str_replace("&page=".$page,"",$currpage);

if($page == 1){
$pagelinks .= "<span class=\"pageprevdead\">< PREV</span>";
}else{
$pageprev = $page - 1;
$pagelinks .= "<a class=\"pageprevlink\" href=\"" . $currpage .
"&page=" . $pageprev . "\">< PREV</a>";
}

$numofpages = ceil($numrows / $limit);
$range = $admin['pageRange']['value'];
if ($range == "" or $range == 0) $range = 7;
$lrange = max(1,$page-(($range-1)/2));
$rrange = min($numofpages,$page+(($range-1)/2));
if (($rrange - $lrange) < ($range - 1)) {
if ($lrange == 1) {
$rrange = min($lrange + ($range-1), $numofpages);
} else {
$lrange = max($rrange - ($range-1), 0);
}
}

if ($lrange > 1) {
$pagelinks .= "..";
} else {
$pagelinks .= "  ";
}
for($i = 1; $i <= $numofpages; $i++){
if ($i == $page) {
$pagelinks .= "<span class=\"pagenumdead\">$i</span>";
} else {
if ($lrange <= $i and $i <= $rrange) {
$pagelinks .= "<a class=\"pagenumlink\" " .
"href=\"" . $currpage . "&page=" . $i .
"\">" . $i . "</a>";
}
}
}
if ($rrange < $numofpages) {
$pagelinks .= "..";
} else {
$pagelinks .= "  ";
}

if(($numrows - ($limit * $page)) > 0){
$pagenext = $page + 1;
$pagelinks .= "<a class=\"pagenextlink\" href=\"" . $currpage .
"&page=" . $pagenext . "\">NEXT ></a>";
} else {
$pagelinks .= "<span class=\"pagenextdead\">NEXT ></span>";
}
} else {
$pagelinks .= "<span class=\"pageprevdead\">< " .
"PREV</span>  ";
$pagelinks .= "<span class=\"pagenextdead\"> " .
"NEXT ></span>  ";
}
$pagelinks .= "</div>";
return $pagelinks;
}

?>

As you can see i use "SELECT SQL_CALC_FOUND_ROWS " to return the number of total rows in the db, but how to pass this value to my function? tried to get that value trough "echo mysql_result($result, 0);" and than called paginate($result) but it's not that....:( . Also in the function have a global $admin variable --- did i miss something in my gallery script?
Said in my book "reusable code" but still not work for me. I really don't have error's displaying, i have already the navigation bar (<prev 1|2|3 next>) but no way to navigate trough the database recorset....
Could someone kindly give me a kick on my code? I feel that i'm not far but my non experience in php/mysql is a barrier who frustrates me....
In advance thanks for your comments and advices.

Inigoesdr

03-10-2008, 08:14 PM

SQL_CALC_FOUND_ROWS doesn't return a value itself, you have to SELECT FOUND_ROWS(); after that query to get the value. (Manual (http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows))

Cyber_type

03-10-2008, 09:27 PM

you're right and the SELECT FOUND_ROWS(); is already in the function

Inigoesdr

03-10-2008, 10:20 PM

Whoops, I missed that.. try to var_dump($row, $numrows); and see what the value is. Also, make sure you have error reporting turned on:
ini_set('display_errors', 1);
error_reporting(E_ALL);

Cyber_type

03-10-2008, 11:56 PM

:confused: sorry i can't follow your code:(:(:(
looks too hard for me at this moment

_Aerospace_Eng_

03-11-2008, 04:40 AM

Its just PHP. Put it at the top of your script. It simply turns on full error reporting just in case you are getting any errors.

Cyber_type

03-11-2008, 10:21 AM

did it. but still having no error. OK for the navigation bar, still having the prev 1234 next, when i click on the numbers 1234 it changes the URL (gallery.php?&page=1 and so on) but at this moment i can't figure out what doesn't let's me navigate trough the recordset....

Inigoesdr

03-12-2008, 11:07 AM

Well, you need to do some debugging and find out where the problem lies. var_dump(); the variables you're using and make sure they are what you expect as the script progresses. Or you can try a different pagination script:
/**
* paginate - This function uses the $_GET['page'] variable to pass the page number to the next/prev page
*
* @param int $currentPage Current Page
* @param int $totalPages Total Pages
* @param string $link Hyperlink with a ? at the end if it doesn't have any $_GET vars
*
* @return string
*/
function paginate($currentPage, $totalPages, $link)
{
$prev = $pages = $next = '';

if($currentPage == 0)
$currentPage = 1;
elseif($currentPage > 1)
$prev = sprintf('<a href="%s&page=%d">« Previous</a> ', $link, $currentPage - 1);

for ($i = 1; $i <= $totalPages; $i++)
{
if($currentPage == $i)
$pages .= $i . ' ';
else
$pages .= sprintf('<a href="%s&page=%d">%d</a> ', $link, $i, $i);
}

if($currentPage < $totalPages)
$next = sprintf('<a href="%s&page=%d">Next »</a>', $link, $currentPage + 1);

return $prev . $pages . $next;
}
// example:
echo paginate((int) $_GET['page'], 10, $_SERVER['SCRIPT_NAME'] . '?');
This script requires you to provide the number of pages(which is easy to do totalRows/rowsPerPage), and the URL to the page you want to paginate. But, you can give it a try as an alternative.