PHP MySQL - LIMIT clause

Let’s discuss how to use LIMIT Clause through PHP to connect with the MySQL database in the XAMPP Server.

Consider the table - treatments present in the database - hospital snapshot.

Schema:

 

Here, there are 4 columns with the following data types:

 

1.      Treatment_ID with int type of length - 2

2.      Treatment_Name with varchar type of length - 100

3.      Treatment_Doctor with varchar type of length - 100

4.      Doctor_Experience with int type of length - 2.

 

Records:

There are 6 records in the treatments table.

 

Now we will see about the LIMIT clause.

 

LIMIT in MySQL is used to return partivular number of records from the top of the table.

 

SQL LIMIT - Syntax

 

SELECT columns… from TABLE_NAME LIMIT N

As we know that columns refers to the column names that exist in the given table (TABLE_NAME).

N specifies the integer value to display records from top.

 

PHP MySQL - Object oriented Connection with LIMIT

 

We will see how to connect PHP with a MySQL database in XAMPP Server using PHP Script using Object oriented Format.

 

Approach

1.Specify the credentials to create a connection object.
The credentials include XAMPP Server localhost name, username, password and database name.
$connecting_data =  new mysqli($server, $user, $pwd, $data);

2.Specify the SQL Query that includes the LIMIT clause.
$my_query = SELECT columns… from TABLE_NAME LIMIT N

3.Get the query into the connection object.
$return = $connecting_data->query($my_query);

4.Return the records from the result query.
if ($return->num_rows > 0) {
 
  while($record_data = $return->fetch_assoc()) {

   // Display the data
  }
} else {
  echo "No Data";
}

5.Close the connection object
$connecting_data->close();

Example 1-

Return only top 3 records using LIMIT clause.

<?php

// Specify the server
$server = "localhost";

// Specify the user
$user = "root";

// Specify the password
$pwd = "";

// Specify the database 
$data = "hospital";

// Let's create connection by using the above details
$connecting_data =  new mysqli($server, $user, $pwd, $data);

// Mention the SQL Query to return only first 3 records.
$my_query = "SELECT * FROM treatments LIMIT 3";


$return = $connecting_data->query($my_query);

if ($return->num_rows > 0) {
 
  while($record_data = $return->fetch_assoc()) {

    echo "Treatment ID: " . $record_data["Treatment_ID"]. " , Treatment Name: " . $record_data["Treatment_Name"]. " , Doctor: " . $record_data["Treatment_Doctor"]. " and Experience: " . $record_data["Doctor_Experience"]. "<br>";
  }
} else {
  echo "No Data";
}

$connecting_data->close();
?>

Output:

We can see that only top 3 records were returned.

 

It can be possible to specify LIMIT with ORDER BY.

In this case, first the records inside the table is ordered  and then it will return particular rows from top using LIMIT.

 

Example 2:

Sort the records in Descending order by Treatment_ID and return first 2 records.

<?php

// Specify the server
$server = "localhost";

// Specify the user
$user = "root";

// Specify the password
$pwd = "";

// Specify the database 
$data = "hospital";

// Let's create connection by using the above details
$connecting_data =  new mysqli($server, $user, $pwd, $data);

$my_query = "SELECT * FROM treatments ORDER BY Treatment_ID DESC LIMIT 2";


$return = $connecting_data->query($my_query);

if ($return->num_rows > 0) {
 
  while($record_data = $return->fetch_assoc()) {

    echo "Treatment ID: " . $record_data["Treatment_ID"]. " , Treatment Name: " . $record_data["Treatment_Name"]. " , Doctor: " . $record_data["Treatment_Doctor"]. " and Experience: " . $record_data["Doctor_Experience"]. "<br>";
  }
} else {
  echo "No Data";
}

$connecting_data->close();
?>

Output:

So we can see that only first 2 records were returned from the records sorted in Descendng order by Treatment_ID.

 

Example 3:

Sort the records in Ascending order by Treatment_ID and return first 5 records.

<?php

// Specify the server
$server = "localhost";

// Specify the user
$user = "root";

// Specify the password
$pwd = "";

// Specify the database 
$data = "hospital";

// Let's create connection by using the above details
$connecting_data =  new mysqli($server, $user, $pwd, $data);

$my_query = "SELECT * FROM treatments ORDER BY Treatment_ID ASC LIMIT 5";


$return = $connecting_data->query($my_query);

if ($return->num_rows > 0) {
 
  while($record_data = $return->fetch_assoc()) {

    echo "Treatment ID: " . $record_data["Treatment_ID"]. " , Treatment Name: " . $record_data["Treatment_Name"]. " , Doctor: " . $record_data["Treatment_Doctor"]. " and Experience: " . $record_data["Doctor_Experience"]. "<br>";
  }
} else {
  echo "No Data";
}

$connecting_data->close();
?>

Output:

 

So we can see that only first 5 records were returned from the records sorted in Ascending order by Treatment_ID.

 

Conclusion

So by the end of this article, we saw how to perform PHP-MySQL query with LIMIT clause in XAMPP Server by running PHP Script.