PHP | MySQL LIMIT Clause

In MySQL the LIMIT clause is used with the SELECT statement to restrict the number of rows in the result set. The Limit Clause accepts one or two arguments which are offset and count.The value of both the parameters can be zero or positive integers.

Offset:It is used to specify the offset of the first row to be returned.
Count:It is used to specify the maximum number of rows to be returned.

The Limit clause accepts one or two parameters, whenever two parameters are specified, the first is the offset and the second denotes the count whereas whenever only one parameter is specified, it denotes the number of rows to be returned from the beginning of the result set.

Syntax:

SELECT column1, column2, ...
FROM table_name
LIMIT offset, count;

You can learn about LIMIT clause in details in article MySQL | LIMIT Clause.

Let us consider the following table “Data” with three columns “Firstname”, “Lastname” and “Age”.

To retrieve the first three rows from the table “Data”, we will use the following query:

SELECT * FROM Data LIMIT 3;

To retrieve the rows 2-3(inclusive) from the table “Data”, we will use the following query:

SELECT * FROM Data LIMIT 1, 2;

Below is the PHP implementation of the query to display first two rows of the table “Data” using LIMIT clause in both procedural and object-oriented extensions:

1. Limit Clause using Procedural Method

   < ? php $link = mysqli_connect("localhost", "root", "", "Mydb");
   
   if ($link == = false) {
       die("ERROR: Could not connect. ".mysqli_connect_error());
   }
   
   $sql = "SELECT * FROM Data LIMIT 2";
   if ($res = mysqli_query($link, $sql)) {
       if (mysqli_num_rows($res) > 0) {
           echo "<table>";
           echo "<tr>";
           echo "<th>Firstname</th>";
           echo "<th>Lastname</th>";
           echo "<th>Age</th>";
           echo "</tr>";
           while ($row = mysqli_fetch_array($res)) {
               echo "<tr>";
               echo "<td>".$row['Firstname']."</td>";
               echo "<td>".$row['Lastname']."</td>";
               echo "<td>".$row['Age']."</td>";
               echo "</tr>";
           }
           echo "</table>";
           mysqli_free_result($res);
       }
       else {
           echo "No matching records are found.";
       }
   }
   else {
       echo "ERROR: Could not able to execute $sql. ".mysqli_error($link);
   }
   
   mysqli_close($link);
   ? >

   Output :
   
   

   Explanation:

   1. The “res” variable stores the data that is returned by the function mysql_query().
   2. Everytime mysqli_fetch_array() is invoked, it returns the next row from the res() set.
   3. The while loop is used to loop through all the rows of the table “data”.
2. Limit Clause using Object Oriented Method

   < ? php $mysqli = new mysqli("localhost", "root", "", "Mydb");
   
   if ($mysqli == = false) {
       die("ERROR: Could not connect. ".$mysqli->connect_error);
   }
   
   $sql = "SELECT * FROM Data LIMIT 2";
   if ($res = $mysqli->query($sql)) {
       if ($res->num_rows > 0) {
           echo "<table>";
           echo "<tr>";
           echo "<th>Firstname</th>";
           echo "<th>Lastname</th>";
           echo "<th>Age</th>";
           echo "</tr>";
           while ($row = $res->fetch_array()) {
               echo "<tr>";
               echo "<td>".$row['Firstname']."</td>";
               echo "<td>".$row['Lastname']."</td>";
               echo "<td>".$row['Age']."</td>";
               echo "</tr>";
           }
           echo "</table>";
           $res->free();
       }
       else {
           echo "No matching records are found.";
       }
   }
   else {
       echo "ERROR: Could not able to execute $sql. ".$mysqli->error;
   }
   
   $mysqli->close();
   ? >

   Output :
   

3. Limit Clause using PDO Method

   < ? php try {
       $pdo = new PDO("mysql:host=localhost;dbname=Mydb", "root", "");
       $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   }
   catch (PDOException $e) {
       die("ERROR: Could not connect. ".$e->getMessage());
   }
   
   try {
       $sql = "SELECT * FROM Data LIMIT 2";
       $res = $pdo->query($sql);
       if ($res->rowCount() > 0) {
           echo "<table>";
           echo "<tr>";
           echo "<th>Firstname</th>";
           echo "<th>Lastname</th>";
           echo "<th>Age</th>";
           echo "</tr>";
           while ($row = $res->fetch()) {
               echo "<tr>";
               echo "<td>".$row['Firstname']."</td>";
               echo "<td>".$row['Lastname']."</td>";
               echo "<td>".$row['Age']."</td>";
               echo "</tr>";
           }
           echo "</table>";
           unset($res);
       }
       else {
           echo "No matching records are found.";
       }
   }
   catch (PDOException $e) {
       die("ERROR: Could not able to execute $sql. ".$e->getMessage());
   }
   
   unset($pdo);
   ? >

   Output :