There are a couple of considerations here. First, you really wouldn't want a SELECT that might be broadly defined to actually return the rows. You could end up with 200,000 rows which would be bad in several ways. So, you want to do a COUNT first, then multiple small queries as the user pages through the dataset. Here is one way to do it.
In a script called say
searchscript.php
you do a count of the records based upon the search criteria and pass that information along to the results display page
which is called
pagescript.php
NOTE: This code tested and works.
See the results at
http://www.herbhost.com/phptest/searchscript.php
<?php
// searchscript.php
// use form input or whatever you plan on doing to get the search
// criterial for the lookup - we'll call it $searchpoop
$searchpoop = 0;
// build query to get a count
$query = "SELECT COUNT(*) as numrows FROM thumbs
WHERE pageno > $searchpoop";
// 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("$db",$dbh))
die("Cannot open the database");
// execute the query
if (!($result = @ mysql_query ($query, $dbh)))
die("Cannot execute query against the database");
$row = @ mysql_fetch_array($result);
$rowcnt = $row["numrows"];
if ($rowcnt > 0) // got some rows on user query
{
// build a link to the results page
echo "<a href=\"pagescript.php?pageid=1&rowcnt=$rowcnt&searchpoop=$searchpoop\">";
echo "$rowcnt Matches Found - Click to see Results</a>";
}
else
{
echo "No Matches Found, Sorry!";
}
?>
</body>
</html>
This next script is the one that gets invoked to actually display the results
<?php
// pagescript.php
// clean up input
$pageid = substr($pageid, 0, 3); // or however big page max is
$pageid = EscapeShellCmd($pageid);
$rowcnt = substr($rowcnt, 0, 6); // or however big max rows is
$rowcnt = EscapeShellCmd($rowcnt);
$searchpoop = substr($searchpoop, 0, 50); // or whatever length
$searchpoop = EscapeShellCmd($searchpoop);
// build query - using 10 recs per page
$skiprows = (($pageid - 1) * 10); // number of rows to skip in query
$query = "SELECT pageno, thumbid, filename FROM thumbs
WHERE pageno > $searchpoop
LIMIT $skiprows, 10";
// 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("$db",$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))
{
$page = $row["pageno"];
$filename = $row["filename"];
$thumbid = $row["thumbid"];
echo "Page - $page, Thumb - $thumb, Filename - $filename<br>";
} // if there are rows in array
} // for i
} // if there were rows
// if not on page 1, then do previous
if ($pageid > 1)
{
$oldpageid = $pageid - 1;
echo "<a href=\"pagescript.php?pageid=$oldpageid&rowcnt=$rowcnt&searchpoop=$searchpoop\">";
echo "<< Previous </a>";
}
// now put out 10 pages worth of links
$maxpage = ceil($rowcnt / 10); // largest page
if ($maxpage < 10) // handle a short search
{
$pagespread = $maxpage - 1;
$midpoint = floor($maxpage / 2);
}
else
{
$pagespread = 9;
$midpoint = 4;
}
// set the starting and ending page numbers
$lopage = $pageid - $midpoint;
if ($lopage < 1)
{
$lopage = 1;
}
$hipage = $lopage + $pagespread;
if ($hipage > $maxpage)
{
$hipage = $maxpage;
$lopage = $hipage - $pagespread;
}
// now put out the ten (or less) links
for ($i = $lopage; $i <= $hipage; $i++)
{
// no link for current page, just echo
if ($pageid == $i)
{
echo " $i ";
}
else // put out the link
{
echo "<a href=\"pagescript.php?pageid=$i&rowcnt=$rowcnt&searchpoop=$searchpoop\">";
echo " $i </a>";
}
} // for all links
// finally, put out the next link if not last page
if ($pageid < $maxpage)
{
$newpageid = $pageid + 1;
echo "<a href=\"pagescript.php?pageid=$newpageid&rowcnt=$rowcnt&searchpoop=$searchpoop\">";
echo " Next >></a>";
}
?>
