PHP MySQL Limit

In this tutorial you will learn how to fetch limited number of records from a MySQL database table using PHP.

## Limiting Result Sets

The [LIMIT](http://www.bixiaguangnian.com/manual/sql/3398.html "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:

```mysql
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:

```mysql
SELECT * FROM persons LIMIT 3;
```

To retrieve the rows 2-4 (inclusive) of a result set, you can use the following query:

```mysql
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:

The PHP code in the following example will display just three rows from the persons table.

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

Last modification：September 14, 2024