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_connect($dsn,$user,$pass)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_object($result_count);
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_object($result))) {
echo "<tr><td>$row->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(" <b><a href=" . $_SERVER['PHP_SELF'] ."?page=$pageprev>PREV</a></b> ");
}else{
echo(" PREV ");
}
/* $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(" [$i] ");
}else{
echo(" <b><a href=" . $_SERVER['PHP_SELF'] ."?page=$i>$i</a></b> ");
}
}
/* check if we have a remainder and print it at the end page list */
if(($totalrows->increm % $limit) != 0){
if($i == $page){
echo(" [$i] ");
}else{
echo(" <b><a href=" . $_SERVER['PHP_SELF'] ."?page=$i>$i</a></b> ");
}
}
/* 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(" <b><a href=" . $_SERVER['PHP_SELF'] ."?page=$pagenext>NEXT</a></b> ");
}else{
echo(" NEXT ");
}
// now let us free the resources
odbc_free_result($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?
3 5995 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.
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.Pagination.php';
$paginate =& new Pagination;
// optional: set current query string (for use with existing link queries)
$paginate->query = '?category=links&';
// 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]
I believe that these are equivalent:
MySQL: - SELECT * FROM `table` LIMIT $x, $y ORDER BY `column`;
MSSQL: - SELECT TOP $y * FROM (SELECT TOP $x FROM `table` ORDER BY `column`) ORDER BY `column`;
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Kong Li |
last post by:
Environments:
1. WinXP SP1, MDAC 2.7 SP1 (Microsoft ODBC for Oracle is
2.573.9030.0), Oracle client 9.2.0.1.
2. Win2000 SP4, MDAC 2.7 SP1 (Microsoft ODBC for Oracle is
2.573.9030.0), Oracle...
|
by: rjp |
last post by:
Hi,
Does I need to install the mssql client binaries to use ODBC to
connect to a mssql database ?
TYIA
rjp
|
by: Pippen |
last post by:
I'm currently trying to access data from a Cache DB using MSSQL. I have
linked the Cache server through an ODBC connection. I can see in the Linked
Server
expansion all the tables in Cache for the...
|
by: Abram |
last post by:
I have an ODBC 3.0 Application on Windows NT Server maintaining a
connection to a SQL Server 2000 database on the same machine. When an
error occurs, I'd like to be able to determine whether the...
|
by: Greg Gursky |
last post by:
Hello:
I'm doing some planning on a potential project with which I need
some help. The database at the focus of this question is a
MS Access database that is currently accessed by DAO from a...
|
by: Benjamin Fischer |
last post by:
Hi,
I'm running a "Windows Server 2003" with "MS SQL 2000" IIS and PHP5.1 as
test environment for my project. I would like to access my MSSQL-Server
via Windows ODBC and PDO. I created a System...
|
by: =?iso-8859-1?B?RmFkpQ==?= |
last post by:
Hello mates,
Something weird is happening or it's just the fact that I'm doing
something wrong.
When use mssql for db connectivity, and then i call mssql_fetch_array,
the values i get from the...
|
by: 2DIL |
last post by:
hello all experts,
basically, i have a very small project, in which 97% of the work is complete. yet there is an issue which i cannot solve and i have no error to indicate what the problem...
|
by: lscruiser |
last post by:
PAGINATION QUESTION
This code displays the same 5 results from the database. How do I get the page to reference the next or previous group of 5 results? I am using ODBC, SQL, and PHP. LIMIT won't...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |