odbc sql php pagination query

lscruiser · Nov 2, 2009

The below script counts the number of entries in the database as a total and then displays 5 entries per page with Previous and Next buttons for pagination. The problem with this code is that the same 5 entries appear on every page. I am not sure about my SELECT statement on line 18 and the specifics on line 9 with GET. Your help would be greatly appreciated. Thank you in advance.

<?php
$db = odbc_connect('','','');  //put your connection here
function inv_rows($r1)  {
	ob_start(); 
	(int)$number=odbc_result_all($r1);
	ob_clean(); 
	return $number;
	}
	$page = isset($_GET["page"]) ? $_GET["page"] : 1;  //not sure of "page" text
	if(empty($page)){$page = 1; }			   

	$query = "SELECT * FROM plastic"; // name of table is plastic with columns ename and iname.	   

	$num_result = odbc_exec($db, $query);
	$numrows = inv_rows($num_result);			   	
	echo '<p>There are '.$numrows.' ideas.</p>';
	$limit = 5;
	$limitvalue = $page * $limit - ($limit);
	$limitnew = $limitvalue + $limit;
	$sql = "SELECT * from (SELECT TOP 5 * from (SELECT TOP 5 * from plastic ORDER BY ename, iname DESC) as table1 ORDER BY ename, iname DESC) as table2 order by ename, iname ASC";	  

	$result = odbc_exec($db, $sql);
	while(odbc_fetch_row($result)){		   	
?>
<table style="width: 600;">	
<tr>
<td style="width: 300; height: 25px;">Name:</td>
td style="width: 300; height: 25px;">Idea Name:</td>
</tr>				 
<tr>
<td style="width: 300; height: 25px;"><?php echo odbc_result($result, "ename"); ?></td>
<td style="width: 300; height: 25px;"><?php echo odbc_result($result, "iname"); ?></td>
</tr>
<tr>
<td colspan="5" style="height: 25px"><hr/></td>
</tr>
</table>
<?php //PREVIOUS AND NEXT BUTTONS
}
if($page !=1){
$pageprev = $page - 1;
echo "&nbsp;<strong><a href='?page=$pageprev'>PREV</a></strong>&nbsp;"; }
else{ echo "&nbsp;PREV&nbsp;"; }
$numofpages = $numrows / $limit;
for($i = 1; $i <= $numofpages; ++$i){
if($i == $page){ echo "&nbsp;[$i]&nbsp;"; }
else{ echo "&nbsp;<strong><a href='?page=$i'>$i</a></strong>&nbsp;"; }
}
if(($numrows % $limit) != 0){
if($i == $page){ echo "&nbsp;[$i]&nbsp;"; }
else{ echo "&nbsp;<strong><a href='?page=$i'>$i</strong></b>&nbsp;"; }
}
if(($numrows - ($limit * $page)) > 0){
$pagenext = $page + 1;
echo "&nbsp;<strong><a href='?page=$pagenext'>NEXT</a></strong>&nbsp;"; }
else{ echo "&nbsp;NEXT&nbsp;"; }
odbc_free_result($result);			
exit;
?>

Roger_Ramjet · Nov 3, 2009

Well if you are using crap MickeySoft databases like Access/JET then you can not paginate this way because they only have TOP n with no chance of an offset.

What you will have to do is to keep track of the first and last PRIMARY KEY on each page displayed. Then your query would be

// next 5
SELECT TOP 5 * FROM table WHERE id > $lastid ORDER BY id
//previous 5
SELECT * FROM (SELECT TOP 5 * FROM table WHERE id < $firstid ORDER BY id DESC) AS t ORDER BY id ASC

The way to store the first and last id is in session vars; though you can do it with hidden fields in the output, or complex values in the Previous/Next submit buttons. Personally I recommend session vars

lscruiser · Nov 3, 2009

My auto increment primary key is plastic_ID and the table name is plastic with two column names of ename and iname. I am not sure of the proper syntax and placement of the session vars of $lastid and $firstid in my code.

You gave me 2 SELECT statements. Can I ******* that your first SELECT statement replaces my $query statement and your second SELECT statement replaces my $sql statement? If not, I want to understand where to put your two SELECT statements. With plastic_ID as the primary key then the two statements you gave me would be:
$query = "SELECT TOP 5 FROM plastic WHERE plastic_ID > $lastid ORDER BY plastic_ID";
$sql = "SELECT FROM (SELECT TOP 5 FROM plastic WHERE plastic_ID < $firstid ORDER BY plastic_ID DESC) AS t ORDER BY plastic_ID ASC";
Would this be correct?
I have $page = isset($GET["page"]) ? $GET["page"] : 1; on line 9 and am not sure that "page" is correct.

I appreciate your reply. Thank you.

Roger_Ramjet · Nov 4, 2009

lscruiser;10932939 wrote:
My auto increment primary key is plastic_ID and the table name is plastic with two column names of ename and iname. I am not sure of the proper syntax and placement of the session vars of $lastid and $firstid in my code.

You gave me 2 SELECT statements. Can I ******* that your first SELECT statement replaces my $query statement and your second SELECT statement replaces my $sql statement? If not, I want to understand where to put your two SELECT statements. With plastic_ID as the primary key then the two statements you gave me would be:
$query = "SELECT TOP 5 FROM plastic WHERE plastic_ID > $lastid ORDER BY plastic_ID";
$sql = "SELECT FROM (SELECT TOP 5 FROM plastic WHERE plastic_ID < $firstid ORDER BY plastic_ID DESC) AS t ORDER BY plastic_ID ASC";
Would this be correct?
I have $page = isset($GET["page"]) ? $GET["page"] : 1; on line 9 and am not sure that "page" is correct.

I appreciate your reply. Thank you.

DO NOT USE GET
get creates dirty urls, so just use post for the form submit.

2 The previous and next buttons should be on SUBMIT buttons on seperate forms on the page so that the submit buttons POST the request, not dirty utl links sending GETS

if (isset($_POST['submit'])) {

  switch ($_POST['direction']) {
  	case 'next':
  		$query = "SELECT TOP 5 * FROM plastic WHERE plastic_ID > " . $_SESSION['lastid'] . " ORDER BY plastic_ID";
  		break;
  	case 'prev':
  		$query = "SELECT * FROM (SELECT TOP 5 * FROM plastic WHERE plastic_ID < " . $_SESSION['firstid'] . " ORDER BY plastic_ID DESC) AS t ORDER BY plastic_ID ASC";;
  		break;
  	default:
  		$query = "SELECT TOP 5 * FROM plastic ORDER BY plastic_ID";
  	}

 }

3 As you build your result display, you can store the id of the first result either in a session var, as I show, or you can put it into a hidden field in the Prev form. Likewise for the last id.

if (isset($_POST['submit'])) {

  switch ($_POST['direction']) {
  	case 'next':
  		$query = "SELECT TOP 5 * FROM plastic WHERE plastic_ID > " . $_POST['lastid'] . " ORDER BY plastic_ID";
  		break;
  	case 'prev':
  		$query = "SELECT * FROM (SELECT TOP 5 * FROM plastic WHERE plastic_ID < " . $_POST['firstid'] . " ORDER BY plastic_ID DESC) AS t ORDER BY plastic_ID ASC";;
  		break;
  	default:
  		$query = "SELECT TOP 5 * FROM plastic ORDER BY plastic_ID";
  	}

 }

Up to you really.

lscruiser · Nov 4, 2009

Thank you for your reply. I took your code and replaced it with my code. The result is seeing all entries (not 5 at a time as desired) and the pagination links do not link to the NEXT or PREV pages.
I appreciate your response and help.
Here is my updated code.

<?php
$db = odbc_connect('','',''); //put your connection here
function inv_rows($r1) {
ob_start();
(int)$number=odbc_result_all($r1);
ob_clean();
return $number;
}
$page = isset($POST["page"]) ? $POST["page"] : 1;
if(empty($page)){$page = 1; }
$query = "SELECT FROM plastic"; // name of table is plastic with columns plastic_ID, ename, and iname.

$num_result = odbc_exec($db, $query);
$numrows = inv_rows($num_result);

echo 'There are '.$numrows.' ideas.';
$limit = 5;
$limitvalue = $page $limit - ($limit);
$limitnew = $limitvalue + $limit;

if (isset($POST['submit'])) {
switch ($POST['direction']) {
case 'next':
$query = "SELECT TOP 5 FROM plastic WHERE plastic_ID > " . $SESSION['lastid'] . " ORDER BY plastic_ID";
break;
case 'prev':
$query = "SELECT FROM (SELECT TOP 5 FROM plastic WHERE plastic_ID < " . $SESSION['firstid'] . " ORDER BY plastic_ID DESC) AS t ORDER BY plastic_ID ASC";
break;
default:
$query = "SELECT TOP 5 FROM plastic ORDER BY plastic_ID";
}

}

$result = odbc_exec($db, $query);
while(odbc_fetch_row($result)){

?>
<table style="width: 600;">

<tr>
<td style="width: 300; height: 25px;">Name:</td>
<td style="width: 300; height: 25px;">Idea Name:</td>
</tr>

<tr>
<td style="width: 300; height: 25px;"><?php echo odbc_result($result, "ename"); ?></td>
<td style="width: 300; height: 25px;"><?php echo odbc_result($result, "iname"); ?></td>
</tr>
<tr>
<td colspan="5" style="height: 25px"><hr/></td>
</tr>
</table>
<?php //PREVIOUS AND NEXT BUTTONS
}
if($page !=1){
$pageprev = $page - 1;
echo " <a href='?page=$pageprev'>PREV</a> "; }
else{ echo " PREV "; }
$numofpages = $numrows / $limit;
for($i = 1; $i <= $numofpages; ++$i){
if($i == $page){ echo " [$i] "; }
else{ echo " <a href='?page=$i'>$i</a> "; }
}
if(($numrows % $limit) != 0){
if($i == $page){ echo " [$i] "; }
else{ echo " <a href='?page=$i'>$i "; }
}
if(($numrows - ($limit * $page)) > 0){
$pagenext = $page + 1;
echo " <a href='?page=$pagenext'>NEXT</a> "; }
else{ echo " NEXT "; }
odbc_free_result($result);

exit;
?>

Roger_Ramjet · Nov 4, 2009

<?php
$db = odbc_connect('','',''); //put your connection here
function inv_rows($r1) {
ob_start();
(int)$number=odbc_result_all($r1);
ob_clean();
return $number;
}
$page = isset($_POST["page"]) ? $_POST["page"] : 1;
if(empty($page)){$page = 1; }
$query = "SELECT * FROM plastic"; // name of table is plastic with columns plastic_ID, ename, and iname.
$num_result = odbc_exec($db, $query);
$numrows = inv_rows($num_result);
echo '<p>There are '.$numrows.' ideas.</p>';
$limit = 5;
$limitvalue = $page * $limit - ($limit);
$limitnew = $limitvalue + $limit;
if (isset($_POST['submit'])) {
switch ($_POST['direction']) {
case 'next':
$query = "SELECT TOP 5 * FROM plastic WHERE plastic_ID > " . $_SESSION['lastid'] . " ORDER BY plastic_ID";
break;
case 'prev':
$query = "SELECT * FROM (SELECT TOP 5 * FROM plastic WHERE plastic_ID < " . $_SESSION['firstid'] . " ORDER BY plastic_ID DESC) AS t ORDER BY plastic_ID ASC";
break;
default:
$query = "SELECT TOP 5 * FROM plastic ORDER BY plastic_ID";
}
}
$result = odbc_exec($db, $query);
while(odbc_fetch_row($result)){
?>
<table style="width: 600;">
<tr>
<td style="width: 300; height: 25px;">Name:</td>
<td style="width: 300; height: 25px;">Idea Name:</td>
</tr>
<tr>
<td style="width: 300; height: 25px;"><?php echo odbc_result($result, "ename"); ?></td>
<td style="width: 300; height: 25px;"><?php echo odbc_result($result, "iname"); ?></td>
</tr>
<tr>
<td colspan="5" style="height: 25px"><hr/></td>
</tr>
</table>
<?php //PREVIOUS AND NEXT BUTTONS
}
if($page !=1){
$pageprev = $page - 1;
echo "&nbsp;<strong><a href='?page=$pageprev'>PREV</a></strong>&nbsp;"; }
else{ echo "&nbsp;PREV&nbsp;"; }
$numofpages = $numrows / $limit;
for($i = 1; $i <= $numofpages; ++$i){
if($i == $page){ echo "&nbsp;[$i]&nbsp;"; }
else{ echo "&nbsp;<strong><a href='?page=$i'>$i</a></strong>&nbsp;"; }
}
if(($numrows &#37; $limit) != 0){
if($i == $page){ echo "&nbsp;[$i]&nbsp;"; }
else{ echo "&nbsp;<strong><a href='?page=$i'>$i</strong></b>&nbsp;"; }
}
if(($numrows - ($limit * $page)) > 0){
$pagenext = $page + 1;
echo "&nbsp;<strong><a href='?page=$pagenext'>NEXT</a></strong>&nbsp;"; }
else{ echo "&nbsp;NEXT&nbsp;"; }
odbc_free_result($result);
exit;
?>

Please learn to use the php tags

lscruiser · Nov 4, 2009

Excuse me for not formating my question correctly. Hopefully it is formated correctly now. My question is still unanswered form the previous post.
I took your code and replaced it with my code. The result is seeing all entries (not 5 at a time as desired) and the pagination links do not link to the NEXT or PREV pages.
I appreciate your response and help.
Here is my code.

<?php 
$db = odbc_connect('',''',''');  //put your connection here 
function inv_rows($r1)  { 
ob_start(); 
(int)$number=odbc_result_all($r1); 
ob_clean(); 
return $number; 
} 
$page = isset($_POST["page"]) ? $_POST["page"] : 1;
if(empty($page)){$page = 1; } 
$query = "SELECT * FROM plastic"; // name of table is plastic with columns plastic_ID, ename, and iname.    	    

$num_result = odbc_exec($db, $query); 
$numrows = inv_rows($num_result);                    

echo '<p>There are '.$numrows.' ideas.</p>'; 
$limit = 5; 
$limitvalue = $page * $limit - ($limit); 
$limitnew = $limitvalue + $limit;
if (isset($_POST['submit'])) {
switch ($_POST['direction']) {
case 'next':
$query = "SELECT TOP 5 * FROM plastic WHERE plastic_ID > " . $_SESSION['lastid'] . " ORDER BY plastic_ID";
break;
case 'prev':
$query = "SELECT * FROM (SELECT TOP 5 * FROM plastic WHERE plastic_ID < " . $_SESSION['firstid'] . " ORDER BY plastic_ID DESC) AS t ORDER BY plastic_ID ASC";
break;
default:
$query = "SELECT TOP 5 * FROM plastic ORDER BY plastic_ID";
}     

}
$result = odbc_exec($db, $query); 
while(odbc_fetch_row($result)){                

?> 
<table style="width: 600;">     

<tr> 
<td style="width: 300; height: 25px;">Name:</td> 
<td style="width: 300; height: 25px;">Idea Name:</td> 
</tr>                 

<tr> 
<td style="width: 300; height: 25px;"><?php echo odbc_result($result, "ename"); ?></td> 
<td style="width: 300; height: 25px;"><?php echo odbc_result($result, "iname"); ?></td> 
</tr> 
<tr> 
<td colspan="5" style="height: 25px"><hr/></td> 
</tr> 
</table> 
<?php //PREVIOUS AND NEXT BUTTONS 
} 
if($page !=1){ 
$pageprev = $page - 1; 
echo "&nbsp;<strong><a href='?page=$pageprev'>PREV</a></strong>&nbsp;"; } 
else{ echo "&nbsp;PREV&nbsp;"; } 
$numofpages = $numrows / $limit; 
for($i = 1; $i <= $numofpages; ++$i){ 
if($i == $page){ echo "&nbsp;[$i]&nbsp;"; } 
else{ echo "&nbsp;<strong><a href='?page=$i'>$i</a></strong>&nbsp;"; } 
} 
if(($numrows % $limit) != 0){ 
if($i == $page){ echo "&nbsp;[$i]&nbsp;"; } 
else{ echo "&nbsp;<strong><a href='?page=$i'>$i</strong></b>&nbsp;"; } 
} 
if(($numrows - ($limit * $page)) > 0){ 
$pagenext = $page + 1; 
echo "&nbsp;<strong><a href='?page=$pagenext'>NEXT</a></strong>&nbsp;"; } 
else{ echo "&nbsp;NEXT&nbsp;"; } 
odbc_free_result($result);             

exit; 
?>

Roger_Ramjet · Nov 5, 2009

OK. I don't know where you got the design for this pagination, but it is not going to work with an NS db that uses TOP.

You can find lots of posts and solutions here by searching for pagination. In fact every question you have has been answered many many times here. Except of course that they are for MySQL.

With mysql you have LIMIT with an offset, so using the page number is OK as one can calculate the offset from the page number and posts-per-page.

Using TOP one can not calculate anything from the page number. So stop trying to use it and use the firstid, lastid as I said. Either save them as session vars, or put them in the form data. Then you can use them in the queries as I have shown. The reason for this is that there is no real chance that page 5 times per-page 10 = 50 will correspond to the id in any way no matter what db engine you are using. So you can not use id > 50 to get the next page list.

lscruiser · Nov 5, 2009

You are correct in saying there are many solutions for paginations with MySQL. However, I am using an ODBC connection (not my choice) with MSSQL (not my choice) written in PHP. I have not found a workable solution with an ODBC connection to a MSSQL database written in PHP. If you have an example you could share, I would appreciate it. Thank you for your help.

big_nerd · Nov 5, 2009

Actually, I dont know about ODBC (never used it) but there IS a way to use NESTED queries with SELECT TOP with MSSQL to get Paged Results, but you must have a defined primary key.

$max = 15; // Number Results/Page
$start = 61 // This would be the where you start, i.e. 16, 31 (1 after last result item)

$query = "SELECT TOP $max * FROM [table] WHERE [column] = 'yes' AND ([primary_key] NOT IN (SELECT TOP $start [primary_key] FROM [table] ORDER BY [primary key])) ";

I hope this made sense, its effectively LIKE using LIMIT clause with MySQL.

This is less efficient because it does use nested queries, but it works.

Roger_Ramjet · Nov 5, 2009

Yep, that will work Nerd. Nice one

lscruiser, OK, to do this with breadcrumbs to many/any page, you need Nerd's solution. User submits for page 5, 10 records per page. So the subquery has TOP 40. The the outer query selects TOP 10 ... NOT IN. Hope you get that.

$perpage = 10;
...
...
if ($_POST['submit']) {

$page = $_POST['page'];
$top = ($page - 1) * $perpage;
$sql = "SELECT TOP $perpage * FROM tbl WHERE tblid NOT IN (SELECT TOP $top tblid FROM tbl ORDER BY tblid) ORDER BY tblid"; 	
// etc	
}

Had not thought of that. We live and learn.

Now, if it is MickeySoft SQL then you can create a stored proc for this, just feed it the page number and the per-page and off you go. Same-Same for mysql of course.

PS Nerd, why the select == yes? I know mssql can be dickey about IN and EXISTS but I still don't get it.

My thinking on this was just to have Previous/Next, but that way you can not jump to eg page 5 :queasy:

big_nerd · Nov 5, 2009

oh sorry that was copied from something that I was forced to move from MySQL to MSSQL, it was an enumeration yes or no, as the previous guy (before me) just gave them (company) access to phpMyAdmin as an 'admin panel', and it was easier to manage with that.

i just did a quick replace of items to mask the actual terribly embarrassing column names, but left it there to illustrate the use of a where clause.

lscruiser · Nov 5, 2009

Big.nerd: I am not sure what you are saying on that last post. I can hard code a name from the database in the WHERE clause and display an entry of that hard coded name. Of course the hard coding name is for testing only. What is the proper syntax to display all the names if I use the WHERE clause? Or, do I use the WHERE clause?
Information about my code:
Table name = plastic
Primary key = plastic_ID
column name = ename
column name = iname
This page should display how many names (as a number) are in the database, list the first 5 names and the iname information along with the PREV and NEXT buttons.
My code with your corrections tells how many names are in the database but does not display any names. The PREV and NEXT buttons don't do anything. I am still not sure if line nine has the proper wording of "page". I appreciate your help with this. Thank you.

<?php 
$db = odbc_connect('WEB_PROD_IDEAS','write','write123');  //put your connection here 
function inv_rows($r1)  { 
ob_start(); 
(int)$number=odbc_result_all($r1); 
ob_clean(); 
return $number; 
} 
$page = isset($_POST["page"]) ? $_POST["page"] : 1;  //not sure of "page" text 
if(empty($page)){$page = 1; }                

$query = "SELECT * FROM plastic"; //name of table is plastic with columns plastic_ID, ename, and iname.    		    

$num_result = odbc_exec($db, $query); 
$numrows = inv_rows($num_result);                    

echo '<p>There are '.$numrows.' ideas.</p>'; 
$limit = 5; 
$limitvalue = $page * $limit - ($limit); 
$limitnew = $limitvalue + $limit; 
$max = 5; // Number Results/Page
$start = 6; // This would be the where you start, i.e. 16, 31 (1 after last result item)
$sql = "SELECT TOP $max * FROM plastic WHERE ename = 'yes' AND (plastic_ID NOT IN (SELECT TOP $start plastic_ID FROM plastic ORDER BY plastic_ID)) ";
$result = odbc_exec($db, $sql); 
while(odbc_fetch_row($result)){                

?> 
<table style="width: 600;">     

<tr> 
<td style="width: 300; height: 25px;">Name:</td> 
<td style="width: 300; height: 25px;">Idea Name:</td> 
</tr>                 

<tr> 
<td style="width: 300; height: 25px;"><?php echo odbc_result($result, "ename"); ?></td> 
<td style="width: 300; height: 25px;"><?php echo odbc_result($result, "iname"); ?></td> 
</tr> 
<tr> 
<td colspan="5" style="height: 25px"><hr/></td> 
</tr> 
</table> 
<?php //PREVIOUS AND NEXT BUTTONS 
} 
if($page !=1){ 
$pageprev = $page - 1; 
echo '&nbsp;<strong><a href="?page='.$pageprev.'">PREV</a></strong>&nbsp;'; }
else{ echo "&nbsp;PREV&nbsp;"; } 
$numofpages = $numrows / $limit; 
for($i = 1; $i <= $numofpages; ++$i){ 
if($i == $page){ echo "&nbsp;[$i]&nbsp;"; } 
else{ echo "&nbsp;<strong><a href='?page=$i'>$i</a></strong>&nbsp;"; } 
} 
if(($numrows &#37; $limit) != 0){ 
if($i == $page){ echo "&nbsp;[$i]&nbsp;"; } 
else{ echo "&nbsp;<strong><a href='?page=$i'>$i</strong></b>&nbsp;"; } 
} 
if(($numrows - ($limit * $page)) > 0){ 
$pagenext = $page + 1; 
echo "&nbsp;<strong><a href='?page=$pagenext'>NEXT</a></strong>&nbsp;"; } 
else{ echo "&nbsp;NEXT&nbsp;"; } 
odbc_free_result($result);             

exit; 
?>

big_nerd · Nov 5, 2009

lscruiser, that comment was for Roger Ramjet as he was asking about the = 'yes' clause I had in the posted example.

If you take a look at what Roger mentioned, he cleaned up my code to be a littlle easier to read, if you look @ your code below:

$start = 6; // This would be the where you start, i.e. 16, 31 (1 after last result item)
$sql = "SELECT TOP $max * FROM plastic WHERE ename = 'yes' AND (plastic_ID NOT IN (SELECT TOP $start plastic_ID FROM plastic ORDER BY plastic_ID)) ";

You have the same result set every time because you have a static $start set.

You are seeing results 6-10 if I am not mistaken, correct?

You are using get statements, instead of what Roger had shown you using POST, when you set a URL ?page=3 , etc you are sending it VIA get.

This is a potential security risk, but I will give you something that may work, I am pressed for time, so if I mess up, hopefully someone here will fix it!

if (!isset($_GET['page']) || empty($_GET['page']) {
     $start = (int)$_GET['page'];
     $start = ($start * $max);
} else {
     $start = 0;
}

//$start = 6; // This would be the where you start, i.e. 16, 31 (1 after last result item)
$sql = "SELECT TOP $max * FROM plastic WHERE ename = 'yes' AND (plastic_ID NOT IN (SELECT TOP $start plastic_ID FROM plastic ORDER BY plastic_ID)) ";

That ought to do it, I hope anyways, I gotta run!

lscruiser · Nov 5, 2009

It is not clear on my part when I get partial code back from you to put it in the correct place. It might be helpful if the entire code with updates was returned. I am having trouble putting it all together when it is returned.
The code below has error: Undefined index: submit on line 21.
Thank you in advance for your help.

<?php 
$db = odbc_connect('','','');  //put your connection here 
function inv_rows($r1)  { 
ob_start(); 
(int)$number=odbc_result_all($r1); 
ob_clean(); 
return $number; 
} 
$page = isset($_POST["page"]) ? $_POST["page"] : 1;
if(empty($page)){$page = 1; }              

$query = "SELECT * FROM plastic"; //name of table is plastic with columns plastic_ID, ename, and iname.    		    

$num_result = odbc_exec($db, $query); 
$numrows = inv_rows($num_result);                    

echo '<p>There are '.$numrows.' ideas.</p>'; 
$limit = 5; 
$limitvalue = $page * $limit - ($limit); 
$limitnew = $limitvalue + $limit; 
$max = 5; // Number Results/Page
$start = 6; // This would be the where you start
$perpage = 5; 
if ($_POST['submit']) {  

$page = $_POST['page']; 
$top = ($page - 1) * $perpage; 
$sql = "SELECT TOP $perpage * FROM plastic WHERE plastic_ID NOT IN (SELECT TOP $top plastic_ID FROM plastic ORDER BY plastic_ID) ORDER BY plastic_ID";           

} 
$result = odbc_exec($db, $sql); 
while(odbc_fetch_row($result)){                

?> 
<table style="width: 600;">     

<tr> 
<td style="width: 300; height: 25px;">Name:</td> 
<td style="width: 300; height: 25px;">Idea Name:</td> 
</tr>                 

<tr> 
<td style="width: 300; height: 25px;"><?php echo odbc_result($result, "ename"); ?></td> 
<td style="width: 300; height: 25px;"><?php echo odbc_result($result, "iname"); ?></td> 
</tr> 
<tr> 
<td colspan="5" style="height: 25px"><hr/></td> 
</tr> 
</table> 
<?php //PREVIOUS AND NEXT BUTTONS 
} 
if($page !=1){ 
$pageprev = $page - 1; 
echo '&nbsp;<strong><a href="?page='.$pageprev.'">PREV</a></strong>&nbsp;'; }
else{ echo "&nbsp;PREV&nbsp;"; } 
$numofpages = $numrows / $limit; 
for($i = 1; $i <= $numofpages; ++$i){ 
if($i == $page){ echo "&nbsp;[$i]&nbsp;"; } 
else{ echo "&nbsp;<strong><a href='?page=$i'>$i</a></strong>&nbsp;"; } 
} 
if(($numrows % $limit) != 0){ 
if($i == $page){ echo "&nbsp;[$i]&nbsp;"; } 
else{ echo "&nbsp;<strong><a href='?page=$i'>$i</strong></b>&nbsp;"; } 
} 
if(($numrows - ($limit * $page)) > 0){ 
$pagenext = $page + 1; 
echo "&nbsp;<strong><a href='?page=$pagenext'>NEXT</a></strong>&nbsp;"; } 
else{ echo "&nbsp;NEXT&nbsp;"; } 
odbc_free_result($result);             

exit; 
?>

big_nerd · Nov 6, 2009

Try this out:

Note: as a learning point - you can see that I had the SQL query in there, if you replace the SQL query with the info I provided it will yield this result:

<?php
$db = odbc_connect('WEB_PROD_IDEAS','write','write123');  //put your connection here
function inv_rows($r1)  {
ob_start();
(int)$number=odbc_result_all($r1);
ob_clean();
return $number;
}
$page = isset($_POST["page"]) ? $_POST["page"] : 1;  //not sure of "page" text
if(empty($page)){$page = 1; }                

$query = "SELECT * FROM plastic"; //name of table is plastic with columns plastic_ID, ename, and iname.                

$num_result = odbc_exec($db, $query);
$numrows = inv_rows($num_result);                    

echo '<p>There are '.$numrows.' ideas.</p>';
$limit = 5;
$limitvalue = $page * $limit - ($limit);
$limitnew = $limitvalue + $limit;
$max = 5; // Number Results/Page
//$start = 6; // This would be the where you start, i.e. 16, 31 (1 after last result item)
f (!isset($_GET['page']) || empty($_GET['page']) {
     $start = (int)$_GET['page'];
     $start = ($start * $max);
} else {
     $start = 0;
}

//$start = 6; // This would be the where you start, i.e. 16, 31 (1 after last result item)
$sql = "SELECT TOP $max * FROM plastic WHERE ename = 'yes' AND (plastic_ID NOT IN (SELECT TOP $start plastic_ID FROM plastic ORDER BY plastic_ID)) ";
$result = odbc_exec($db, $sql);
while(odbc_fetch_row($result)){                

?>
<table style="width: 600;">     

<tr>
<td style="width: 300; height: 25px;">Name:</td>
<td style="width: 300; height: 25px;">Idea Name:</td>
</tr>                 

<tr>
<td style="width: 300; height: 25px;"><?php echo odbc_result($result, "ename"); ?></td>
<td style="width: 300; height: 25px;"><?php echo odbc_result($result, "iname"); ?></td>
</tr>
<tr>
<td colspan="5" style="height: 25px"><hr/></td>
</tr>
</table>
<?php //PREVIOUS AND NEXT BUTTONS
}
if($page !=1){
$pageprev = $page - 1;
echo '&nbsp;<strong><a href="?page='.$pageprev.'">PREV</a></strong>&nbsp;'; }
else{ echo "&nbsp;PREV&nbsp;"; }
$numofpages = $numrows / $limit;
for($i = 1; $i <= $numofpages; ++$i){
if($i == $page){ echo "&nbsp;[$i]&nbsp;"; }
else{ echo "&nbsp;<strong><a href='?page=$i'>$i</a></strong>&nbsp;"; }
}
if(($numrows &#37; $limit) != 0){
if($i == $page){ echo "&nbsp;[$i]&nbsp;"; }
else{ echo "&nbsp;<strong><a href='?page=$i'>$i</strong></b>&nbsp;"; }
}
if(($numrows - ($limit * $page)) > 0){
$pagenext = $page + 1;
echo "&nbsp;<strong><a href='?page=$pagenext'>NEXT</a></strong>&nbsp;"; }
else{ echo "&nbsp;NEXT&nbsp;"; }
odbc_free_result($result);             

exit;
?>

I know I can be a pain with stuff like this, so feel free to be a pain to me when I ask for something you can help with...

It does help you learn though...

Shrike · Nov 7, 2009

If you are using MSSQL 2005 or higher you can make use of Common Table Expressions for a (slightly) more intuitive method to paginate recordsets. Who needs nice and simple LIMIT eh!

Shrikant_Patil · Nov 8, 2009

MSSQL 2008 wont support limit . Better use ROW_NUMBER () for pagination

EX:

SELECT * FROM ( SELECT ROW_NUMBER() OVERY (ORDER BY COLUMN_NAME) AS Rn , Fname , Middlename,Lname from Persons ) WHERE Rn > Var1 and Rn <var2 ( or use BETWEEN instead of AND )

pass var1 start limit and var as end limit

lscruiser · Nov 9, 2009

big.nerd or anyone else that can help:
Thank you for your response. I have three questions, not necessarily in the correct order.
1. On line 9 below I am not sure I am calling the field name of "page" the right thing.
2. On line 20 below I receive the error "Notice: Undefined index: page." - with the code that is below. I need to know how to fix this error.
3. On line 25 I need to rename the value of "yes" to where it displays all the results of the column ename.
Details: My table name is "plastic" with primary key of "plastic_ID" and columns of "ename" and "iname". I want my page to display the number of names in the database as a number. Example: There are "XX" names. This part of the code below works. Then I want to display 5 names per page with a PREV and NEXT button to move the user through the results. My connection is ODBC (not my choice) with MSSQL(not my choice) and PHP.
Thank you for your help. Returning the complete updated code helps me understand your changes.

<?php
$db = odbc_connect('','','');  //put your connection here
function inv_rows($r1)  {
ob_start();
(int)$number=odbc_result_all($r1);
ob_clean();
return $number;
}
$page = isset($_POST["page"]) ? $_POST["page"] : 1;//question about this line 9
if(empty($page)){$page = 1; }                

$query = "SELECT * FROM plastic"; //name of table is plastic with columns plastic_ID, ename, and iname.                

$num_result = odbc_exec($db, $query);
$numrows = inv_rows($num_result);                    

echo '<p>There are '.$numrows.' ideas.</p>';
$limit = 5;
$limitvalue = $page * $limit - ($limit);
$limitnew = $limitvalue + $limit;
$max = 5;
if (!isset($_GET['page']) || empty($_GET['page'])) {
     $start = (int)$_GET['page']; //question about this line 20 - Notice: Undefined index: page 
     $start = ($start * $max);
} else {
     $start = 0;
}
$sql = "SELECT TOP $max * FROM plastic WHERE ename = 'yes' AND (plastic_ID NOT IN (SELECT TOP $start plastic_ID FROM plastic ORDER BY plastic_ID)) ";//question about this line 25
$result = odbc_exec($db, $sql);
while(odbc_fetch_row($result)){                

?>
<table style="width: 600;">     

<tr>
<td style="width: 300; height: 25px;">Name:</td>
<td style="width: 300; height: 25px;">Idea Name:</td>
</tr>                 

<tr>
<td style="width: 300; height: 25px;"><?php echo odbc_result($result, "ename"); ?></td>
<td style="width: 300; height: 25px;"><?php echo odbc_result($result, "iname"); ?></td>
</tr>
<tr>
<td colspan="5" style="height: 25px"><hr/></td>
</tr>
</table>
<?php //PREVIOUS AND NEXT BUTTONS
}
if($page !=1){
$pageprev = $page - 1;
echo '&nbsp;<strong><a href="?page='.$pageprev.'">PREV</a></strong>&nbsp;'; }
else{ echo "&nbsp;PREV&nbsp;"; }
$numofpages = $numrows / $limit;
for($i = 1; $i <= $numofpages; ++$i){
if($i == $page){ echo "&nbsp;[$i]&nbsp;"; }
else{ echo "&nbsp;<strong><a href='?page=$i'>$i</a></strong>&nbsp;"; }
}
if(($numrows % $limit) != 0){
if($i == $page){ echo "&nbsp;[$i]&nbsp;"; }
else{ echo "&nbsp;<strong><a href='?page=$i'>$i</strong></b>&nbsp;"; }
}
if(($numrows - ($limit * $page)) > 0){
$pagenext = $page + 1;
echo "&nbsp;<strong><a href='?page=$pagenext'>NEXT</a></strong>&nbsp;"; }
else{ echo "&nbsp;NEXT&nbsp;"; }
odbc_free_result($result);             

exit;
?>

big_nerd · Nov 9, 2009

The field name page comes from the section where you say:

if($i == $page){ echo "&nbsp;[$i]&nbsp;"; }
else{ echo "&nbsp;<strong><a href='?page=$i'>$i</a></strong>&nbsp;"; }
}
if(($numrows % $limit) != 0){
if($i == $page){ echo "&nbsp;[$i]&nbsp;"; }
else{ echo "&nbsp;<strong><a href='?page=$i'>$i</strong></b>&nbsp;"; }
}

Do you see how you have <a href='?page= $i ?

That is where $_GET['page'] comes from, you are passing the GET variable (when passing it in the URL it is a GET request.

Its a notice that $_GET['page'] doesnt exist, at the top of your code, RIGHT BELOW <?php add the following

error_reporting(E_ALL ^ E_NOTICE);

This will suppress notice's

With question #3,

lscruiser wrote:
3. On line 25 I need to rename the value of "yes" to where it displays all the results of the column ename.

I am not sure, what is the value ename? That is a column name, you may not need that at all.

You can try removing that, you are essentially telling the SQL to give you data where the value of the 'ename' column is yes, when I provided the example on how to do this, I had taken this from a portion of my code that needed it, if you don't need it, remove it.

It seems as if you are trying to pull a POST of the variable PAGE but i don't see you using POST anywhere.

I had made some recommendations in the code below and made a couple changes, I hope this helps..

<?php
error_reporting(E_ALL ^ E_NOTICE); // supress notice's
$db = odbc_connect('','','');  //put your connection here
function inv_rows($r1)  {
ob_start();
(int)$number=odbc_result_all($r1);
ob_clean();
return $number;
}

// Setting the $max as 5, as in 5 results PER PAGE.
$max = 5; // using this instead of limit

// if you havn't specified page (as in they clicked on next page)  set $start as 0 (i think, may need to be 1)
// If they have specified it, get the 'page' from the URL (?page=#) and be sure its a number. (thats what (int) does)
// Then set the $page (page number) and the start, is the number of pages * limit, i.e. page 1 is 5, 2 is 10, etc.
if (!isset($_GET['page']) || empty($_GET['page'])) {
     $page = (int)$_GET['page']; //question about this line 20 - Notice: Undefined index: page
     $start = ($page * $max);
} else {
     $start = 0;
}
$query = "SELECT * FROM plastic"; //name of table is plastic with columns plastic_ID, ename, and iname.                

$num_result = odbc_exec($db, $query);
$numrows = inv_rows($num_result);                    

echo '<p>There are '.$numrows.' ideas.</p>'; // I am assuming this works
// Example: Page 2, start will be 10
// Example SQL Query: Select top 5 * FROM plastic WHERE plastic_id is not in the top 10 items, thus you get 11-15...
$sql = "SELECT TOP $max * FROM plastic WHERE (plastic_ID NOT IN (SELECT TOP $start plastic_ID FROM plastic ORDER BY plastic_ID)) ";//question about this line 25
$result = odbc_exec($db, $sql);
while(odbc_fetch_row($result)){                

?>
<table style="width: 600;">     

<tr>
<td style="width: 300; height: 25px;">Name:</td>
<td style="width: 300; height: 25px;">Idea Name:</td>
</tr>                 

<tr>
<td style="width: 300; height: 25px;"><?php echo odbc_result($result, "ename"); ?></td>
<td style="width: 300; height: 25px;"><?php echo odbc_result($result, "iname"); ?></td>
</tr>
<tr>
<td colspan="5" style="height: 25px"><hr/></td>
</tr>
</table>
<?php //PREVIOUS AND NEXT BUTTONS
}
if($page !=1){ 
$pageprev = $page - 1;
echo '&nbsp;<strong><a href="?page='.$pageprev.'">PREV</a></strong>&nbsp;'; }
else{ echo "&nbsp;PREV&nbsp;"; }
$numofpages = $numrows / $max;
for($i = 1; $i <= $numofpages; ++$i){
if($i == $page){ echo "&nbsp;[$i]&nbsp;"; }
else{ echo "&nbsp;<strong><a href='?page=$i'>$i</a></strong>&nbsp;"; }
}
if(($numrows % $max) != 0){
if($i == $page){ echo "&nbsp;[$i]&nbsp;"; }
else{ echo "&nbsp;<strong><a href='?page=$i'>$i</strong></b>&nbsp;"; }
}
if(($numrows - ($max * $page)) > 0){
$pagenext = $page + 1;
echo "&nbsp;<strong><a href='?page=$pagenext'>NEXT</a></strong>&nbsp;"; }
else{ echo "&nbsp;NEXT&nbsp;"; }
odbc_free_result($result);             

exit;
?>

odbc sql php pagination query

Llscruiser

RRoger_Ramjet

Llscruiser

RRoger_Ramjet

Llscruiser

RRoger_Ramjet

Llscruiser

RRoger_Ramjet

Llscruiser

Bbig_nerd

RRoger_Ramjet

Bbig_nerd

Llscruiser

Bbig_nerd

Llscruiser

Bbig_nerd

SShrike

SShrikant_Patil

Llscruiser

Bbig_nerd