odbc sql php pagination query

[lscruiser]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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

if($i == $page){ echo " [$i] "; }
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.

2. 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;
?> 

[lscruiser]

I have not been able to write a correct SELECT statement for the page information to change.
I found a similar problem: http://www.phpbuilder.com/board/showthread.php?t=10352935 for reference.
The name of my table is plastic. My primary key is plastic_ID. ename represents names and iname represents ideas. I want to show 5 names and ideas per page with pagination. Below are three different SELECT statements that produce the same results if you click on PREV or NEXT buttons. These buttons change when clicked, but the results stay the same.
I use an ODBC connection with MSSQL and PHP.
Can you help me write a correct SELECT statement? Returning the complete code helps me understand your edits. I 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($_GET["page"]) ? $_GET["page"] : 1;
if(empty($page)){$page = 1; }                

$query = "SELECT * FROM plastic"; // name of table is plastic with primary key of plastic_ID and 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; 
//all three SELECT statements below produce a PREV and NEXT action that appears to work BUT the results are the same when clicking to a PREV or NEXT page
  $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";
//$sql = "SELECT * FROM (SELECT TOP 5 * FROM (SELECT TOP $numrows * FROM plastic ORDER BY ename, iname ASC) as table1 ORDER BY ename, iname DESC) as table2 ORDER BY ename, iname ASC";
//$sql = "SELECT * FROM (SELECT TOP 5 * FROM plastic ORDER BY ename, iname ASC) AS t1 ORDER BY ename, iname DESC";
$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]

My dear fellow you really have not understood this have you.

The purpose of using the subquery is to elliminate the records that you do not want to display because they would be displayed on a previous page. So page 5 should show records 21-25 and in order to achive this we have to elliminate the first 20. That is what the subquery is used for. The query

SELECT TOP 20 plastic_ID  FROM plastic ORDER BY plastic_ID ASC

Will return the 20 records we DO NOT WANT.
When we use this as a subquery, we want to NOT select these records so we use NOT IN

SELECT TOP 5 * FROM plastic WHERE plastic_ID NOT IN (SELECT TOP 20 plastic_ID  FROM plastic ORDER BY plastic_ID ASC) ORDER BY plastic_id ASC

The first 20 are elliminated and we then get the next 5

[lscruiser]

You are correct when you say that I don't understand this. That is why I am asking for help. I want to display 5 records per page. I still get the same result - all pages display the same thing no matter what page I click. I want to increment 5 records at a time per page. Thank you 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($_GET["page"]) ? $_GET["page"] : 1;
if(empty($page)){$page = 1; }                

$query = "SELECT * FROM plastic"; // name of table is plastic with primary key of plastic_ID and columns ename and iname.  ename represents a name and iname represents an idea  

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

echo '<p>There are '.$numrows.' ideas.</p>'; //This works ok
$limit = 5; 
$limitvalue = $page * $limit - ($limit); 
$limitnew = $limitvalue + $limit; 
//the SELECT statement below produce a PREV and NEXT action that appears to work BUT the results are the same when clicking to a PREV or NEXT page
//I want to show 5 records at a time
$sql = "SELECT TOP $limit * FROM plastic WHERE plastic_ID NOT IN (SELECT TOP 10 plastic_ID FROM plastic ORDER BY plastic_ID ASC) ORDER BY plastic_ID 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; 
?> 

[big_nerd]

lcruiser,

here AGAIN is another post, I did see an error from my first one but lets try this one:

<?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;
}


if (get_magic_quotes_gpc()) {
    function stripslashes_deep($value)
    {
        $value = is_array($value) ?
                    array_map('stripslashes_deep', $value) :
                    stripslashes($value);

    return $value;
}

$_POST = array_map('stripslashes_deep', $_POST);
$_GET = array_map('stripslashes_deep', $_GET);
$_COOKIE = array_map('stripslashes_deep', $_COOKIE);
$_REQUEST = array_map('stripslashes_deep', $_REQUEST);
}


// 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 -1)* $max);  // 
     // REMOVE THE BELOW LINE IF IT STARTS TO WORK
     echo "Found the page number!, Page number is $page, so we need to omit $start results!";
     // REMOVE THE ABOVE LINE IF IT STARTS TO WORK     

} else {
     // REMOVE THE BELOW LINE IF IT STARTS TO WORK
     echo "Can't get a page number, so this must be page 1.<br>";
     // REMOVE THE ABOVE LINE IF IT STARTS TO WORK
     $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
// Example: Page 1
// $start = 0; (Since you are on PAGE 1)
// SELECT TOP 5 * FROM plastic WHERE plastic_ID NOT IN (SELECT TOP 0 plastic_ID FROM plastic ORDER BY plastic_ID)

// Example: Page 2
// $page = 2, thus, $start = ((2-1) * 5)  -- Page 2 would be 1 * 5, thus 5
// Thus, $start = 5;
// SELECT TOP 5 * FROM plastic WHERE plastic_ID NOT IN (SELECT TOP 5 plastic_ID FROM plastic ORDER BY plastic_ID)
// RESULTS:
//
// GIVE ME 5 results of ALL COLUMNS FROM plastic WHERE the plastic ID isnt within the top 5 plastic_id's.
// Meaning, IGNORE 1-5.

$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;
?> 

Just for the record, I just tested it now in our lab, it works from SQL Server 2000 and UP. (The SQL Query formatting, I obviously don't have your data)

Please try the code above, Also, I put in a couple lines that spit out if it found a page or not.

let me know if it says "Found the page number!, Page number is 2, so we need to omit 5 results!"

(that would be page 2)

OR if it ALWAYS says:

"Can't get a page number, so this must be page 1"

best of luck

[lscruiser]

big.nerd: I appreciate you testing the code.
The above code displays these results:

Found the page number!, Page number is 0, so we need to omit -5 results!
There are 17 ideas.
Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '-'., SQL state 37000 in SQLExecDirect in E:\Inetpub..........................Untitled-1.php on line 69
Warning: odbc_fetch_row(): supplied argument is not a valid ODBC result resource in E:\Inetpub..........................Untitled-1.php on line 70
PREV 1 2 3 4 NEXT
Warning: odbc_free_result(): supplied argument is not a valid ODBC result resource in E:\Inetpub..........................Untitled-1.php on line 104

When I remove the error reporting on line 2 I see this error:
Notice: Undefined index: page in E:\Inetpub..........................Untitled-1.php on line 36
Found the page number!, Page number is 0, so we need to omit -5 results!
There are 17 ideas.
Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '-'., SQL state 37000 in SQLExecDirect in E:\Inetpub..........................Untitled-1.php on line 69
Warning: odbc_fetch_row(): supplied argument is not a valid ODBC result resource in E:\Inetpub..........................Untitled-1.php on line 70
PREV 1 2 3 4 NEXT
Warning: odbc_free_result(): supplied argument is not a valid ODBC result resource in E:\Inetpub..........................Untitled-1.php on line 104

I don't think it is seeing $page

Wow, if you tested it successfully, and it doesn't work for me, what's going on?
To create my database I wrote this script:
CREATE DATABASE product;
CREATE TABLE plastic (
plastic_ID MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
ename VARCHAR(40) NOT NULL,
iname VARCHAR(40) NOT NULL,
PRIMARY KEY (plastic_ID)
);

I took out several other columns to keep it simple for testing

I appreciate your help, thank you.

[big_nerd]

The error isnt with the SQL query, it was with a result unexpected.

$page is 0; thus I was removing 5 making it -5. You can SELECT TOP any POSITIVE number, negatives on the other hand break it.

Ive added a check to see if $page is 0; if it is to omit making it SELECT TOP -5, I now set it to 0.

The SQL query as i explained in the comments does work as said, this is a programming-related issue.

<?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;
}


if (get_magic_quotes_gpc()) {
    function stripslashes_deep($value)
    {
        $value = is_array($value) ?
                    array_map('stripslashes_deep', $value) :
                    stripslashes($value);

    return $value;
}

$_POST = array_map('stripslashes_deep', $_POST);
$_GET = array_map('stripslashes_deep', $_GET);
$_COOKIE = array_map('stripslashes_deep', $_COOKIE);
$_REQUEST = array_map('stripslashes_deep', $_REQUEST);
}


// 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
     if ($page == 0) {
       $start = 0;
     } else {
       $start = (($page -1)* $max);  //
       // REMOVE THE BELOW LINE IF IT STARTS TO WORK
       echo "Found the page number!, Page number is $page, so we need to omit $start results!";
       // REMOVE THE ABOVE LINE IF IT STARTS TO WORK     

     }
} else {
     // REMOVE THE BELOW LINE IF IT STARTS TO WORK
     echo "Can't get a page number, so this must be page 1.<br>";
     // REMOVE THE ABOVE LINE IF IT STARTS TO WORK
     $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
// Example: Page 1
// $start = 0; (Since you are on PAGE 1)
// SELECT TOP 5 * FROM plastic WHERE plastic_ID NOT IN (SELECT TOP 0 plastic_ID FROM plastic ORDER BY plastic_ID)

// Example: Page 2
// $page = 2, thus, $start = ((2-1) * 5)  -- Page 2 would be 1 * 5, thus 5
// Thus, $start = 5;
// SELECT TOP 5 * FROM plastic WHERE plastic_ID NOT IN (SELECT TOP 5 plastic_ID FROM plastic ORDER BY plastic_ID)
// RESULTS:
//
// GIVE ME 5 results of ALL COLUMNS FROM plastic WHERE the plastic ID isnt within the top 5 plastic_id's.
// Meaning, IGNORE 1-5.

$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 &#37; $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;
?>