PHP paging/MySQL query limit

[speedy_rudolf]

Hi. I'm trying to make a sort of a forum, and this is the part of the code that needs to be changed, only I don't know how.

mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");
$sql="SELECT * FROM $tbl_name ORDER BY id DESC";
// OREDER BY id DESC is order result by descending 
$result=mysql_query($sql);
?>
<table width='50%' border="0" align="center" cellpadding="3" cellspacing="1">
<?php
$limit=10;
$page=0;
$numrows = mysql_num_rows($result);
$pages = intval($numrows/$limit);
if ($numrows % $limit) {
$pages++;}
$current = ($page/$limit) + 1;
if (($pages < 1) || ($pages == 0)) {
$total = 1;}
else {
$total = $pages;}
$result = mysql_query("SELECT * FROM $tbl_name ORDER BY id DESC LIMIT $page,$limit");
while($rows=mysql_fetch_array($result)){?>
<tr><td><font size="+2"><a href="view_topic.php?id=<? echo $rows['id']; ?>"><? echo $rows['topic']; ?></a><BR></font></td>
<td width='0*'><? echo $rows['datetime']; ?></td></tr>
<tr><td><? echo substr($rows['detail'], 0, 250)."..."; ?>  <a href="view_topic.php?id=<? echo $rows['id']; ?>"><? echo "Citeste mai mult";} ?></a>
</td></tr>
<tr><tr><td>
<?
if ($page != 0) { // Don't show back link if current page is first page.
$back_page = $page - $limit;
echo("<a href=\"$PHP_SELF?query=$query&page=$back_page&limit=$limit\">back</a>    \n");}
for ($i=1; $i <= $pages; $i++) // loop through each page and give link to it.
{
$ppage = $limit*($i - 1);
if ($ppage == $page){
echo("<b>$i</b>\n");} // If current page don't give link, just text.
else{
echo("<a href=\"$PHP_SELF?query=$query&page=$ppage&limit=$limit\">$i</a> \n");}
}
if (!((($page+$limit) / $limit) >= $pages) && $pages != 1) { // If last page don't give next link.
$next_page = $page + $limit;
echo("    <a href=\"$PHP_SELF?query=$query&page=$next_page&limit=$limit\">next</a>");}
?>
</td></tr></tr><?php
mysql_close();
?>
</table>

 

I made a database with 4 fields and 11 entries. The script shows the last 10 entries just as it should, but when I click on the 'next' button, nothing happens (it should show the first entry, but it just refreshes the page, and still the last 10 entries/1st page are shown). I think it has something to do with the link, but I can't figure out what exactly.

Can someone help, or at least give me a hint? Thanks in advance. Bye.

[diocyria]

$limit=10;
$page=0;
$numrows = mysql_num_rows($result);
$pages = intval($numrows/$limit);
if ($numrows % $limit) {
$pages++;}
$current = ($page/$limit) + 1; // all this will ever do is return the result of (0/$limit) + 1=1

// this following section is almost a waste, just use the value stored in $pages instead of making a whole new $total variable
if (($pages < 1) || ($pages == 0)) {  // $pages < 1 covers both scenarios, no need for the second $pages == 0 check
$total = 1;}
else {
$total = $pages;}
$result = mysql_query("SELECT * FROM $tbl_name ORDER BY id DESC LIMIT $page,$limit");

 

Now the query at the bottom of the quoted example above, this isn't really needed since your already pulling the records using a query above.  You can probably continue from the first query and create an array of records sized of $limit, storing each row appropriate to the current page.  As an example:

 

$startPgRow = $page * $limit; // may not be the exact calculation depending on your table setups (may also need to add 1 to $page value)
$curRow = 0; // could use a 1 starting value as well, which might be easier depending on above calculation/table setup

// both of the following may need to use a <= comparison instead, again depending on current value/table setups
while ($curRow < $startPgRow) { mysql_fetch_row($result); $curRow++; } // could do additional error checking
while ($curRow < ($startPgRow + $limit)) { $myPgResults[] = mysql_fetch_row($result); $curRow++; } // could do additional error checking and/or use mysql_fetch_assoc($result) depending on what/how you want to deal with it

// output row data, which you could also do directly using the last while() above, again depending on your preferences

 

I'm not totally sure if you can use the LIMIT clause of a MySQL query the way you are, I've only used it in terms of LIMIT <rowcount>.  Hope this helps!

[speedy_rudolf]

.......

Nope. Doesn't work. I tried copying the, changing the code, rewriting the code from scratch (using your code as a guideline), but still nothing. At best, it just takes me right back to my original problem, but most often it just gives me an error. Thanks anyway. Hope someone else can give me another idea.

[speedy_rudolf]

---

.......

Nope. Doesn't work. I tried copying the, changing the code, rewriting the code from scratch (using your code as a guideline), but still nothing. At best, it just takes me right back to my original problem, but most often it just gives me an error. Thanks anyway. Hope someone else can give me another idea.

 

I finally did it Here is the code

mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");
$sql="SELECT * FROM $tbl_name ORDER BY id DESC";
// OREDER BY id DESC is order result by descending 
$result=mysql_query($sql);
?>
<table width='50%' border="0" align="center" cellpadding="3" cellspacing="1">
<?php
$limit=10;
$page=0;
$numrows = mysql_num_rows($result);
$pages = intval($numrows/$limit);
if ($numrows % $limit) {
$pages++;}
$page=$_GET

---

;
$limitst=10;
if(empty($page)){
    $limit=$limitst;
    $oldlimit=0;
    $page=1;
}else{
    $limit=$page*$limitst;
    $oldlimit=$limit-$limitst;
}
$result = mysql_query("SELECT * FROM $tbl_name ORDER BY id DESC LIMIT ".$oldlimit.",".$limitst);
while($rows=mysql_fetch_array($result)){?>
<tr><td><font size="+2"><a href="view_topic.php?id=<? echo $rows['id']; ?>"><? echo $rows['topic']; ?></a><BR></font></td>
<td width='0*'><? echo $rows['datetime']; ?></td></tr>
<tr><td><? echo substr($rows['detail'], 0, 250)."..."; ?>  <a href="view_topic.php?id=<? echo $rows['id']; ?>"><? echo "Citeste mai mult";} ?></a>
</td></tr>
<tr><tr><td>
<?
if($pages == 1){
    echo "<b>1</b>";
}else{
for ($i=1; $i <= $pages; $i++) // loop through each page and give link to it.
{
	if($i == $page){
		echo("<b>$i</b>\n");} // If current page don't give link, just text.
	else{
		echo "<a href=\"$PHP_SELF?page=$i\">$i</a> \n";
	}
}
}
?>
</td></tr></tr><?php
mysql_close();
?>
</table>

 

Thank you very much every one for your ideas 

[WatsonN]

I'm glad you figured it out 

But can you mark this solved so people like me don't come and try to help only to find its been solved already 

 

Thanks