Jump to content

Pagnation problem

New Coder

By New Coder
in PHP Coding Help

 Share

Recommended Posts

New Coder Member

New Coder

Posted
Posted

Hello All,

 

I followed the very good tutorial from this site to allow me to paginate my pages.

I have managed to get it working but not as I imagined so I think something is slightly wrong with my interperation.

 

I have the number of items to show set to 15. If the number of items retrned is 16 i expected the first page to display the first 15 rows and the second page to display the last and only single row. At the moment it displays the first 15 on page 1 and the last 15 on page 2.

Is this how it should work or have I coded incorrectly.

 

$sql = "SELECT COUNT(*) FROM members";

$result = mssql_query( $sql, $conn ) 
	or die( "Err: EXE" );

$r = mssql_fetch_row($result);
$numrows = $r[0];

// number of rows to show per page
$rowsperpage = 15;
// find out total pages
$totalpages = ceil($numrows / $rowsperpage);

// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage']))
{   
	// cast var as int   
	$currentpage = (int) $_GET['currentpage'];
} 
else
{   
	// default page num   
	$currentpage = 1;
} // end if

// if current page is greater than total pages...
if ($currentpage > $totalpages) 
{  
 	// set current page to last page   
	 $currentpage = $totalpages;
 } // end if
 // if current page is less than first page...
 if ($currentpage < 1) 
 {   
	 // set current page to first page   
	 $currentpage = 1;
 } // end if

 // the offset of the list, based on current page 
 $offset = ($currentpage) * $rowsperpage;

 /******  build the pagination links (repeated below so displayed top and bottom of page)******/
 $range = 3;

 // if not on page 1, don't show back links
 if ($currentpage > 1) 
 {   
	 // show << link to go back to page 1   
	 echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<[First]</a> ";   
	 // get previous page num   
 	$prevpage = $currentpage - 1;   
 	// show < link to go back to 1 page   
	 echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><[Prev]</a> ";
 } // end if 

 // loop to show links to range of pages around current page
 for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) 
 {  
 	 // if it's a valid page number...  
  	 if (($x > 0) && ($x <= $totalpages)) 
   	{     
    	// if we're on current page...      
		if ($x == $currentpage) 
		{         
		// 'highlight' it but don't make a link        
		 echo " [<b>$x</b>] ";      
		 // if not current page...      
		 } 
		 else 
		 {        
	  // make it a link         
	 	 echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";     
	  	 } // end else   
	  } // end if 
	 } // end for                 
	   
	   // if not on last page, show forward and last page links        
	   if ($currentpage != $totalpages) {  
	    // get next page  
		 $nextpage = $currentpage + 1;    
		 // echo forward link for next page   
		  echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>[Next]></a> ";   
		  // echo forward link for lastpage   
		  echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>[Last]>></a> ";
		  } // end if
		  /****** end build pagination links ******/
		  
 // get the info from the db 
 $sqlnum = "SELECT * FROM ( 
 			SELECT TOP $rowsperpage * FROM ( 
				SELECT TOP $offset * FROM members order by member_id ASC) 
			AS newtbl ORDER BY member_id DESC ) 
		AS newtbl2 ORDER BY member_id ASC";
$result = mssql_query( $sqlnum, $conn ) 
	or die( "ERR: EXE" );

$num = mssql_num_rows ($result);
if ($num !=0)
{		
while ($row = mssql_fetch_assoc($result)) 
{
	$student_name = rtrim($row["forename"])." ".rtrim($row["surname"]);
	$member_id = $row["member_id"];
	$profile = nl2br($row["profile"]);
	$list .= "<tr>";
	$list .= "<th class=table colspan=2>".$member_name." (".$memebr_id.")</th>";
	$list .= "</tr><tr>";
	if ($row["profile"] == NULL)
	{
		$list .= "<td class=table><font color=\"red\">A profile has not been saved for this member</font></td>";
	}
	else
	{
		$list .= "<td class=tblleft>$profile</td>";
	}
	$list .= "<td class=table><form action=\"personal_details.php\" method=\"post\"><input type=\"hidden\" name=\"member_id\" value=\"$member_id\"><input type=\"submit\" value=\"View Detail\" class=\"submit\"></form></td>";
	$list .= "</tr><tr>";	
}
$list .= "</table>";

echo( $list );

 /******  build the pagination links ******/
 // if not on page 1, don't show back links
 if ($currentpage > 1) 
 {   
	 // show << link to go back to page 1   
	 echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<[First]</a> ";   
	 // get previous page num   
 	$prevpage = $currentpage - 1;   
 	// show < link to go back to 1 page   
	 echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><[Prev]</a> ";
 } // end if 

 // loop to show links to range of pages around current page
 for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) 
 {  
 	 // if it's a valid page number...  
  	 if (($x > 0) && ($x <= $totalpages)) 
   	{     
    	// if we're on current page...      
		if ($x == $currentpage) 
		{         
		// 'highlight' it but don't make a link        
		 echo " [<b>$x</b>] ";      
		 // if not current page...      
		 } 
		 else 
		 {        
	  // make it a link         
	 	 echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";     
	  	 } // end else   
	  } // end if 
	 } // end for                 
	   
	   // if not on last page, show forward and last page links        
	   if ($currentpage != $totalpages) {  
	    // get next page  
		 $nextpage = $currentpage + 1;    
		 // echo forward link for next page   
		  echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>[Next]></a> ";   
		  // echo forward link for lastpage   
		  echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>[Last]>></a> ";
		  } // end if
		  /****** end build pagination links ******/
}
else
{ 
echo ("You do not have any Members");
}

 

Many Thanks

Link to comment
Share on other sites
harkly Regular Member

harkly

Posted
Posted

Is your code working at all? I ask becuase you have a few spelling errors that may be causing serious issues. For example

 

mssql_query should be mysql_query

 

$member_id vs $memebr_id

 

I'm working on the same pagination tutorial as well, you can check out what I have done here. I'm not having the issue you are working on.

http://www.phpfreaks.com/forums/index.php/topic,312808.0.html

Link to comment
Share on other sites
New Coder Member

New Coder

Posted
  • Author
Posted

As pikachu says we are using mssql not mysql. The other item was a typo that just prevented a part of the row from displaying, thats sorted now.

 

The code works...ish. Or at least in a fashion I wasn't expecting.

The problem is it will always show 15 results on each page. So for 25 rows returned, page 1 will display records 1 to 15 and page 2 will display records 11 to 25. I would have thought page 2 should display 16 to 25.  Should this be the case?

 

Thanks

Link to comment
Share on other sites
Username: Member

Username:

Posted
Posted

My way is alot less professional but can give a good result providing you have skill in PHP.

 

 

$readfrom = mysql_real_escape_string($_GET['readfrom']);

$readto = mysql_real_escape_string($_GET['readto']);

$tbl = "table"; //Table in DB

$result = mysql_query("SELECT * FROM $tbl LIMIT $readfrom,$readto");

 

 

Link to comment
Share on other sites
New Coder Member

New Coder

Posted
  • Author
Posted

I have found the problem is with the mssql query. As mssql does not support or really have an equivlient to the LIMIT function I had to re-write the query.

I have found a quick and dirty solution till I can re-write the query to perform better and correctly.

 

I substituted:

 // if not on last page, show forward and last page links        		   if ($currentpage != $totalpages) {  		    // get next page  			 $nextpage = $currentpage + 1;    			 // echo forward link for next page   			  echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>[Next]></a> ";   			  // echo forward link for lastpage   			  echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>[Last]>></a> ";			  } // end if			  /****** end build pagination links ******/			  	 // get the info from the db 	 $sqlnum = "SELECT * FROM ( 	 			SELECT TOP $rowsperpage * FROM ( 					SELECT TOP $offset * FROM members order by member_id ASC) 				AS newtbl ORDER BY member_id DESC ) 			AS newtbl2 ORDER BY member_id ASC";

 

 

with

 

 

// if not on last page, show forward and last page links        
	   if ($currentpage != $totalpages) {  
	    // get next page  
		 $nextpage = $currentpage + 1;    
		 // echo forward link for next page   
		  echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>[Next]></a> ";   
		  // echo forward link for lastpage   
		  echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>[Last]>></a> ";
		  // get the info from the db
		 $sqlnum = "SELECT * FROM ( 	 			SELECT TOP $rowsperpage * FROM ( 					SELECT TOP $offset * FROM members order by member_id ASC) 				AS newtbl ORDER BY member_id DESC ) 			AS newtbl2 ORDER BY member_id ASC";
		  } 
		  else
		  {
		  	// get the info from the db
			$remainder = $numrows - (($totalpages - 1) * $rowsperpage) ;
			$sqlnum = "SELECT * FROM ( 	 			SELECT TOP $rowsperpage * FROM ( 					SELECT TOP $offset * FROM members order by member_id ASC) 				AS newtbl ORDER BY member_id DESC ) 			AS newtbl2 ORDER BY member_id ASC";
		   }// end if

 

and it works for now.

 

Thanks

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.