Records Not Showing w/Page Navigation

[dnayana]

I'm close to finishing my first PHP application.

The problem I am having is when the user clicks on one of the navigation links (i.e. First, Next, Prev, Last), the records do not display -- it remains on the first 18 records. However, the page number url variable do change appropriately.

I would like to display all the records but broken up to display 18 recs per page.

I can not analyze why the records are not displaying. I'm using MS SQL Server 2000. Thru my readings, I know the "LIMIT" clause is not supported as it is within MySQL. So, I've researched the workaround to it for SQL Server and I thought I done it right since it displays 18 records, but not quite sure now, since it only stays on the first set of 18 records.

The following is my code:

<?
// how many rows to show per page
$introwsPerPage = 18;

// by default show first page
$intpageNum = 1;

// if $_GET['intpage'] defined, use it as page number
if(isset($_GET['intpage']))
{
	$intpageNum = $_GET['intpage'];
}

// counting the offset
$offset = ($intpageNum - 1) * $introwsPerPage;

	//connect to a DSN ('Name of DSN', 'UserName', 'Password') 
	//odbc_connect: Connect to a datasource 
	$conn = odbc_connect('NGAMATCandidate','sa','')
		or die('Unable to connect to database'); 
	
		//the SQL statement that will query the database 
		   $qryCandidate = 
		     "SELECT TOP 100 PERCENT * FROM (
		        SELECT TOP 18 ctrCandidate, txtLastName, txtFirstName FROM (
				   SELECT TOP 100 ctrCandidate, txtLastName, txtFirstName
                       FROM tblCandidate 
					     ORDER BY txtLastName, txtFirstName  ASC) AS newtbl
                ORDER BY txtLastName, txtFirstName DESC) AS newtbl2
              ORDER BY txtLastName, txtFirstName ASC";
		
				
		//perform the query 
		//odbc_exec: Prepare and execute a SQL statement 
		  $qryresult=odbc_exec($conn, $qryCandidate); 
	
		//retrieve the data from the database
		//odbc_fetch_row: Fetch a row 
		  while(@odbc_fetch_row($qryresult))
		  { 	
		  
			//odbc_result: Get result data
		  $ctrCandidate = odbc_result($qryresult, "ctrCandidate");
		  $txtLastName = odbc_result($qryresult, "txtLastName");
		  $txtFirstName = odbc_result($qryresult, "txtFirstName");		  

			//Display Results
			echo ("<tr bgcolor=\"$color\">");
			echo ("<td height=\"18\">$txtLastName, $txtFirstName</td>");		
			echo ("<td><a href=\"candidateadmin.php?ctrCandidate=$ctrCandidate\"><div align=\"center\">[Edit]</div></a></td>");

			echo ("</tr>");	  	
			}
			
// how many rows we have in database
$querycount   = "SELECT COUNT(ctrCandidate) AS intnumrows FROM tblCandidate";
$result  = odbc_exec($conn, $querycount);
$row = odbc_fetch_array($result);
$intnumrows = $row['intnumrows'];

// how many pages we have when using paging?
$intmaxPage = ceil($intnumrows/$introwsPerPage);

// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav = '';
for($intpage = 1; $intpage <= $intmaxPage; $intpage++)
{
	if ($page == $intpageNum)
	{
		$nav .= " $intpage ";   // no need to create a link to current page
	}
	else
	{
		$nav .= " <a href=\"$self?intpage=$intpage\">$page</a> ";
	}		
}

// creating previous and next link
// plus the link to go straight to
// the first and last page

if ($intpageNum > 1)
{
	$intpage = $intpageNum - 1;
	$prev = " <a href=\"$self?intpage=$intpage\">[Prev]</a>&nbsp; ";
	
	$first = " <a href=\"$self?intpage=1\">[First Page]</a>&nbsp; ";
} 
else
{
	$prev  = '&nbsp;'; // we're on page one, don't print previous link
	$first = '&nbsp;'; // nor the first page link
}

if ($intpageNum < $intmaxPage)
{
	$intpage = $intpageNum + 1;
	$next = " <a href=\"$self?intpage=$intpage\">[Next]</a>&nbsp; ";
	
	$last = " <a href=\"$self?intpage=$intmaxPage\">[Last Page]</a>&nbsp; ";
} 
else
{
	$next = '&nbsp;'; // we're on the last page, don't print next link
	$last = '&nbsp;'; // nor the last page link
}

			//Display Navigation
			echo ("<tr>");			
			echo ("<td colspan=\"5\" align=\"center\">");
			echo ("<br/><br/>$first $prev $next $last");
			echo ("<br/><br/>");
			echo ("<center>");
			echo ("<A HREF=\"index.php\">Return to HomePage</a>");
			echo ("</center>");
			echo ("</td>");
			echo ("</tr>");
			echo ("</table>");			

			//disconnect the db
			odbc_close($conn);
?>

Thank you in advance for any assistance!

Nicole

 

[Borvik]

I've been using RC4PHP to make paging in MSSQL easier - it was a lot easier than trying to nest SQL statements and reverse sorts to get the pages to come out properly.

I believe RC4PHP takes your SQL statement and makes a paging procedure out of it - which it then runs.

http://sourceforge.net/projects/rc4php/

 

[dnayana]

Thank you Borvik!

I will take a look at it.