helping with page paging

[BlackRaven]

not sure what i am doing wrong, but my paging doesn't work for seom reason keeps showing the same on diff page numbers.

<?php

$page = $_GET["page"];
if (($page<0) ¦¦ ($page=="")) $page=0;
$epp = 1; /* entries per page */

$l1 = $epp*($page);
$l2 = $epp;

$result = MYSQL_QUERY( "SELECT * FROM $table".$where_query." ORDER BY date LIMIT $l1, $l2");

if($page!=0){
print("<a href='$PHP_SELF?page=".($page-1)."&search=".$search. "&category=".$category."&subcategory=".$subcategory. "&picture=".$picture."&itemfor=".$itemfor."&locationcountry=". $locationcountry."' alt='Prev Page' title='Prev Page'>[ Prev ]</a>");
}
$totalpages =(int)( $xcount / $epp);
print(" Page # ".($page+1)." ");

if(($page)!=$totalpages && ($totalpages!=0)){

print("<a href='$PHP_SELF?page=".($page+1)."&search=".$search. "&category=".$category."&subcategory=".$subcategory. "&picture=".$picture."&itemfor=".$itemfor."&locationcountry=". $locationcountry."' alt='Next Page' title='Next Page'>[ Next ]</a>");
}

[edited by: jatar_k at 7:03 pm (utc) on Jan. 8, 2005]
[edit reason] fixed sidescroll [/edit]

[BlackRaven]

ok i think the problem lies with the $totalpage since if i maually input the page number i get all the rite values.

[jshpro2]

Where is $xcount getting it's value?

[BlackRaven]

$result = MYSQL_QUERY( "SELECT * FROM $table".$where_query." ORDER BY date LIMIT $l1, $l2");
$xcount = mysql_num_rows($result);

[dreamcatcher]

I think you need two queries, one to fetch the total number of rows fetched by your query, and the second with the LIMIT clauses.

[BlackRaven]

yeap thanks got it working now,

$forrowcount=MYSQL_QUERY( "SELECT * FROM $table".$where_query);
$xcount = mysql_num_rows($forrowcount);
$result = MYSQL_QUERY( "SELECT * FROM $table".$where_query." ORDER BY date LIMIT $l1, $l2");

just curious, it there no other way, i mean do i have to use to query? seems kinda waste?..but what do i know i guess...just another average engineer who moonlights as a programmer

[s1dev]

There are other ways:

You can leave off the LIMIT clause and loop from $¦1 to $¦2 when displaying your records. That way mysql_num_rows() will be the total number of records possible.

If you're going to use a second query to calculate the total number of records, I'd suggest replacing:
$forrowcount=MYSQL_QUERY( "SELECT * FROM $table".$where_query);
$xcount = mysql_num_rows($forrowcount);

with:
$countrslt=MYSQL_QUERY( "SELECT count(*) as xcount FROM $table".$where_query);
$rec = mysql_fetch_assoc($countrslt);
$xcount = $rec["xcount"];

It'll lessen traffic between the webserver and the database server.

[jshpro2]

s1dev,

The idea with looping from limi1 to limit2 is an excellent one, but I'd like to add that if your database has 700,000 records, it takes an awefull long time to SELECT *

For smaller databases your idea is excellent though