How to Limit the Number of Rows Returned From a MySQL Table Using PHP



PHP







In this article, we show how you can limit the number of results returned from a MySQL query using PHP.

You can limit the numbers of rows returned to any number that you want.

This has good application when you want to limit the number of results to a certain mount. Say, you want to show the cheapest hotels and you have a list of 50 hotels. You can decide to show any amount, such as the 10 cheapest hotels or the 5 cheapest hotels.

To do this in MySQL, you use the LIMIT keyword and after this keyword you put in the number of rows you want returned based on the query.



So the above is the MySQL code to select all columns of the table named table_name and limit the amount of rows returned to 5 rows.

We'll now show how to tie this in with PHP.

PHP Code

The PHP code to limit the number of rows returned is shown below.



So the following code gets the data required to make a connection to a database and actually selects a database.

We then create a variable named $result that queries the table, table_name, and selects the first 5 rows from the table.

You then can later decide what you want to do with these selected rows.

I created a MySQL table called PopularToys. This is shown below.



MySQL PopularToys table


Applying the PHP code above and displaying the rows in tabular form, the following result is achieved.

Actual PHP Output


Sky Viper Drone 70.00
Remote-controlled car 25.00
Remote-controlled helcopter 150.00
Stuffed teddy bear 10.00
Star wars puzzle 12.00


Combine the LIMIT Keyword with Another Criterion

So the LIMIT function many times will be combined with another search criterion.

Say, we want to show the 5 cheapest items in a MySQL table. We would combine the LIMIT keyword with ORDER BY price.

Below is the following PHP code to select the 5 cheapest items in the table.



So the above PHP code selects the columns, toy and price, from the PopularToys table and orders the rows according from lowest to highest price and limits the rows returned to 5. So this query selects the 5 cheapest prices in the table.

Using the same table, PopularToys, and adding, ORDER BY price LIMIT 5, we now get the following result.

Actual PHP Output


Stuffed teddy bear 10.00
Star wars puzzle 12.00
Remote-controlled car 25.00
Super water slide 50.00
Sky Viper Drone 70.00


User Can Decide How Many Results Are Returned For a Certain Query

Many websites, such as ebay, lets a user decide how many results are returned on a page for a given search query.

Normally they are put in dropdown menus and the user can specify how many results he or she wants returned on a page.

You can see the following example below.

A dropdown menu is created so a user can specify how many results are returned.

How Many Results To Show?






Related Resources

How to Modify the Defintion or Data Type of a Column of a MySQL Table Using PHP

How to Insert Data into a MySQL Table Using PHP

How to Delete a Row of a MySQL Table Using PHP

How to Delete All Rows of a MySQL Table Using PHP

How to Insert Data Into a Table of a MySQL Database From an HTML Form Using PHP

How to Update a Value in a MySQL Table Using PHP

How to Copy a Table Definition in MySQL Using PHP

HTML Comment Box is loading comments...