Hope you understand what i mean
20 per page, generate the right number of pages
Moderator: General Moderators
-
stickman373
- Forum Commoner
- Posts: 30
- Joined: Mon Jul 22, 2002 10:26 am
20 per page, generate the right number of pages
How can i list the first 20 records from a mysql database and then the next 20 on the next page and so on? so that the link would be something like http://www.test.com/script.php?page=1 and that would show the first 20 and then the next 20 would be http://www.test.com/script.php?page=2. But at the bottom of the page it would list all the page numbers that will contain records so if I have 45 records in the database then it would list 3 pages that would be viewable, but if I have 74 records in it then it would list 4 pages being available?
Hope you understand what i mean
Hope you understand what i mean
look at this: http://www.devnetwork.net/forums/viewto ... 0768#10768
-
stickman373
- Forum Commoner
- Posts: 30
- Joined: Mon Jul 22, 2002 10:26 am
i tried to use the code with my database and just get a blank page, could you look this over please and see if you think anything is wrong? Thanks
http://members.lycos.co.uk/stickman373/test.php?page=1
http://members.lycos.co.uk/stickman373/test.php?page=1
Code: Select all
<?php
$connection = mysql_connect("localhost","stickman373","******");
mysql_select_db("stickman373_uk_db");
$totalquery = "SELECT COUNT (title) FROM movie ORDER BY title";
$totalresult = mysql_fetch_row(mysql_query($totalquery));
$total = $totalresultї0]; // EDIT!
mysql_free_result($totalresult);
$perpage = 2;
if (!$page || !is_numeric($page)) $page=1;
$limit = ($page - 1) * $perpage;
$numpages = ceil($total / $perpage);
$prev = $page - 1;
$next = $page + 1;
if ($numpages > 1) {
$pagesvar = "";
if ($page - 3 > 0) $pagesvar .= "<a href='?page=1'><u><<</u></a> ";
if ($prev > 0 && $prev <= $numpages) $pagesvar .= "<a href='?page=$prev'><u><</u></a> ";
for ($i=$page-2; $i < $page; $i++) {
if ($i > 0) $pagesvar .= "<a href='?page=$i'><u>$i</u></a> ";
}
$pagesvar .= "<a>$page</a> ";
for ($i=$page+1; $i <= $page+2; $i++) {
if ($i <= $numpages) $pagesvar .= "<a href='?page=$i'><u>$i</u></a> ";
}
if ($next <= $numpages && $next > 0) $pagesvar .= "<a href='?page=$next'><u>></u></a> ";
if ($page + 3 <= $numpages) $pagesvar .= "<a href='?page=$numpages'><u>>></u></a> ";
}
$query2 = "SELECT title FROM movie ORDER BY title DESC LIMIT $limit,$perpage";
$result2 = mysql_query($query2);
while ($row = mysql_fetch_array($result2)) {
echo $recordї'title'];
}
mysql_free_result($result2);
?>A big example...AKA: an entire page from one of my websites:
Code: Select all
<?php
session_start();
mysql_connect("localhost:3306", "poet") && mysql_select_db("poetry")
or $failed = "Could not connect to database.";
//Set default values
//Defaulted to sort desc
if(!$sortdir || !($sortdir==asc || $sortdir==desc))
$sortdir=desc;
//PERPAGE/SET CONSTANTs ARE RIGHT HERE
//CHANGE THIS TO CHANGE THE NUMBER OF POEMS ALLOWED PER PAGE/SET
if(!$perpage)
$perpage=12;
if(!$perset)
$perset=20;
$pagenum=ceil($pagenum);
include "tprint.php";
echo "<table cellpadding='0' cellspacing='0' class='ARRANGE'>
<tr>
<td class='ARRANGETD1'>
<form action='poetry.php' method='get'>
<b>Arrange in:</b>
<select name='sortdir'>";
if($sortdir)
{
if($sortdir == "asc")
{
echo "<option value='asc' selected='selected'>Ascending Order</option>";
}
else
{
echo "<option value='asc'>Ascending Order</option>";
}
if($sortdir == "desc")
{
echo "<option value='desc' selected='selected'>Descending Order</option>";
}
else
{
echo "<option value='desc'>Descending Order</option>";
}
}
echo "</select> <b>by date</b> <input type='submit' value='Arrange' />
</form>
</td>
</tr>
</table>";
$listquery = "select count(1) from poems where type='o'"; //
$listresult = mysql_query($listquery); // All of this connects to a DB table, etc
while ($row = mysql_fetch_assoc($listresult)) { //
$totalpoems = $rowї"count(1)"]; //Counts the number of entries to be displayed
}//end of while
$totalpages=ceil($totalpoems/$perpage);
echo "<b>Total Poems:</b> $totalpoems";
echo "<br /><b>Page</b> ";
echo $pagenum+1;
echo " <b>of</b> $totalpages";
//Add other pages
echo "<center>";
echo "<b>Pages:</b><br />";
$totalsets=ceil($totalpages/$perset);
$setnum=floor($pagenum/$perset);
if($count=($pagenum * $perpage)<$totalpoems); //reset count
{
if($setnum>0)
{
$newset=$setnum-1;
$newpage=$newset*$perset;
echo "<a href='poetry.php?sortdir=$sortdir&pagenum=$newpage' class='PAGES'>Prev Set | </a>";
}
if($pagenum>0)
{
$newpage=$pagenum-1;
echo "<a href='poetry.php?sortdir=$sortdir&pagenum=$newpage' class='PAGES'>Prev</a> -";
}//end of if
$page=$setnum*$perset;
while($page<$totalpages && $page<($setnum+1)*$perset)
{
if($page==$pagenum)
{
$page++;
echo " <b class='WHITE'>$page</b>";
}
else
{
echo"<a href='poetry.php?sortdir=$sortdir&pagenum=$page' class='PAGES'> ";
$page++;
echo "$page</a>";
}
}//end of while
if($pagenum+1<$totalpages)
echo "- <a href='poetry.php?sortdir=$sortdir&pagenum=" . ($pagenum+1) . "' class='PAGES'> Next</a>";
if($setnum+1<$totalsets)
{
$newset=$setnum+1;
$newpage=$newset*$perset;
echo "<a href='poetry.php?sortdir=$sortdir&pagenum=$newpage' class='PAGES'> | Next Set</a>";
}
}//end of if
echo "</center>";
//END of adding new pages
echo "<table cellpadding='0' cellspacing='0' class='MYPOEMS'>";
$listquery = "select title, num, time, user from poems where type='o' order by num $sortdir";
$listresult = mysql_query($listquery); // Connects to another table without a count
while ($row = mysql_fetch_assoc($listresult)) {
$titlesї] = $rowї"title"]; //get the title of each poem in a loop
$numsї] = $rowї"num"]; // get s the corrosponding number so that a link can be made with the GET method
$timesї] = $rowї"time"]; // get timestamp to echo
$usersї] = $rowї"user"]; // get the author to echo
} //end of while
$count=$perpage*($pagenum);
$listend=$count+$perpage;
while($count<$listend)
{
if($count>=$totalpoems)
break;
echo "<tr><td class='TITLECOL'><a href='read.php?num=$numsї$count]'>$titlesї$count]</a></td>"; //makes the link
if(date("j", $timesї$count])%10 == 1 && floor(date("j", $timesї$count])/10) != 1)
$ending="st";
else if(date("j", $timesї$count])%10 == 2 && floor(date("j", $timesї$count])/10) != 1)
$ending="nd";
else if(date("j", $timesї$count])%10 == 3 && floor(date("j", $timesї$count])/10) != 1)
$ending="rd";
else
$ending="th";
echo "<td class='TIMESTAMP'><b>Posted:</b> ".date("n/j", $timesї$count]).date("/y", $timesї$count])." <b>at</b> ".date("g:i A", $timesї$count])." <b>by</b> ";
if(session_is_registered("loggedin") && $usersї$count] == $loggedin)
{
echo "<a href='usrpoems.php?author=$usersї$count]'><b>".$usersї$count]."</b></a>";
}
else
{
echo"<a href='usrpoems.php?author=$usersї$count]'>".$usersї$count]."</a>";
}
if(session_is_registered("loggedin") && $usersї$count] == $loggedin)
{
echo "<td class='ED'><form action='edit.php' method='post'><input type='hidden' value='$usersї$count]' name='user' /><input type='hidden' value='$numsї$count]' name='num' /><input type='image' src='edit.gif'class='BUTTON' value='Edit' /></form></td><td class='ED2'><form action='delete.php' method='post'><input type='hidden' value='$usersї$count]' name='user' /><input type='hidden' value='$numsї$count]' name='num' /><input type='image' src='delete.gif'class='BUTTON' value='Delete' /></form></td></tr>";
}
echo "</td></tr>";
$count++;
}
echo "</table>";
if(!$titles)
{
echo "<table cellpadding='0' cellspacing='0' class='ERROR' align='center'><tr><td>Sorry, there are currently no poems.</td></tr></table>";
}
include "bprint.php";
$prevurl = "poetry.php";
session_register("prevurl");
?>-
stickman373
- Forum Commoner
- Posts: 30
- Joined: Mon Jul 22, 2002 10:26 am
stickman, change this line:
to
and make sure you echo $pagesvar somewhere so it'll show you the next-prev buttons.
Code: Select all
echo $recordї'title'];Code: Select all
echo $rowї'title'];-
stickman373
- Forum Commoner
- Posts: 30
- Joined: Mon Jul 22, 2002 10:26 am
ok i did that and they list and then i tried to echo the $pagesvar and it doesn't seem to show up? I echoed it at the end , but no show
Code: Select all
<?php
$connection = mysql_connect("localhost","stickman373","redman");
mysql_select_db("stickman373_uk_db");
$totalquery = "SELECT COUNT (title) FROM movie ORDER BY title";
$totalresult = mysql_fetch_row(mysql_query($totalquery));
$total = $totalresultї0]; // EDIT!
mysql_free_result($totalresult);
$perpage = 2;
if (!$page || !is_numeric($page)) $page=1;
$limit = ($page - 1) * $perpage;
$numpages = ceil($total / $perpage);
$prev = $page - 1;
$next = $page + 1;
if ($numpages > 1) {
$pagesvar = "";
if ($page - 3 > 0) $pagesvar .= "<a href='?page=1'><u><<</u></a> ";
if ($prev > 0 && $prev <= $numpages) $pagesvar .= "<a href='?page=$prev'><u><</u></a> ";
for ($i=$page-2; $i < $page; $i++) {
if ($i > 0) $pagesvar .= "<a href='?page=$i'><u>$i</u></a> ";
}
$pagesvar .= "<a>$page</a> ";
for ($i=$page+1; $i <= $page+2; $i++) {
if ($i <= $numpages) $pagesvar .= "<a href='?page=$i'><u>$i</u></a> ";
}
if ($next <= $numpages && $next > 0) $pagesvar .= "<a href='?page=$next'><u>></u></a> ";
if ($page + 3 <= $numpages) $pagesvar .= "<a href='?page=$numpages'><u>>></u></a> ";
}
$connection = mysql_connect("localhost","stickman373","redman");
mysql_select_db("stickman373_uk_db");
$query2 = "SELECT title FROM movie ORDER BY title DESC LIMIT $limit,$perpage";
$result2 = mysql_query($query2);
while ($row = mysql_fetch_array($result2)) {
echo $rowї'title'];
}
echo $pagesvar;
mysql_free_result($result2);
?>well uhh...no erros or nething??
try
tell me what $total and $numpages echos out for u
try
Code: Select all
<?php
$connection = mysql_connect("localhost","stickman373","redman");
mysql_select_db("stickman373_uk_db");
$totalquery = "SELECT COUNT (title) FROM movie ORDER BY title";
$totalresult = mysql_query($totalquery);
$total = mysql_fetch_rowї$totalresult);
$total = $totalї0];
mysql_free_result($totalresult);
$perpage = 2;
if (!$page || !is_numeric($page)) $page=1;
$limit = ($page - 1) * $perpage;
$numpages = ceil($total / $perpage);
$prev = $page - 1;
$next = $page + 1;
if ($numpages > 1) {
$pagesvar = "";
if ($page - 3 > 0) $pagesvar .= "<a href='?page=1'><u><<</u></a> ";
if ($prev > 0 && $prev <= $numpages) $pagesvar .= "<a href='?page=$prev'><u><</u></a> ";
for ($i=$page-2; $i < $page; $i++) {
if ($i > 0) $pagesvar .= "<a href='?page=$i'><u>$i</u></a> ";
}
$pagesvar .= "<a>$page</a> ";
for ($i=$page+1; $i <= $page+2; $i++) {
if ($i <= $numpages) $pagesvar .= "<a href='?page=$i'><u>$i</u></a> ";
}
if ($next <= $numpages && $next > 0) $pagesvar .= "<a href='?page=$next'><u>></u></a> ";
if ($page + 3 <= $numpages) $pagesvar .= "<a href='?page=$numpages'><u>>></u></a> ";
}
$query2 = "SELECT title FROM movie ORDER BY title DESC LIMIT $limit,$perpage";
$result2 = mysql_query($query2);
while ($row = mysql_fetch_array($result2)) {
echo $rowї'title'];
}
mysql_free_result($result2);
echo "Total Records: <b>$total</b> ---- Total Pages: <b>$numpages</b><br>";
echo $pagesvar;
?>-
stickman373
- Forum Commoner
- Posts: 30
- Joined: Mon Jul 22, 2002 10:26 am
Parse error: parse error in test.php on line 7
Last edited by stickman373 on Sat Aug 17, 2002 8:56 am, edited 1 time in total.
-
stickman373
- Forum Commoner
- Posts: 30
- Joined: Mon Jul 22, 2002 10:26 am
-
stickman373
- Forum Commoner
- Posts: 30
- Joined: Mon Jul 22, 2002 10:26 am
hey i fixed it
This:
should be:
now it works, thanks for your help
This:
Code: Select all
$totalquery = "SELECT COUNT (title) FROM movie ORDER BY title";Code: Select all
$totalquery = "SELECT * FROM movie ORDER BY title";