need another help with query

[kjoker]

i've gone php crazy.. 😃

anyway.. i'm trying to create a site navigation on my news site. the query code is:

this is for displaying the result on the page
$query1 = "SELECT * FROM news ORDER BY id DESC LIMIT $offset, $rowsPerPage";

this is to count the rows.
$query2 = "SELECT COUNT(id) AS numrows FROM news";

this works fine with indexing all of the entries in the table. What I'm trying to do is to index the site by article category. So I modified the query code:

this is for displaying the result on the page
$query1 = "SELECT * FROM news WHERE category='$cat' ORDER BY id DESC LIMIT $offset, $rowsPerPage";

this is to count the rows
$query2 = "SELECT count(id) AS numrows FROM news where category='$cat'";

the page displayed fine at first but when i click the next button...the next page is empty..i click on previous..it's also empty.

I'm stumped so I'm turning to the experts here.
😕
Thanks

[leatherback]

How are you retrieving the variables from the link?

Are you using a construct like :

$offset = $_GET['offset']; ?

J.

[kjoker]

offset is defined by ($pageNum - 1) * $rowsPerPage;
here's my code:

$rowsPerPage = 5; 
$pageNum = 1; 
if(isset($_GET['page'])) 
{ 
    $pageNum = $_GET['page']; 
} 
$offset = ($pageNum - 1) * $rowsPerPage; 
$cat = $_GET['category'];

$query1 here<
while loop here<
$query2 here<

$row2     = mysql_fetch_array($result2, MYSQL_ASSOC); 
$numrows = $row2['numrows']; 
$maxPage = ceil($numrows/$rowsPerPage); 
$self = $_SERVER['PHP_SELF']; 
if ($pageNum > 1) 
{ 
    $page = $pageNum - 1; 
    $prev = " <a href=\"$self?page=$page\">[Prev]</a> "; 

$first = " <a href=\"$self?page=1\">[First Page]</a> "; 
} 
else 
{ 
    $prev  = ' [Prev] ';       

    $first = ' [First Page] ';  

} 
if ($pageNum < $maxPage) 
{ 
    $page = $pageNum + 1; 
    $next = " <a href=\"$self?page=$page\">[Next]</a> "; 
    $last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> "; 
} 
else 
{ 
    $next = ' [Next] ';      

    $last = ' [Last Page] ';
} 

echo $first . $prev . " Showing page <strong>$pageNum</strong> of <strong>$maxPage</strong> pages " . $next . $last; 

thanks 🙂

[liquorvicar]

are you sure the second page should be picking up any results?

try echo-ing out your query and running it against the database directly to check for any errors.

kudose - would that work with a query with a LIMIT clause? i'd have thought that it would return the LIMIT number of rows, not the total without the LIMIT (which i believe is what the poster is after).

[kjoker]

yep. if there is no second page...the link next would be greyed out and there's no link.

it calculates correctly. the category that i want to view has 13 entries. i set the limit to 10..and it said page 1 out of 2.

I added this query after the first query:
if(mysql_num_rows($result) == 0){
echo("Nothing to Display!");
}

and on the second page..it said Nothing to display. I guess this is the problem. So somehow the result is set back to = 0???

EDIT AGAIN: ROFLMAO omg... silly me...
classic PEBKAC..\

I forgot to put the category=$category...
<a href=\"$self?category=$category&page=$page\">[Prev]</a>

so the page is like viewnews.php?page=2
duh... of course it doesn't work... now it works just fine. you know..the problem always lies on something that is really simple...

I was also having a problem with my index.php pagination..and the problem was that I close mysql...so pagination doesn't work.

EDIT EDIT AGAIN: forgot to say thank you 😃. Thanks guys.