next / previous table searching problem

[gljones]

Hi,

I'm trying to have data displayed at 25 rows/page. the first page outputs the data correctly and counts the total amount of rows found. when the "next" link is clicked on [page 2], no rows are found. not sure what it is, i may be over complicating it.

here's the code:

$where_clause = "where area = '$area_search' && Specialty = '$type'";

$strSQL = "SELECT * FROM tnh2 $where_clause";
$pagelimit = "25";
$result = mysql_query($strSQL) or die ("Error in search string. ");
$totalrows = mysql_num_rows($result);
$pagenums = ceil($totalrows/$pagelimit);


// get the starting page
if ($page=='')
{
$page='1';
}

echo "<br>Page $page > ";


// get the starting row value
$start = ($page - 1) * $pagelimit;
$starting_no = $start + 1;

// get end count for this page
echo " $totalrows "."matches found > ";
if ($totalrows - $start < $pagelimit)
{
$end_count = $totalrows;
}
elseif ($totalrows - $start >= $pagelimit)
{
$end_count = $start + $pagelimit;
}


echo " Results $starting_no to $end_count shown.<br><br>";

// create dynamic next and previous page links
If ($totalrows - $end_count > $pagelimit)
{
$var2 = $pagelimit;
}
elseif ($totalrows - $end_count <= $pagelimit)
{
$var2 = $totalsrows - $end_count;
}

// previous link
if ($page > 1)
{
echo "<a href=\"".$PHP_SELF."?page=".($page - 1)."&area=".$area_search."&Specialty=".$type."\">

Previous 25 Listings</a>&nbsp&nbsp&nbsp&nbsp";
}

// next link
if ($page < $pagenums)    
{
echo "<a href=\"".$PHP_SELF."?page=".($page + 1)."&area=".$area_search."&Specialty=".$type."\">Next 25 Listings</a>";
}

$strSQL = "SELECT * FROM tnh2 $where_clause Limit $start, $pagelimit";
$result = mysql_query($strSQL) or die ("Error in strSQL: $strSQL. " . mysql_error());

?>

<?
// if records present
if (mysql_num_rows($result) > 0)
{
// iterate through resultset
// print listings
while($row2 = mysql_fetch_object($result))
{

?>
the data is then printed after this.....

thanks for any help.

[EgoManiac]

might be easier to use the LIMIT function in your SQL syntax

for example:

SELECT * FROM table WHERE foo=bar LIMIT 0,25;

will only give you 25 rows starting from row 0

SELECT * FROM table WHERE foo=bar LIMIT 25,25;

will only give you 25 rows starting from row 25

etc

hope that helps

[gljones]

thanks for your reply. the limit is working correctly, although i see what you mean by simplifying it.

what isn't working is that on page 2 of the next 25 rows of data, no rows are being found. there should be 107 records and only the first 25 show on page 1.

thanks

[keith73]

here's an example of how I paginate results and use the limit clause of the query.

<?php
$start = ( isset($_GET['start']) )?$_GET['start']:0;
$limit = ( isset($_GET['limit']) )?$_GET['limit']:20;
$result = mysql_query("SELECT count(*) from Table1");
$total = mysql_result($result,0,"count(*)");
mysql_free_result($result);

$result = mysql_query("SELECT * FROM Table1 LIMIT $start,$limit");
if ( !$result ){
    print "QUERY ERROR";
    exit;
}//end if

while ( $row = mysql_fetch_object($result) ){
    //print your results here
}//end while

//pagination
//first check to see if we even need to show page numbers
if ( $total > $limit && $limit > 0 ){
    $pages = $total/$limit;

if ( $total%$limit ){
    $pages++;
}//end if

for ( $i=1;$i <= $pages;$i++ ){
    if ( $i == ($start+$limit)/$limit ){
        echo "$i ";
    } else {
        echo "<a href=\"$PHP_SELF?start=" . (($i*$limit)-$limit) . "&limit=$limit\">$i</a> ";
    }//end if
}//end for
}//end if
?>

hope this helps

• keith

[keith73]

Originally posted by gljones
thanks for your reply. the limit is working correctly, although i see what you mean by simplifying it.

what isn't working is that on page 2 of the next 25 rows of data, no rows are being found. there should be 107 records and only the first 25 show on page 1.

thanks

print your query out to the screen to see what it actually looks like. perhaps something is not being passed to the second page properly.

The query should look the same on every page, except for the limit clause. if it looks different on the second page, something isn't right with the info being passed.

• keith

[gljones]

thanks for the reply. I just noticed that on the 2nd page it says [echoes] back "Results 26 to 1 shown". i'm not sure what that implies - I must have something backwards.

I also tried your code. it wouldn't display the results at all, i was able to get a count [$total] from the table, just not display the results. I think i'm getting stuck on "iterate through resultset" part where it has to print the results out.

after your code, how are the results printed, i was using this:

<?
// if records present
if (mysql_num_rows($result) > 0)
{
// iterate through resultset
// print listings
while($row = mysql_fetch_object($result))
{

?>
then the data would be echoed here like:

<? echo $row->something; ?>

thanks again.