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!
odbc sql php pagination query
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
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 ' <strong><a href="?page='.$pageprev.'">PREV</a></strong> '; }
else{ echo " PREV "; }
$numofpages = $numrows / $limit;
for($i = 1; $i <= $numofpages; ++$i){
if($i == $page){ echo " [$i] "; }
else{ echo " <strong><a href='?page=$i'>$i</a></strong> "; }
}
if(($numrows % $limit) != 0){
if($i == $page){ echo " [$i] "; }
else{ echo " <strong><a href='?page=$i'>$i</strong></b> "; }
}
if(($numrows - ($limit * $page)) > 0){
$pagenext = $page + 1;
echo " <strong><a href='?page=$pagenext'>NEXT</a></strong> "; }
else{ echo " NEXT "; }
odbc_free_result($result);
exit;
?>
- The field name page comes from the section where you say:
if($i == $page){ echo " [$i] "; }
else{ echo " <strong><a href='?page=$i'>$i</a></strong> "; }
}
if(($numrows % $limit) != 0){
if($i == $page){ echo " [$i] "; }
else{ echo " <strong><a href='?page=$i'>$i</strong></b> "; }
}
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 ' <strong><a href="?page='.$pageprev.'">PREV</a></strong> '; }
else{ echo " PREV "; }
$numofpages = $numrows / $max;
for($i = 1; $i <= $numofpages; ++$i){
if($i == $page){ echo " [$i] "; }
else{ echo " <strong><a href='?page=$i'>$i</a></strong> "; }
}
if(($numrows % $max) != 0){
if($i == $page){ echo " [$i] "; }
else{ echo " <strong><a href='?page=$i'>$i</strong></b> "; }
}
if(($numrows - ($max * $page)) > 0){
$pagenext = $page + 1;
echo " <strong><a href='?page=$pagenext'>NEXT</a></strong> "; }
else{ echo " NEXT "; }
odbc_free_result($result);
exit;
?>
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 " <strong><a href='?page=$pageprev'>PREV</a></strong> "; }
else{ echo " PREV "; }
$numofpages = $numrows / $limit;
for($i = 1; $i <= $numofpages; ++$i){
if($i == $page){ echo " [$i] "; }
else{ echo " <strong><a href='?page=$i'>$i</a></strong> "; }
}
if(($numrows % $limit) != 0){
if($i == $page){ echo " [$i] "; }
else{ echo " <strong><a href='?page=$i'>$i</strong></b> "; }
}
if(($numrows - ($limit * $page)) > 0){
$pagenext = $page + 1;
echo " <strong><a href='?page=$pagenext'>NEXT</a></strong> "; }
else{ echo " NEXT "; }
odbc_free_result($result);
exit;
?>
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
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 " <strong><a href='?page=$pageprev'>PREV</a></strong> "; }
else{ echo " PREV "; }
$numofpages = $numrows / $limit;
for($i = 1; $i <= $numofpages; ++$i){
if($i == $page){ echo " [$i] "; }
else{ echo " <strong><a href='?page=$i'>$i</a></strong> "; }
}
if(($numrows % $limit) != 0){
if($i == $page){ echo " [$i] "; }
else{ echo " <strong><a href='?page=$i'>$i</strong></b> "; }
}
if(($numrows - ($limit * $page)) > 0){
$pagenext = $page + 1;
echo " <strong><a href='?page=$pagenext'>NEXT</a></strong> "; }
else{ echo " NEXT "; }
odbc_free_result($result);
exit;
?>
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 ' <strong><a href="?page='.$pageprev.'">PREV</a></strong> '; }
else{ echo " PREV "; }
$numofpages = $numrows / $max;
for($i = 1; $i <= $numofpages; ++$i){
if($i == $page){ echo " [$i] "; }
else{ echo " <strong><a href='?page=$i'>$i</a></strong> "; }
}
if(($numrows % $max) != 0){
if($i == $page){ echo " [$i] "; }
else{ echo " <strong><a href='?page=$i'>$i</strong></b> "; }
}
if(($numrows - ($max * $page)) > 0){
$pagenext = $page + 1;
echo " <strong><a href='?page=$pagenext'>NEXT</a></strong> "; }
else{ echo " NEXT "; }
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
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.
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 ' <strong><a href="?page='.$pageprev.'">PREV</a></strong> '; }
else{ echo " PREV "; }
$numofpages = $numrows / $max;
for($i = 1; $i <= $numofpages; ++$i){
if($i == $page){ echo " [$i] "; }
else{ echo " <strong><a href='?page=$i'>$i</a></strong> "; }
}
if(($numrows % $max) != 0){
if($i == $page){ echo " [$i] "; }
else{ echo " <strong><a href='?page=$i'>$i</strong></b> "; }
}
if(($numrows - ($max * $page)) > 0){
$pagenext = $page + 1;
echo " <strong><a href='?page=$pagenext'>NEXT</a></strong> "; }
else{ echo " NEXT "; }
odbc_free_result($result);
exit;
?>
This question is still unresolved after many tries. The last answer provides no solution. I agree, it is a result unexpected.
DOES ANYONE HAVE A SOLUTION FOR THIS PROBLEM? Or do you have an example using MS SQL with an ODBC connection with PHP?