MySQL Limit Query Implementation in PHP

MySQL Limit Query generally used in pagination purpose. I will show you how you can implement in PHP and MySQL.

First of all, let’s see the syntax of SQL limit.

MySQL Limit Syntax

SELECT *
FROM table_name
LIMIT start_from, total_results;

This is the basic syntax where…

• table_name shows the database table name
• start_from shows the starting value from table
• total_results shows the number of total results to print

Why we have to use MySQL Limit?

Developers or programmer make the listing page for list out all the details from the database. At that time, if you have small number of results in the database then no worries to fetch results. Because it will give you results immediately. But if you have a large number of data like 1000 rows to fetch then your page will take time to load. Sometimes it takes a very long time and users get panic from it.

This is the time to use the Limit Query. It will help you to fetch 10 or 20 number of results from the database. The best part is it will take very less time to load and a user can get the results very quickly. You can get full tutorial for pagination in php here.

Now, let’s take some examples for get more idea.

Example – 1

If you want to print just 5 results from database table then you have to write query like below.

SELECT id,name
FROM admin
LIMIT 5;

Output:

Example – 2

Now, if you want to fetch results which is start from 6 to 10. Below query will help you to do this.

SELECT id,name
FROM admin
LIMIT 6, 5;

In above query 6 is for starting point and 5 is for number of results you want.

Output:

SQL Limit Implementation in PHP

Now, I will show you the MySQL Limit implementation in PHP. Just follow the code below.

<?php 

// Database connection
$con = mysqli_connect("DB_host", "DB_Username", "DB_Password", "DB_Name");

if ($con === false) {
	die("ERROR:: Database not connected. ".mysqli_error($con));
}

// Select first 5 results
$query = "SELECT * FROM name LIMIT 5";
$result = mysqli_query($con, $query) or die ('Error :: in fetch results'.mysqli_error($con));

// Print results in table format
if (mysqli_num_rows($result) > 0)
{
?>
< table border="1">
< tr>
< th><b>ID</a></ th>
< th><b>Name</b></ th>
</ tr>
<?php while ($row = mysqli_fetch_array($result)) { ?>
< tr>
< td><?php echo $row['id']; ?></ td>
< td><?php echo $row['name']; ?></ td>
</ tr>
<?php } ?>
</ table>
<?php

mysqli_free_result($result);
}
else {
  echo "No results found!";
}

// Close database connection
mysqli_close($con);

?> 

Output:

You will get the output table like this.

Releated Articles:

• MySQL Insert Query
• MySQL Update Query