|
Recent Articles |
MySQL Named As Possible Acquisition Target
As corporations continue to merge, sell out, and gobble each other up, analysts have identified the next likely targets in the software world: MySQL and Linux.
GData, MySQL, and the Future of on-line Databases
In reading Richard MacManus' Why Google
is extending RSS, I couldn't help feeling that
he was missing the point a bit. It's as if he was
focusing on the small things ("Why RSS?") rather
than looking at the bigger picture...
MySQL, Oracle Agree On InnoDB Deal
A multiyear agreement between Oracle and MySQL over
the InnoDB storage engine license should put an
end to fears of a potential Oracle-fueled money
grab...
SQL Server Indexes
A database index is similar to an index in a book - it is comprised of a lookup value, and a number identifier that corresponds to the row number in a table.
Oracle Produces Oracle SQL Developer
The kids out in Redwood Shores announced a new product called SQL developer, known previously as Project Raptor. The product is a free database...
SQL Injections Abound
Danish security firm Secunia reported on Monday several moderately critical vulnerabilities in various software products that allow SQL injection attacks. Products like Gregarius, Total Ecommerce, Akarrus Social Bookmarking Engine and others.
Oracle Acquisitions are Not About MySQL
I've been thinking about this for the last day or so and have come to the conclusion that Oracle's acquisition of Sleepycat Software (and Berkeley DB) is not about MySQL.
Someone AJAXified mytop!
Check this out. Someone has built and AJAX powered version of mytop, the little console based MySQL monitoring tool I wrote years ago.
|
|
|
Latest Web News |
Microsoft, Yahoo Update Messengers
Late news from Yahoo reveals a new beta of their instant messaging client with plugins and a developer kit, while Microsoft ties its debut of Windows Live Messenger to Johnny Depp's forthcoming 'Pirates of the Caribbean' sequel.
Riya Offers Facial Recognition Tech
Usable facial-recognition software belongs in the realm of science fiction, right? Or perhaps it exists, but only in the some of the more technologically advanced branches of the government. Not so. The photo-sharing site Riya already uses some of that technology, and is due to incorporate more this fall.
Thoughts On Microsoft's Future
The recent Microsoft news (Scoble leaving, Gates taking a reduced role, Ray Ozzie stepping up) combined with the last 10 years worth of evolution in networking and of the web really got me wondering about Microsoft's role in the future.
Google Poised For Nielsen Raidings
Google is a media company - a dangerous one for conventional players. Mountain View's next potential rival: Nielsen Media Research, the audience measurement company that has held a virtual monopoly in the sector for decades. And it shouldn't be surpirsing. Google's MO is information collection and research.
PayPal To Google: Bring It On
PayPal President Jeff Jordan said his company is ready and waiting for any competing payment services that Google may launch. The GBuy system is supposed to come out on June 28, according to a note from RBC analyst Jordan Rohan.
Blogging From A Sinking Ship
PubSub CTO Bob Wyman was never one to pull punches.
And though the blogosphere has been a store window
for many companies, Wyman's latest blog entry detailing
not just that the company is days from bankruptcy,
but chronicling the internal political struggles
between himself and the CEO, has some wondering...
Google Talking Voice Recognition?
Senior Google executives are dropping big hints that the company's future growth will come not just from PCs but from voice recognition services over mobile cellular phones and cars.
|
|
|
06.20.06
PHP Pagination With MySQL
 By
Michael Rogne
It is extremely common these days to make results display across multiple pages. Some examples are maybe browsing through picture galleries, store products, blog entries, etc.
Unless you are a veteran programmer, this might seem a little intimidating at first. Rest assured, it is an easy task.
In order to create your own paging system, you will need to get some information first. You will need the total number of list entries, number of results per page, current page number being viewed, the total number of pages and the offset.
Lets start with the total number of list entries. In order to do this, we need some test data in a MySQL table. For demonstration purposes, we are going to use the following MySQL table:
CREATE TABLE products ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, name VARCHAR(50) NOT NULL, description TEXT NOT NULL ); INSERT INTO products (name,description) VALUES ('Product 1','Description for Product 1'); INSERT INTO products (name,description) VALUES ('Product 2','Description for Product 2'); INSERT INTO products (name,description) VALUES ('Product 3','Description for Product 3'); INSERT INTO products (name,description) VALUES ('Product 4','Description for Product 4'); INSERT INTO products (name,description) VALUES ('Product 5','Description for Product 5'); INSERT INTO products (name,description) VALUES ('Product 6','Description for Product 6'); INSERT INTO products (name,description) VALUES ('Product 7','Description for Product 7'); INSERT INTO products (name,description) VALUES ('Product 8','Description for Product 8'); INSERT INTO products (name,description) VALUES ('Product 9','Description for Product 9'); INSERT INTO products (name,description) VALUES ('Product 10','Description for Product 10');
Now that we have a MySQL table full of "fake" products, lets get the paging working!
First, we need the total number of list entries. To do this, we will use:
MySQL's COUNT() function. $result = mysql_query("SELECT COUNT(*) AS total_entries FROM products") or die(mysql_error()); $row = mysql_fetch_row($result); $total_entries = $row[0];
Now we have the total entries retrieved and stored in the $total_entries variable.
Next, we need to get the number of results per page. It is a common programming practice when developing scripts to use a central configuration file (such as config.php). For the sake of simplicity, we'll call our variable $entries_per_page.
$entries_per_page = 3;
Next, we need to get the current page number being viewed. We will call our page number variable >$page_number.
if(isset($_GET['page_number'])) { $page_number = $_GET['page_number']; } else { $page_number = 1; }
This means set $page_number to the page_number specified via query string if it exists, otherwise set it to page number 1.
Next, we need to get the total number of pages that there will be. An important thing to remember is that even if the very last page contains only one product, that one product will still be one page.
To do this, we use PHP's ceil() function. This means round up the number to the nearest integer. For example, ceil(2.1) will round up to 3. ceil(2.9) will round up to 3. In order to figure out the total number of pages, you simply do ceil(TOTAL ENTRIES / ENTRIES PER PAGE).
$total_pages = ceil($total_entries / $entries_per_page);
For this specific example, $total_pages has the value 4.
Finally, we need to determine the offset. What exactly is the "offset"? The offset is the number of the first entry to pull. It is what you use to tell MySQL where to start fetching data. For example, if we are viewing products on page two, we would want to return only products 4 through 7. If we are viewing products on page three, we would want to return only products 8 through 10. To get an offset, you simply do:
(CURRENT PAGE - 1) * ENTRIES PER PAGE
$offset = ($page_number - 1) * $entries_per_page;
Now that we have all of the data we require we are able to use MySQL to pull all products within our "page" being viewed.
$result = mysql_query("SELECT * FROM products LIMIT $offset, $entries_per_page") or die(mysql_error()); while($obj = mysql_fetch_object($result)) { // Display the data however you want here. print <<id - $obj->name - $obj->description<br>
EOD; }
And lastly, the whole point of this article is to demonstrate page numbering so now we will display the page numbers! What we are going to do is iterate through a loop that loops through all page numbers and makes them a link unless it's the current page.
for($i = 1; $i <= $total_pages; $i++) { if($i == $page_number) { // This is the current page. Don't make it a link. print "$i "; } else { // This is not the current page. Make it a link. print " <a href="products.php?page_number=$i">$i</a> "; } }
Of course, you could (and I encourage you to) experiment with the code shown here to make it display however you wish.
You can view a complete example of this script in action at http://www.phplabs.com/articles/products.php.
About the Author:
Mike is an experienced web developer with a wide range of programming capabilities. He has been delivering top quality web applications for several years primarily using Perl/CGI and PHP combined with MySQL.
|
