PHP MySQL LIMIT Clause

 

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).
  • Whereas, 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 query through passing it to the PHP mysqli_query() function to get the limited number of records. Consider the following persons table inside the demo database:

+----+------------+-----------+----------------------+
| id | first_name | last_name | email                |
+----+------------+-----------+----------------------+
|  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 PHP code in the following example will display just three rows from the persons table.


Code

<?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 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: 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:

+----+------------+-----------+----------------------+
| id | first_name | last_name | email                |
+----+------------+-----------+----------------------+
|  1 | Peter      | Parker    | peterparker@mail.com |
|  2 | John       | Rambo     | johnrambo@mail.com   |
|  3 | Clark      | Kent      | clarkkent@mail.com   |
+----+------------+-----------+----------------------+




0 Comments