Starting my Pagination, Question on Limit

[Lamez]

I am starting my pagination script, and I am going to use a mysql query with limit

 

here is some code:

<?php
$r = mysql_query("SELECT `username` FROM `users` LIMIT 10");
while($rs = mysql_fetch_array($r)){
echo $rs['username'];
}
?>

 

selects the first 10 users, but how do I select the next 10 users?

[rofl90]

Heres my pagination script

getting news.

<?php
$page = isset($_GET['page']) && is_numeric($_GET['page']) && $_GET['page'] >= 1 ? $_GET['page'] : 1;

$per_page = 3;

$count_q = mysql_query("SELECT COUNT(id) FROM news");
$count_r = mysql_fetch_row($count_q);
$count = $count_r[0];

if($page > ceil($count/per_page)) $page = 1;


$limit_min = ($page-1)*$per_page;
$query = mysql_query("SELECT * FROM news ORDER BY id DESC LIMIT {$limit_min}, {$per_page}");
while($r = mysql_fetch_assoc($query)) {

$timestamp = stripslashes($r['time']);
$time = date('l, jS \of F Y G:iA', $timestamp);
$title = stripslashes($r['title']);
$text = stripslashes($r['text']);
$postedby = stripslashes($r['postedby']);

echo "<h1 class='titler'>$title<span class='byauthor'> by <font size='3px' color='#E6E6E6'>$postedby</font></span></h1>
<div class='newstext'><em>
$text
</em></div>
<br /><br />
<strong class='time'>Posted on $time</strong>";
}
}
?>

[Lamez]

wow you saved me a lot of trouble, but what does this line do:

 

$count_q = mysql_query("SELECT COUNT(id) FROM news");

[rofl90]

counts the id's in news

[Lamez]

cool, I think I can use it.

 

Thank you very much.

[Lamez]

it is not working

 

I am getting an error, it is:

 

Warning: Division by zero in /mounted-storage/home48c/sub007/sc33591-LWQU/uploads/Login System v.2.0/list.php on line 12

 

here is my new code:

 

<?php
include ("include/session.php");

$page = isset($_GET['page']) && is_numeric($_GET['page']) && $_GET['page'] >= 1 ? $_GET['page'] : 1;

$per_page = 1;

$count_q = mysql_query("SELECT COUNT(username) FROM ".TBL_USERS);
$count_r = mysql_fetch_row($count_q);
$count = $count_r[0];

if($page > ceil($count/per_page)) $page = 1;


$limit_min = ($page-1)*$per_page;
$query = mysql_query("SELECT * FROM ".TBL_USERS." ORDER BY username DESC LIMIT {$limit_min}, {$per_page}");
while($r = mysql_fetch_assoc($query)) {

echo $r['username'] ."<br>";
}
?>

 

am I doing somthing wrong?

[hassank1]

you are missing the '$' in :

if($page > ceil($count/per_page)) $page = 1;

 

it should be :

if($page > ceil($count/$per_page)) $page = 1;

[Lamez]

oh a stupid mistake, thanks it works now

 

[Lamez]

alright now I am trying to make the links

 

Prev Page [1] [2] [3] Next Page

 

I can make it go from page 1 to page 2, but that is about all, could someone give me some advice?

 

code:

<?php
include ("include/session.php");

$page = isset($_GET['page']) && is_numeric($_GET['page']) && $_GET['page'] >= 1 ? $_GET['page'] : 1;

$per_page = 1;

$count_q = mysql_query("SELECT COUNT(username) FROM ".TBL_USERS);
$count_r = mysql_fetch_row($count_q);
$count = $count_r[0];

if($page > ceil($count/$per_page)) $page = 1;


$limit_min = ($page-1)*$per_page;
$query = mysql_query("SELECT * FROM ".TBL_USERS." ORDER BY username DESC LIMIT {$limit_min}, {$per_page}");
while($r = mysql_fetch_assoc($query)) {

echo $r['username'] ."<br>";
}
$nxt = $page + 1;
$prv = $nxt - 2;
echo '<a href="?page='.$nxt.'">Next Page</a><br>';
echo '<a href="?page='.$prv.'">Prev Page</a>';
?>

[Lamez]

anyone know how do setup the links?

[moon 111]

Heres something that I wrote:

 

<?php
$q = "SELECT * FROM site";
$r = mysql_query($q);
$num = ceil(mysql_num_rows($r) / $limit2);
if($num != 1)
{
  for($i = 1; $i <= $num; $i++)
  {
    if($page / $limit2 != $i) echo "[<a href='index.php?p=list&page=".$i."'>".$i."</a>] ";
    else echo "[<b>".$i."</b>] ";
  }
}
?>

 

$limit2 is the number of page you want to display per page and $page is the current page.

Basicly it takes the number of pages that its displaying and deviding them into pages ($num = ceil...). Then it checks if there is more then one page (no need for pagnation with 1) and loops thru all of the pages echoing them

[Lamez]

aww this looks neat, so all I really need to do is change the query, and define $limit2?

[Lamez]

wow that worked great, except the bolding does not work too well, but I will take it out, thanks

 

final code:

<?php
include ("style/include/session.php");

$page = isset($_GET['page']) && is_numeric($_GET['page']) && $_GET['page'] >= 1 ? $_GET['page'] : 1;

$per_page = 10;

$count_q = mysql_query("SELECT COUNT(username) FROM ".TBL_USERS." ORDER BY username DESC");
$count_r = mysql_fetch_row($count_q);
$count = $count_r[0];

if($page > ceil($count/$per_page)) $page = 1;


$limit_min = ($page-1)*$per_page;
$query = mysql_query("SELECT * FROM ".TBL_USERS." ORDER BY username DESC LIMIT {$limit_min}, {$per_page}");
while($r = mysql_fetch_assoc($query)) {

echo $r['username'] ."<br>";
}


$limit2 = $per_page;

$q = "SELECT username FROM ".TBL_USERS;
$r = mysql_query($q);
$num = ceil(mysql_num_rows($r) / $limit2);
if($num != 1)
{
  for($i = 1; $i <= $num; $i++)
  {
    /*if($page / $limit2 != $i)*/ echo "[<a href='?p=list&page=".$i."'>".$i."</a>] ";
    //else echo "[<b>".$i."</b>] ";
  }
}
?>

 

however if I set the limit2 to 4, and click on page 4 it makes one disappear, well no biggy, thanks you guys very much!

[moon 111]

No problem. Enjoy!