Collections:
Breaking Query Output into Pages in MySQL
How To Break Query Output into Pages in MySQL?
✍: 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 calculates 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
⇒ Updating Existing Rows in MySQL
⇐ Looping through Query Output Rows in MySQL
2022-10-01, 14899🔥, 1💬
Popular Posts:
What Is ISAM in MySQL? ISAM (Indexed Sequential Access Method) was developed by IBM to store and ret...
How To Verify Your PHP Installation in MySQL? PHP provides two execution interfaces: Command Line In...
How To Use "IF ... ELSE IF ..." Statement Structures in SQL Server Transact-SQL? "IF ... ELSE IF ......
How To Generate CREATE VIEW Script on an Existing View in SQL Server? If you want to know how an exi...
What are binary literals supported in SQL Server Transact-SQL? Binary literals in Transact-SQL are s...