ODBC Pagination help w/ MSSQL

[sharijl]

I see tutorials all over the web with pagination and MySQl but not much out there (that is updated using PHP 5 without global variables) on doing this using ODBC connection with MSSQL.

This is what I found online but I am confused.

[PHP]<?php

//connecting to the database using ODBC
$db=odbc_connec t($dsn,$user,$p ass)or die("Error: Can't Connect to Database");

// assigns a value how many rows we will get from each page
$limit = 15;

/* assign variable with sql query of counting how many rows are there in the database.
take note you should assign * with a table data name and table_name with table name*/
$query_count = "SELECT COUNT(*) as value FROM table_name";

//executes the sql statement to the database
$result_count = odbc_exec($db, $query_count);

// get the result count from the database
$totalrows = odbc_fetch_obje ct($result_coun t);

if(empty($page) ){ // Checks if the $page variable is empty (not set)
$page = 1; // If it is empty, we're on page 1
}

//this will be the lowest limit
$limitvalue = $page * $limit - ($limit);
$limitnew = $limitvalue + $limit;
// Ex: (2 * 25) - 25 = 25 <- data starts at 25
$query = "SELECT * FROM table_name where ID between $limitvalue and $limitnew ORDER BY ID ASC";[/PHP]
I think this is where I get stuck. is the ID suppose to be the "primary key"?

PHP Code:
[PHP]//executes the sql statement to the database
$result = odbc_exec($db, $query);

echo "<table><tr><td >Title1</td>
<td>Title2</td>";

// open loop to list down contents from the database
while (($row = odbc_fetch_obje ct($result))) {

echo "<tr><td>$r ow->content_title1 </td>
<td>$row->content_title2 </td>";

//close the loop
}

//now we close the table
echo "</table>";

/*check if we are at page 1. if true, PREV will not be a link. if false, $page will be a link minus 1 page. */
if($page != 1){
$pageprev = $page -1;
echo("&nbsp;<b> <a href=" . $_SERVER['PHP_SELF'] ."?page=$pagepr ev>PREV</a></b>&nbsp;");
}else{
echo("&nbsp;PRE V&nbsp;");
}

/* $numofpages will be the quotient of the total rows divided by the limit value
in this example lets assume that we have 47 rows in the database divided by 15 will result 3.13 */
$numofpages = $totalrows->value / $limit;

/* now let us generate those page numbers*/
for($i = 1; $i <= $numofpages; ++$i){
if($i == $page){
echo("&nbsp;[$i]&nbsp;");
}else{
echo("&nbsp;<b> <a href=" . $_SERVER['PHP_SELF'] ."?page=$i>$ i</a></b>&nbsp;");
}
}

/* check if we have a remainder and print it at the end page list */
if(($totalrows->increm % $limit) != 0){
if($i == $page){
echo("&nbsp;[$i]&nbsp;");
}else{
echo("&nbsp;<b> <a href=" . $_SERVER['PHP_SELF'] ."?page=$i>$ i</a></b>&nbsp;");
}
}

/* finally we check the "next" link if we have a page to move on to, if yes we put it as a link adding another page.
in this example assuming that we have $totalrows->value=47
this will check if (47 - (15 * 1) = 32) > zero, results to true will give a link.*/
if(($totalrows->value - ($limit * $page)) > 0){
$pagenext = $page +1;

echo("&nbsp;<b> <a href=" . $_SERVER['PHP_SELF'] ."?page=$pagene xt>NEXT</a></b> ");
}else{
echo("&nbsp;NEX T&nbsp;");
}

// now let us free the resources
odbc_free_resul t($result);

?>[/PHP]
When this is executed I do see the paging links (but they don't work) and there is no data. How does this script look?

[kovik]

Pagination in PHP and SQL isn't very difficult. At no point in your script do you use the $_GET variables from the URL, so the query strings are rendered useless.

[sharijl]

I have seen that article before but since it refereces MYSQL and not Microsoft SQL using ODBC I did not think much about it. MSSQL does not allow for limit statements so not much of this information really applies to me except when you referenced $_GET. The article gives this example:
[PHP]

if(isset($_GET['page']))
{
$page = (int)$_GET['page'];

if($page < 1)
{
$page = 1;
}
}[/PHP]

But don't see how to tie this into the current code? I referenced this bit which is about the same since $_GET =$_REQUEST

[PHP]<?php

// paging info, done in a global include file
// paging variable must always be 'page'!, ie: ?page=##
$page = intval( $_REQUEST['page'] ) > 0
? intval( $_REQUEST['page'] )
: 1;
$limit = 5;
$offset = $page * $limit - $limit;
[/PHP]

This part I can't do:

[PHP]// get selected records and total possible records
$records = mysql_query( "SELECT * FROM table LIMIT {$offset}, {$limit}" );
[/PHP]

I saw this SQL example which might be a substitution if I knew how it would fit together with the current script.

[HTML]
select top 20 * from table where pkc not in (select top 10 pkc from t order by pkc) order by pkc[/HTML]

rest of the MYSQL script example

[PHP]
$total = mysql_query( "SELECT COUNT( id ) FROM table" );
// paginate!
require 'class.Paginati on.php';
$paginate =& new Pagination;

// optional: set current query string (for use with existing link queries)
$paginate->query = '?category=link s&';

// generate paging data
$paginate->generate( $total, $limit );

// display total pages
echo 'Total pages: ' . $paginate->totalPages; // Total pages: 10

// display paging info
echo 'Page: ' . $paginate->pages; // Page: 2 of 10

// display currently viewed items
echo 'Displaying ' . $paginate->records . ' items'; // Displaying 6 to 10 of 50 items

// display the obligatory links
echo $paginate->links;

// display select box (similar to links)
echo $paginate->select;

?> [/PHP]

[kovik]

I believe that these are equivalent:

MySQL:
[code=sql]SELECT * FROM `table` LIMIT $x, $y ORDER BY `column`;[/code]
MSSQL:
[code=sql]SELECT TOP $y * FROM (SELECT TOP $x FROM `table` ORDER BY `column`) ORDER BY `column`;[/code]