PHP & MySQL Tutorial PHP & MySQL References

PHP & MySQL - Limit Data



The MySQL LIMIT Clause is used to fetch specified number of records from a table. This is useful when the table contains thousands of records and returning a large dataset can impact performance.

The syntax for using LIMIT Clause is given below:

SELECT column1, column2, ...
FROM table_name
WHERE condition(s)
LIMIT fetch_row_count;

Using OFFSET with LIMIT clause

The OFFSET value is most often used with the LIMIT clause. The OFFSET value allows the user to specify which row to start from to retrieve the data.

The syntax for using OFFSET keyword is given below:

/* method 1 */
SELECT column1, column2, ...
FROM table_name
WHERE condition(s)
LIMIT fetch_row_count OFFSET offset_row_count;

/* method 2 */
SELECT column1, column2, ...
FROM table_name
WHERE condition(s)
LIMIT offset_row_count, fetch_row_count;

Along with this, to connect to the MySQL server, mysqli_connect() function can be used. After establishing the connection, mysqli_query() function can be used to perform a query on the database.

The num_rows() function can be used to check if there are more than zero rows returned. Then, the fetch_assoc() function can be used to fetch the result set as an associative array. Later on the free_result() function can be used to free the memory associated with the result.

Select and Limit Data - Object-oriented style

Consider a database containing a table called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

The example below demonstrates how to use the LIMIT clause to select top 3 records from this table using object-oriented style.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDatabase";

//establishing connection 
$mysqli = new mysqli($servername, $username, $password, $dbname);

//checking connection
if ($mysqli->connect_errno) {
  echo "Failed to connect to MySQL: ". $mysqli->connect_error;
  exit();
}

//getting query result from the database
$sql = "SELECT Name, Age, Salary 
FROM Employee LIMIT 3";
$result = $mysqli->query($sql);

//fetching associative array
while ($row = $result->fetch_assoc()) {
  printf("%s, %d, %d\n", $row["Name"], $row["Age"], $row["Salary"]);
}

//free result set
$result->free_result();

//closing the connection
$mysqli->close();
?>

The output of the above code will be:

John, 25, 3000
Marry, 24, 2750
Jo, 27, 2800

Select and Limit Data - Procedural style

To obtain the same result using procedural style, the following script can be used.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDatabase";

//establishing connection 
$mysqli = mysqli_connect($servername, $username, $password, $dbname);

//checking connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: ". mysqli_connect_error();
  exit();
}

//getting query result from the database
$sql = "SELECT Name, Age, Salary 
FROM Employee LIMIT 3";
$result = mysqli_query($mysqli, $sql);

//fetching associative array
while ($row = mysqli_fetch_assoc($result)) {
  printf("%s, %d, %d\n", $row["Name"], $row["Age"], $row["Salary"]);
}

//free result set
mysqli_free_result($result);

//closing the connection
mysqli_close($mysqli);
?>

The output of the above code will be:

John, 25, 3000
Marry, 24, 2750
Jo, 27, 2800

Complete PHP MySQLi Reference

For a complete reference of all properties, methods and functions of PHP MySQLi extension, see PHP MySQLi Reference.