Page 1 of 1

20 per page, generate the right number of pages

Posted: Fri Aug 16, 2002 7:53 am
by stickman373
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 :roll:

Posted: Fri Aug 16, 2002 9:00 am
by fatalcure

Posted: Fri Aug 16, 2002 9:57 am
by stickman373
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

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&#1111;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) &#123; 
      $pagesvar = ""; 
      if ($page - 3 > 0) $pagesvar .= "<a href='?page=1'><u><<</u></a> &nbsp;&nbsp;"; 
      if ($prev > 0 && $prev <= $numpages) $pagesvar .= "<a href='?page=$prev'><u><</u></a> &nbsp;&nbsp;"; 

      for ($i=$page-2; $i < $page; $i++) &#123; 
            if ($i > 0) $pagesvar .= "<a href='?page=$i'><u>$i</u></a> &nbsp;&nbsp;"; 
      &#125; 

      $pagesvar .= "<a>$page</a> &nbsp;&nbsp;"; 

      for ($i=$page+1; $i <= $page+2; $i++) &#123; 
            if ($i <= $numpages) $pagesvar .= "<a href='?page=$i'><u>$i</u></a> &nbsp;&nbsp;"; 
      &#125; 

      if ($next <= $numpages && $next > 0) $pagesvar .= "<a href='?page=$next'><u>></u></a> &nbsp;&nbsp;"; 
      if ($page + 3 <= $numpages) $pagesvar .= "<a href='?page=$numpages'><u>>></u></a> "; 
&#125; 


$query2 = "SELECT title FROM movie ORDER BY title DESC LIMIT $limit,$perpage"; 
$result2 = mysql_query($query2); 
while ($row = mysql_fetch_array($result2)) &#123; 
     
echo $record&#1111;'title'];

&#125; 
mysql_free_result($result2); 

?>

Posted: Fri Aug 16, 2002 3:04 pm
by gotDNS
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)
&#123;
	if($sortdir == "asc")
	&#123;
		echo "<option value='asc' selected='selected'>Ascending Order</option>";
	&#125;
	else
	&#123;
		echo "<option value='asc'>Ascending Order</option>";
	&#125;

	if($sortdir == "desc")
	&#123;
		echo "<option value='desc' selected='selected'>Descending Order</option>";
	&#125;
	else
	&#123;
		echo "<option value='desc'>Descending Order</option>";
	&#125;
&#125;
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)) &#123;                       //
	$totalpoems = $row&#1111;"count(1)"];   //Counts the number of entries to be displayed

&#125;//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
&#123;

  if($setnum>0)
  &#123;
   $newset=$setnum-1;
   $newpage=$newset*$perset;
     echo "<a href='poetry.php?sortdir=$sortdir&pagenum=$newpage' class='PAGES'>Prev Set | </a>";
  &#125;
  if($pagenum>0)
  &#123;
    $newpage=$pagenum-1;
	echo "<a href='poetry.php?sortdir=$sortdir&pagenum=$newpage' class='PAGES'>Prev</a> -";
  &#125;//end of if

    $page=$setnum*$perset;

	while($page<$totalpages && $page<($setnum+1)*$perset)
	&#123; 
	 
	  if($page==$pagenum)
	  &#123;
		$page++;
		echo " <b class='WHITE'>$page</b>";
	  &#125;
	  else
	  &#123;
	    
	    echo"<a href='poetry.php?sortdir=$sortdir&pagenum=$page' class='PAGES'> ";
	    $page++;
		echo "$page</a>";
	  &#125;

      
	&#125;//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)
  &#123;
   $newset=$setnum+1;
   $newpage=$newset*$perset;
     echo "<a href='poetry.php?sortdir=$sortdir&pagenum=$newpage' class='PAGES'> | Next Set</a>";
  &#125;
&#125;//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)) &#123;
	$titles&#1111;] = $row&#1111;"title"];    //get the title of each poem in a loop
	$nums&#1111;] = $row&#1111;"num"];    // get s the corrosponding number so that a link can be made with the GET method
	$times&#1111;] = $row&#1111;"time"];   // get timestamp to echo
	$users&#1111;] = $row&#1111;"user"];   // get the author to echo
&#125; //end of while	



$count=$perpage*($pagenum);
$listend=$count+$perpage;

while($count<$listend) 
&#123;
   
    if($count>=$totalpoems)
         break;

	echo "<tr><td class='TITLECOL'><a href='read.php?num=$nums&#1111;$count]'>$titles&#1111;$count]</a></td>";   //makes the link
	
	if(date("j", $times&#1111;$count])%10 == 1 && floor(date("j", $times&#1111;$count])/10) != 1)
		$ending="st";
	else if(date("j", $times&#1111;$count])%10 == 2 && floor(date("j", $times&#1111;$count])/10) != 1)
		$ending="nd";
	else if(date("j", $times&#1111;$count])%10 == 3 && floor(date("j", $times&#1111;$count])/10) != 1)
		$ending="rd";
	else
		$ending="th";

	echo "<td class='TIMESTAMP'><b>Posted:</b> ".date("n/j", $times&#1111;$count]).date("/y", $times&#1111;$count])." <b>at</b> ".date("g:i A", $times&#1111;$count])." <b>by</b> ";

	if(session_is_registered("loggedin") && $users&#1111;$count] == $loggedin)
	&#123;
		echo "<a href='usrpoems.php?author=$users&#1111;$count]'><b>".$users&#1111;$count]."</b></a>";
	&#125;
	else
	&#123;
		echo"<a href='usrpoems.php?author=$users&#1111;$count]'>".$users&#1111;$count]."</a>";
	&#125;

if(session_is_registered("loggedin") && $users&#1111;$count] == $loggedin)
&#123;
	echo "<td class='ED'><form action='edit.php' method='post'><input type='hidden' value='$users&#1111;$count]' name='user' /><input type='hidden' value='$nums&#1111;$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&#1111;$count]' name='user' /><input type='hidden' value='$nums&#1111;$count]' name='num' /><input type='image' src='delete.gif'class='BUTTON' value='Delete' /></form></td></tr>";
&#125;

echo "</td></tr>";
        
	$count++; 

&#125;




echo "</table>";

if(!$titles)
&#123;
	echo "<table cellpadding='0' cellspacing='0' class='ERROR' align='center'><tr><td>Sorry, there are currently no poems.</td></tr></table>";
&#125;

include "bprint.php";

$prevurl = "poetry.php";
session_register("prevurl");
?>

Posted: Fri Aug 16, 2002 6:15 pm
by stickman373
being a php newbie, i'm lost when looking at that code you posted, if someone could provide a clearer example or help me fix the code i posted I would appreciate it A ton, this is one of the last things i need for a project i'm working on 8)

Posted: Fri Aug 16, 2002 7:21 pm
by fatalcure
stickman, change this line:

Code: Select all

echo $record&#1111;'title'];
to

Code: Select all

echo $row&#1111;'title'];
and make sure you echo $pagesvar somewhere so it'll show you the next-prev buttons.

Posted: Fri Aug 16, 2002 8:56 pm
by stickman373
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&#1111;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) &#123; 
      $pagesvar = ""; 
      if ($page - 3 > 0) $pagesvar .= "<a href='?page=1'><u><<</u></a> &nbsp;&nbsp;"; 
      if ($prev > 0 && $prev <= $numpages) $pagesvar .= "<a href='?page=$prev'><u><</u></a> &nbsp;&nbsp;"; 

      for ($i=$page-2; $i < $page; $i++) &#123; 
            if ($i > 0) $pagesvar .= "<a href='?page=$i'><u>$i</u></a> &nbsp;&nbsp;"; 
      &#125; 

      $pagesvar .= "<a>$page</a> &nbsp;&nbsp;"; 

      for ($i=$page+1; $i <= $page+2; $i++) &#123; 
            if ($i <= $numpages) $pagesvar .= "<a href='?page=$i'><u>$i</u></a> &nbsp;&nbsp;"; 
      &#125; 

      if ($next <= $numpages && $next > 0) $pagesvar .= "<a href='?page=$next'><u>></u></a> &nbsp;&nbsp;"; 
      if ($page + 3 <= $numpages) $pagesvar .= "<a href='?page=$numpages'><u>>></u></a> "; 
&#125; 

$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)) &#123; 
     echo $row&#1111;'title'];
&#125; 
echo $pagesvar; 
mysql_free_result($result2); 
?>

Posted: Fri Aug 16, 2002 9:44 pm
by fatalcure
how many records do you have in the table? Are there more than 2? Because there has to be more than 1 page for it to show up.

Posted: Fri Aug 16, 2002 10:10 pm
by stickman373
yes there are 7 records

Posted: Sat Aug 17, 2002 8:41 am
by fatalcure
well uhh...no erros or nething??
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&#1111;$totalresult);
$total = $total&#1111;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) &#123; 
      $pagesvar = ""; 
      if ($page - 3 > 0) $pagesvar .= "<a href='?page=1'><u><<</u></a> &nbsp;&nbsp;"; 
      if ($prev > 0 && $prev <= $numpages) $pagesvar .= "<a href='?page=$prev'><u><</u></a> &nbsp;&nbsp;"; 

      for ($i=$page-2; $i < $page; $i++) &#123; 
            if ($i > 0) $pagesvar .= "<a href='?page=$i'><u>$i</u></a> &nbsp;&nbsp;"; 
      &#125; 

      $pagesvar .= "<a>$page</a> &nbsp;&nbsp;"; 

      for ($i=$page+1; $i <= $page+2; $i++) &#123; 
            if ($i <= $numpages) $pagesvar .= "<a href='?page=$i'><u>$i</u></a> &nbsp;&nbsp;"; 
      &#125; 

      if ($next <= $numpages && $next > 0) $pagesvar .= "<a href='?page=$next'><u>></u></a> &nbsp;&nbsp;"; 
      if ($page + 3 <= $numpages) $pagesvar .= "<a href='?page=$numpages'><u>>></u></a> "; 
&#125; 

$query2 = "SELECT title FROM movie ORDER BY title DESC LIMIT $limit,$perpage"; 
$result2 = mysql_query($query2); 
while ($row = mysql_fetch_array($result2)) &#123; 
     echo $row&#1111;'title']; 
&#125; 
mysql_free_result($result2); 

echo "Total Records: <b>$total</b> ---- Total Pages: <b>$numpages</b><br>";
echo $pagesvar; 
?>
tell me what $total and $numpages echos out for u

Posted: Sat Aug 17, 2002 8:52 am
by stickman373
Parse error: parse error in test.php on line 7

Posted: Sat Aug 17, 2002 8:53 am
by fatalcure
and how many of those $row['title'] are echoed??

are any of them echoed?

Posted: Sat Aug 17, 2002 8:58 am
by stickman373
here is what i got now

Total Records: ---- Total Pages: 0

Posted: Sat Aug 17, 2002 9:00 am
by stickman373
hey i fixed it :D

This:

Code: Select all

$totalquery = "SELECT COUNT (title) FROM movie ORDER BY title";
should be:

Code: Select all

$totalquery = "SELECT * FROM movie ORDER BY title";
now it works, thanks for your help

Posted: Sat Aug 17, 2002 9:03 am
by fatalcure
uhm, i dont see how that would work and the count not....the * selects all the records, but now you dont have the count of how many TOTAL records are in the table. unless you did a COUNT(*);