English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية
In this tutorial, you will learn how to read a limited number of records from a MySQL database table using PHP.
LIMITThe clause is used to limitSELECTThe number of rows returned by the statement. This feature is very helpful for optimizing page load time and enhancing the readability of the website. For example, you can use pagination to divide a large number of records into multiple pages, and when the user requests a page by clicking on the pagination link, a limited number of records will be loaded from each page of the database.
The basic syntax of the LIMIT clause can be given in the following way:
SELECT column_name(s) FROM table_name LIMIT row_offset, row_count;
The LIMIT clause accepts one or two non-negative integer parameters:
When two parameters are specified, the first parameter specifies the offset of the first row to be returned, that is, the starting point, while the second parameter specifies the number of rows to be returned. The offset of the first row is 0 (not1).
When only one parameter is given, it specifies the maximum number of rows to return from the beginning of the result set.
For example, to retrieve the first three rows, you can use the following query:
SELECT * FROM persons LIMIT 3;
To retrieve the record set at the2-4rows (including), you can use the following query:
SELECT * FROM persons LIMIT 1, 3;
Let us use the LIMIT clause in the SELECT statement to perform an SQL query, and then execute this query by passing it to the PHP mysqli_query() function to obtain a limited number of records. Consider the following persons table in the demonstration database:
+----+------------+-----------+----------------------+ | id | first_name | last_name | email | +----+------------+-----------+----------------------+ | 1 | Peter | Parker | [email protected] | | 2 | John | Rambo | [email protected] | | 3 | Clark | Kent | [email protected] | | 4 | John | Carter | [email protected] | | 5 | Harry | Potter | [email protected] | +----+------------+-----------+----------------------+
The PHP code in the following example only showspersonsThree rows in the table.
<?php
/*Attempt to connect to the MySQL server. Assume you are running MySQL.
Server with default settings (user "root" without a password) */
$link = mysqli_connect("localhost", "root", "", "demo");
//Check connection
if($link === false){
die("Error: Unable to connect. " . mysqli_connect_error());
}
//Attempt to select query execution
$sql = "SELECT * FROM persons LIMIT 3";
if($result = mysqli_query($link, $sql)){
if(mysqli_num_rows($result) > 0){
echo "<table>";
echo "<tr>";
echo "<th>id</th>";
echo "<th>first_name</th>";
echo "<th>last_name</th>";
echo "<th>email</th>";
echo "</tr>";
while($row = mysqli_fetch_array($result)){
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['first_name'] . "</td>";
echo "<td>" . $row['last_name'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
//Close result set
mysqli_free_result($result);
} else{
echo "No records matching your query were found.";
}
} else{
echo "Error: Unable to execute $sql. " . mysqli_error($link);
}
//Close the connection
mysqli_close($link);
?><?php
/*Attempt to connect to the MySQL server. Assume you are running MySQL.
Server with default settings (user "root" without a password) */
$mysqli = new mysqli("localhost", "root", "", "demo");
//Check connection
if($mysqli === false){
die("Error: Unable to connect. " . $mysqli->connect_error);
}
//Attempt to select query execution
$sql = "SELECT * FROM persons LIMIT 3";
if($result = $mysqli->query($sql)){
if($result->num_rows > 0){
echo "<table>";
echo "<tr>";
echo "<th>id</th>";
echo "<th>first_name</th>";
echo "<th>last_name</th>";
echo "<th>email</th>";
echo "</tr>";
while($row = $result->fetch_array()){
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['first_name'] . "</td>";
echo "<td>" . $row['last_name'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
//Release the result set
$result->free();
} else{
echo "No records matching your query were found.";
}
} else{
echo "Error: Unable to execute $sql. " . $mysqli->error;
}
//Close the connection
$mysqli->close();
?><?php
/*Attempt to connect to the MySQL server. Assume you are running MySQL.
Server with default settings (user "root" without a password) */
try{
$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", \
//Set PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("Error: Unable to connect. " . $e->getMessage());
}
//Attempt to select query execution
try{
$sql = "SELECT * FROM persons LIMIT 3";
$result = $pdo->query($sql);
if($result->rowCount() > 0){
echo "<table>";
echo "<tr>";
echo "<th>id</th>";
echo "<th>first_name</th>";
echo "<th>last_name</th>";
echo "<th>email</th>";
echo "</tr>";
while($row = $result->fetch());
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['first_name'] . "</td>";
echo "<td>" . $row['last_name'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
//Release the result set
unset($result);
} else{
echo "No records matching your query were found.";
}
} catch(PDOException $e){
die("Error: Unable to execute $sql. " . $e->getMessage());
}
//Close the connection
unset($pdo);
?>After limiting the result set, the output will be as follows:
+----+------------+-----------+----------------------+ | id | first_name | last_name | email | +----+------------+-----------+----------------------+ | 1 | Peter | Parker | [email protected] | | 2 | John | Rambo | [email protected] | | 3 | Clark | Kent | [email protected] | +----+------------+-----------+----------------------+