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 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:
$sql = "SELECT CONCAT(myguests.firstname,' ',myguests.lastname) AS name FROM `myguests` WHERE CONCAT(myguests.firstname,' ',myguests.lastname) REGEXP '[A-Z]'"
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.
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
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:
$sql = "SELECT CONCAT(myguests.firstname,' ',myguests.lastname) AS name, email FROM `myguests` ORDER by CONCAT(myguests.firstname,' ',myguests.lastname) ASC"
You will get the following result after running this query:
As you can see the result is sorted in alphabetical order and in ascending order.
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.