How to Use REGEXP function to find and Sort the Fetch Data in MySQL

by Ahmed Khan  February 11, 2016

In the previous installment of this MySQL series, I highlighted how to join tables in MySQL. In this tutorial, we will learn about using regular expressions to find data in a table and sorting fetched data in ascending and descending order. We will be using the same selectdata function which we have created in our crud.php file and same index.php file to test it. Let’s get started.

REGEXP in mysql

REGEXP

REGEXP is the function in MySQL which uses a regular expression to find data from our database. Regular expression are patterns on which values are matched to learn about regular expressions. Visit here, this is one of the best site to learn and practice your regex. The schema for regex is simple:

SELECT field1, field2,…fieldN FROM table_name1

WHERE column_name REGEXP ‘expression’

Now let us apply this on our data. We will be finding those name which matches our regular expression. Our query is:

Our regular expression matches only those names which have only alphabets in them. Now change the $sql query with the above in index.php and run it on your browser you will get this result.

image1

Sorting Result:

MySQL provides you function ORDER BY which can help you in sorting your data while fetching it from tables. There are two ways you can sort your data

  1. Ascending
  2. Descending

The schema for the sorting query is:

SELECT field1, field2,…fieldN
FROM table_name1, table_name2…
ORDER BY field1, [field2…] [ASC [DESC]]

We now make our query using same technique. We will get name and email from table myguests and sort the result by name in ascending order. Your $sql query in your index.php will be:

You will get the following result after running this query:

image2

As you can see the result is sorted in alphabetical order and in ascending order.

Conclusion:

Here is a short introduction and list of major topics in this MySQL series.

In our series, we learned all the things which can help you in creating your table, selecting data from your tables on different conditions, and we also learned how to join two different tables which exists in our database. I hope you found this tutorial series on MySQL helpful. If you find anything which you are not able to understand from this tutorial series, you can contact me on my email or comment below. Remember, Cloudways offers you the services of managed MySQL hosting with FREE trial. You can launch a server now and start doing all that you have learned in the series on the managed Cloudways server.

Start Creating Web Apps on Managed Cloud Servers Now!

Easy Web App Deployment for Agencies, Developers and E-Commerce Industry

About Ahmed Khan

Ahmed was a PHP community expert at Cloudways - A Managed PHP Hosting Cloud Platform. He is a software engineer with extensive knowledge in PHP and SEO. He loves watching Game of Thrones is his free time. Follow Ahmed on Twitter to stay updated with his works. You can email him at ahmed.khan@cloudways.com

Stay Connected:

You Might Also Like...