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

    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    < ? 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);
    ? >

    chevron_right

    
    

    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

    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    < ? 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();
    ? >

    chevron_right

    
    

    Output :

  3. Limit Clause using PDO Method

    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    < ? 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);
    ? >

    chevron_right

    
    

    Output :



My Personal Notes arrow_drop_up

I am a technology enthusiast who has a keen interest in programming I am pursuing Engineering in Computer Science from GEU, Dehradun I like to unwind by watching movies and English sitcomsI have a keen interest in music

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.