Sorting, searching and pagination

Sorting:

While retrieving information from database, you can sort information or data as per your requirement. Like in some case, if you want to show the latest entered value in the table then you can show data in descending order of primary key. Similarly you can also show data displayed according to the name in ascending or decending order. Use "ORDER BY" Keyword.



For example:

Here is the below table of data retrieved from table in a database.

S.No.	Name	Email
1	Reeta	reeta@gmail.com
2	Anuj	anuj@gmail.com

 Here, we have retrieve above information from table "info" then we have used the following SQL.

SELECT * FROM INFO

Now we want to sort the above retrieved information in ascending order by Name then we can use the SQL as below:

SELECT * FROM INFO ORDER BY NAME ASC

Here, with above SQL, we will get result as below:

S.No.	Name	Email
1	Anuj	anuj@gmail.com
2	Reeta	reeta@gmail.com

If you want to order descending then simply use DESC instead of ASC

I hope you get it, right? :)

Searching:

In any application or website, you need to search something time and again. So search plays a vital role. Use "LIKE" operator.

Let's assume we want to search name "Anuj" in the above table called "info". For that we need to run SQL as below:

SELECT * FROM INFO WHERE NAME LIKE  'A%'

Here, with the above SQL we can list all the names starting with alphabet "A". The percentage (%) sign will extract all the other character rather then A means, here it will only check the first character "A" and then shows all data.

Pagination:

To paginate use keyword called "LIMIT" as below:

SELECT * FROM INFO LIMIT 0, 10

This will display the first 10 results from the database.

SELECT * FROM INFO LIMIT 2,2

This will show records starting from 2 and shows row 2 and 3.

The MySQL limit syntax can either return the first so many rows from a database table, or it can also return a range of rows from the database table.