This function returns an array of 3 values that makes browsing through your SQL results, or any other list of results, very easy. The navigation bar looks very similar to ones used in most sites [ prev 6 7 8 9 10 next ]. You can limit the amount of rows displayed, the amount of page numbers to display, the previous and next buttons/text. etc.
By : vicus
<?
/*
******************************
* Easy Result Navigator 1.3 *
******************************
Author: Wikus Stander
wikus[at]iamdev[dot]com
http://www.iamdev.com
_ __
(_)__ ___ _ ___/ /__ _ __ _______ __ _
/ / _ `/ ' \/ _ / -_) |/ /_ / __/ _ \/ ' \
/_/\_,_/_/_/_/\_,_/\__/|___/(_)\__/\___/_/_/_/
Copyright:
You may use this function freely. I hope you will
respect the author's intellectual property by:
- keeping this notice in your source, and
- dropping the author a mail: wikus[at]iamdev[dot]com
Thanx.
-------
Changes
-------
01-09-2003: In my example I rather suggest using the COUNT(*) function in the first
SQL query instead of mysql_num_rows(). This would make it return the 'totalrows'
value much faster, especially when there are lots of rows to return..
17-02-2003: Fixed a few bugs when using the next previous links (It would forget the
mysql limit position, etc.) I've removed an argument (start), and you cannot click
on the current selected page anymore. So a few less lines of code and arguments
that makes all the difference. enjoy :)
22-01-2003: Changed variables used in the example at the end of this document to
the global $_GET array. I found most users are running php with register_globals
turned off (php.ini). Thanx Tim Noble for the email :)
-----
To Do
-----
I've had some requests for making it support MSSQL (MSSQL doesn't support the LIMIT
clause) But I'm sure there is a way to get around it :) Keep checking this page...
-----------
Description
-----------
The function returns an array of 3 values as described below:
VALUE 1 [0]: Returns a string that can be appended to your SQL query so that the
correct block of rows can be returned. Eg. " LIMIT 10, 20"
VALUE 2 [1]: Returns an html string that gives the user an indication of which rows
he's currently browsing. Eg. 11-20
VALUE 3 [2]: Returns an html navigation bar needed for navigating through the total
result returned Eg. Page: << 6 7 8 9 10 >>
---------
Arguments
---------
The first 4 arguments are declared/assigned by you while the last 3 are done when
you start using the navigation bar generated by the function (See 'Usage' below).
totalrows - The TOTAL amount of rows returned by your executed SQL statement
numLimit - The amount of page numbers you'd like displayd in your navigation bit
Eg. '4' would display "Page: 1 2 3 4 Next 4 >>"
amm - The amount of rows returned per page (for the SQL string to append)
queryStr - If you'd like extra vars to be passed to that page Eg. "&name=value"
There are also 3 variables inside the function you can change if you'd like the
navigation bar to look a little different (larrow,rarrow,wholePiece).
-----
Usage
-----
See 'Usage' at the end of this document.
*/
function pageBrowser($totalrows,$numLimit,$amm,$queryStr,$numBegin,$begin,$num) {
$larrow = " << Prev ".$numLimit." "; //You can either have an image or text, eg. Previous
$rarrow = " Next ".$numLimit." >> "; //You can either have an image or text, eg. Next
$wholePiece = "Page: "; //This appears in front of your page numbers
if ($totalrows > 0) {
$numSoFar = 1;
$cycle = ceil($totalrows/$amm);
if (!isset($numBegin) || $numBegin < 1) {
$numBegin = 1;
$num = 1;
}
$minus = $numBegin-1;
$start = $minus*$amm;
if (!isset($begin)) {
$begin = $start;
}
$preBegin = $numBegin-$numLimit;
$preStart = $amm*$numLimit;
$preStart = $start-$preStart;
$preVBegin = $start-$amm;
$preRedBegin = $numBegin-1;
if ($start > 0 || $numBegin > 1) {
$wholePiece .= "<a href='?num=".$preRedBegin
."&numBegin=".$preBegin
."&begin=".$preVBegin
.$queryStr."'>"
.$larrow."</a>\n";
}
for ($i=$numBegin;$i<=$cycle;$i++) {
if ($numSoFar == $numLimit+1) {
$piece = "<a href='?numBegin=".$i
."&num=".$i
."&begin=".$start
.$queryStr."'>"
.$rarrow."</a>\n";
$wholePiece .= $piece;
break;
}
$piece = "<a href='?begin=".$start
."&num=".$i
."&numBegin=".$numBegin
.$queryStr
."'>";
if ($num == $i) {
$piece .= "</a><b>$i</b><a>";
} else {
$piece .= "$i";
}
$piece .= "</a>\n";
$start = $start+$amm;
$numSoFar++;
$wholePiece .= $piece;
}
$wholePiece .= "\n";
$wheBeg = $begin+1;
$wheEnd = $begin+$amm;
$wheToWhe = "<b>".$wheBeg."</b> - <b>";
if ($totalrows <= $wheEnd) {
$wheToWhe .= $totalrows."</b>";
} else {
$wheToWhe .= $wheEnd."</b>";
}
$sqlprod = " LIMIT ".$begin.", ".$amm;
} else {
$wholePiece = "Sorry, no records to display.";
$wheToWhe = "<b>0</b> - <b>0</b>";
}
return array($sqlprod,$wheToWhe,$wholePiece);
}
/*
+-------+
| Usage |
+-------+
NOTE!: This is just an example and WON'T WORK if you include
this in your code, comment it out or delete it :)
*/
$criterea = " WHERE column=value AND column_2=value_2";
$sql = "SELECT COUNT(*) AS totalrows FROM table".$criterea;
//get the total amount of rows returned
$arr = mysql_fetch_array(mysql_query($sql));
/*
Call the function:
I've used the global $_GET array as an example for people
running php with register_globals turned 'off' :)
*/
$navigate = pageBrowser($arr[totalrows],10,10,"&name=value",$_GET[numBegin],$_GET[begin],$_GET[num]);
//execute the new query with the appended SQL bit returned by the function
$sql = "SELECT * FROM table".$criterea.$navigate[0];
$rs = mysql_query($sql);
//the indication of which rows are being browsed. Eg. listing 1-10 of 100 results.
echo "<p>Listing ".$navigate[1]." of ".$totalrows." results.</p>";
//the navigation bar returned by the function
echo "<p>".$navigate[2]."</p>";
//loop and display the limited records being browsed
while ($arr = mysql_fetch_array($rs)) {
echo $arr[column_1]." ".$arr[column_2]."<br>";
}
//the navigation bar at the bottom again
echo "<p>".$navigate[2]."</p>";
/*
I hope this can come in handy :)
*/
?>
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More Site Navigation Code Articles
More By Codewalkers
developerWorks - FREE Tools! |
Effective governance for lean development isn’t about command and control. Instead, the focus is on enabling the right behaviors and practices through collaborative and supportive techniques. Hear from Scott Ambler on how it is far more effective to motivate people to do the right thing than it is to force them to do so. Learn how to form a lightweight, collaboration-based framework that reflects the realities of modern IT organizations. FREE! Go There Now!
|
|
|
|
Join us for this on demand webcast to learn about developing complex systems more quickly and efficiently. We'll cover market drivers for developing, governing and reusing systems software assets and how you can develop system software assets with Rational Asset Manager. FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to download a free trial of the Rational Host Access Transformation Services (HATS) Toolkit. The HATS toolkit provides a set of plug-ins for the IBM Rational Software Delivery Platform to help you easily extend your legacy applications. HATS makes your 3270 and 5250 applications available as HTML through the most popular Web browsers, while converting your host screens to a Web look and feel and it also enables you to develop new Web, portal, and rich-client applications. FREE! Go There Now!
|
|
|
|
In this tutorial, you can learn how to install and configure the IBM Rational Asset Manager Eclipse client, explore the different views in the Asset Management perspective, learn various search techniques, work with existing assets, and submit a new asset. FREE! Go There Now!
|
|
|
|
Listen to this webcast to get an overview of Info 2.0 and a technical demo of how to quickly build an enterprise mashup. IBM's Info 2.0 technology leverages emerging Web 2.0 technologies such as mashups, feeds, AJAX, and JSON in order to simplify assembly of information using feeds and services. Come learn about the technical elements of Info 2.0 including the Feed Generation framework, Mashup Engine, and mashup assembly components. Learn how to pull information from databases, departmental information, and the Web to create mashups critical to your company’s success. We will also discuss best practices to help you get started. FREE! Go There Now!
|
|
|
|
Learn how to do more with your reusable assets with the free Rational Asset Manager eKit. The eKit includes demos on how Rational Asset Manager tracks and audits your assets in order to utilize them for reuse. Plus you’ll find white papers and a Webcast that discuss the challenges of a Service Oriented Architecture and how Rational Asset Manager can provide quick and effective solutions. FREE! Go There Now!
|
|
|
|
Informix Dynamic Server (IDS) Express Edition offers outstanding online transaction processing (OLTP) database performance, while helping to simplify and automate many of the tasks associated with deploying databases for small business applications. IDS 11 further extends the ease of management and applications integration with the Admin API and Scheduler, high availability with Continuous Log Restore for backup server recovery in case of a primary server failure, and column level encryption to protect personal and company private data. FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to try the IBM SOA Sandbox for connectivity. The SOA Sandbox for connectivity provides a trial environment with the tooling and components to help you explore how to effectively connect your infrastructure and integrate all of the people, processes and information in your company. Use the hosted sandbox to explore SOA techniques that streamline connecting existing IT assets together, as well as learn how to connect them to new business logic. FREE! Go There Now!
|
|
|
|
As businesses grow increasingly dependent upon Web applications, these complex entities grow more difficult to secure. Most companies equip their Web sites with firewalls, Secure Sockets Layer (SSL), and network and host security, but the majority of attacks are on applications themselves – and these technologies cannot prevent them. This paper explains what you can do to help protect your organization, and it discusses an approach for improving your organization’s Web application security. FREE! Go There Now!
|
|
|
|
With IBM Rational Systems Development Solution, you can deliver products faster with higher quality. Within this kit, Read the “Model Driven Systems Development” white paper to see how to improve product quality and communication. Then check out the rest of the e-Kit to learn more about important topics that can affect the success of any software project through customer examples, tutorials, informative Webcasts, and best practices for designing, building and managing systems. From start to finish, at every stage in your projects, Rational Systems Development Solution can help your company reach its full potential. FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools! |