Categories:
.NET (357)
C (330)
C++ (183)
CSS (84)
DBA (2)
General (7)
HTML (4)
Java (574)
JavaScript (106)
JSP (66)
Oracle (114)
Perl (46)
Perl (1)
PHP (1)
PL/SQL (1)
RSS (51)
Software QA (13)
SQL Server (1)
Windows (1)
XHTML (173)
Other Resources:
How To Break Output into Pages
How To Break Output into Pages? - MySQL FAQs - Managing Tables and Running Queries with PHP Scripts
✍: FYIcenter.com
If you have a query that returns hundreds of rows, and you don't want to present all of them to your users on a single page. You can break output into multiple pages, and only present 10 rows per page like what Google Website is doing. To do this, you need to modify your query with the LIMIT clause to return rows starting from the correct row number, and limited to 10 rows in the output.
The tutorial exercise below shows you how to break output into pages with 2 rows per page. It also calcualtes the total number of pages with a simple query criteria and order by condition. As an example, rows for page number 2 are returned.
<?php include "mysql_connection.php"; $rowsPerPage = 2; $where = " WHERE url LIKE '%co%'"; $order = " ORDER BY time DESC"; $curPage = 2; $start = ($curPage-1) * $rowsPerPage; $sql = "SELECT COUNT(*) AS count FROM fyi_links" . $where . $order; print("SQL = $sql\n"); $rs = mysql_query($sql, $con); $row = mysql_fetch_assoc($rs); $numberOfPages = $row['count'] / $rowsPerPage; print("Number of pages = $numberOfPages\n"); mysql_free_result($rs); $sql = "SELECT * FROM fyi_links" . $where . $order . " LIMIT ".$start.", ".$rowsPerPage; print("SQL = $sql\n"); $rs = mysql_query($sql, $con); while ($row = mysql_fetch_assoc($rs)) { print($row['id'].", ".$row['url'].", " . $row['notes'].", ".$row['time']."\n"); } mysql_free_result($rs); mysql_close($con); ?>
If you run this script, you will get something like this:
SQL = SELECT COUNT(*) AS count FROM fyi_links WHERE url LIKE '%co%' ORDER BY time DESC Number of pages = 3 SQL = SELECT * FROM fyi_links WHERE url LIKE '%co%' ORDER BY time DESC LIMIT 2, 2 101, dev.fyicenter.com, , 2006-07-01 20:24:46 102, dba.fyicenter.com, Nice site., 2006-07-01 20:24:46
2007-05-10, 6079👍, 0💬
Popular Posts:
How can I implement a thread-safe JSP page? You can make your JSPs thread-safe by having them implem...
Describe in detail Basic of SAO architecture of Remoting? For these types of questions interviewer e...
How To Use Subqueries with the IN Operator? - MySQL FAQs - SQL SELECT Statements with JOIN and Subqu...
How to set a HTML document's background color? document.bgcolor property can be set to any appropria...
How To Enter Boolean Values in SQL Statements? - MySQL FAQs - Introduction to SQL Basics If you want...