LIMIT
clause makes it easy to code multi page results or pagination with SQL, and is very useful on large tables. Returning a large number of records can impact on performance.SELECT * FROM table_name LIMIT row_offset, row_count;
The LIMIT clause accepts one or two parameters which must be a nonnegative integer :
Where as, when only one parameter is given, it specifies the maximum number of rows to return from the beginning of the result set.
$sql = "SELECT * FROM student_detaile_2 LIMIT 3";
To retrieve the rows 2-4 (inclusive) of a result set, you can use the following query :
$sql = "SELECT * FROM persons LIMIT 1, 3";
The 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 | Name | College | Email Id | Mobile |
---|---|---|---|---|
1 | V V Ramana Reddy | S V Arts and Science College | info@freetimelearning.com | 9963666068 |
2 | Chanti | Vidhyalaya Degree College | freetimelearn@gmail.com | 9010023210 |
3 | Ramana | S V University | vvramanareddy9963@gmail.com | 9966463846 |
4 | Suresh | Aadhishankara | sureshreddy@gmail.com | 9700900292 |
5 | Suman Reddy | Priyadharshini | sumanreddy.mca@gmail.com | 9966395199 |
<?php
$ftl_connection = mysqli_connect("localhost", "root", "", "ftl_db");
// Check connection
if($ftl_connection === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
// Attempt select query execution
$sql = "SELECT * FROM student_details_2 LIMIT 3";
if($result = mysqli_query($ftl_connection, $sql)){
if(mysqli_num_rows($result) > 0){
echo "<table class='table table-bordered'>";
echo "<tr>";
echo "<th>id</th>";
echo "<th>Name</th>";
echo "<th>College</th>";
echo "<th>Email Id</th>";
echo "<th>Mobile</th>";
echo "</tr>";
while($row = mysqli_fetch_array($result)){
echo "<tr>";
echo "<td>" . $row['stu_id'] . "</td>";
echo "<td>" . $row['stu_name'] . "</td>";
echo "<td>" . $row['college'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "<td>" . $row['mobile'] . "</td>";
echo "</tr>";
}
echo "</table>";
// Close result set
mysqli_free_result($result);
} else{
echo "No records matching your query.";
}
} else{
echo "Error could not able to execute $sql. " . mysqli_error($ftl_connection);
}
// Close connection
mysqli_close($ftl_connection);
?>
id | Name | College | Email Id | Mobile |
---|---|---|---|---|
1 | V V Ramana Reddy | S V Arts and Science College | info@freetimelearning.com | 9963666068 |
2 | Chanti | Vidhyalaya Degree College | freetimelearn@gmail.com | 9010023210 |
3 | Ramana | S V University | vvramanareddy9963@gmail.com | 9966463846 |