PHP MySQL LIMIT Clause

The LIMIT clause is used to specify the number of records to return.

Limiting Result Sets

The LIMIT clause is used to constrain the number of rows returned by the SELECT statement. This feature is very helpful for optimizing the page loading time as well as to enhance the readability of a website. For example you can divide the large number of records in multiple pages using pagination, where limited number of records will be loaded on every page from the database when a user request for that page by clicking on pagination link.

The basic syntax of the LIMIT clause can be given with:

SELECT column_name(s) FROM table_name LIMIT row_offset, row_count;

The LIMIT clause accepts one or two parameters which must be a nonnegative integer:

  • When two parameters are specified, the first parameter specifies the offset of the first row to return i.e. the starting point, whereas the second parameter specifies the number of rows to return. The offset of the first row is 0 (not 1).
  • 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 rows 2-4 (inclusive) of a result set, you can use the following query:

SELECT * FROM persons LIMIT 1, 3;

Let's make a SQL query using the LIMIT clause in SELECT statement, after that we will execute this SQL query through passing it to the mysqli_query() function to get the limited number of records. Consider the following "persons" table inside the "demo" database:

+-----------+------------+-----------+----------------------+
| person_id | first_name | last_name | email_address        |
+-----------+------------+-----------+----------------------+
|         1 | Peter      | Parker    | peterparker@mail.com |
|         2 | John       | Rambo     | johnrambo@mail.com   |
|         3 | Clark      | Kent      | clarkkent@mail.com   |
|         4 | John       | Carter    | johncarter@mail.com  |
|         5 | Harry      | Potter    | harrypotter@mail.com |
+-----------+------------+-----------+----------------------+

The following PHP code display just three rows (2, 3 and 4th) from the persons table:

Example

Download
  • <?php
  • /* Attempt MySQL server connection. Assuming you are running MySQL
  • server with default setting (user 'root' with no password) */
  • $link = mysqli_connect("localhost", "root", "", "demo");
  •  
  • // Check connection
  • if($link === false){
  •     die("ERROR: Could not connect. " . mysqli_connect_error());
  • }
  •  
  • // Attempt select query execution
  • $sql = "SELECT * FROM persons LIMIT 1, 3";
  • if($result = mysqli_query($link, $sql)){
  •     if(mysqli_num_rows($result) > 0){
  •         echo "<table>";
  •             echo "<tr>";
  •                 echo "<th>person_id</th>";
  •                 echo "<th>first_name</th>";
  •                 echo "<th>last_name</th>";
  •                 echo "<th>email_address</th>";
  •             echo "</tr>";
  •         while($row = mysqli_fetch_array($result)){
  •             echo "<tr>";
  •                 echo "<td>" . $row['person_id'] . "</td>";
  •                 echo "<td>" . $row['first_name'] . "</td>";
  •                 echo "<td>" . $row['last_name'] . "</td>";
  •                 echo "<td>" . $row['email_address'] . "</td>";
  •             echo "</tr>";
  •         }
  •         echo "</table>";
  •         // Close result set
  •         mysqli_free_result($result);
  •     } else{
  •         echo "No records matching your query were found.";
  •     }
  • } else{
  •     echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
  • }
  •  
  • // Close connection
  • mysqli_close($link);
  • ?>

After limiting the result set the output will look something like this:

+-----------+------------+-----------+---------------------+
| person_id | first_name | last_name | email_address       |
+-----------+------------+-----------+---------------------+
|         2 | John       | Rambo     | johnrambo@mail.com  |
|         3 | Clark      | Kent      | clarkkent@mail.com  |
|         4 | John       | Carter    | johncarter@mail.com |
+-----------+------------+-----------+---------------------+
 
Close

Your Feedback:

 

We would love to hear from you! Please say something.