Apply search in mysql table multiple columns using mysql concat function

Here i am going to discuss about MySql very useful function called CONCAT(“CAL1″, ” “, “CAL2″……). Sometimes we need to create search feature for our application by which we can input any keyword in search box and apply search from multiple columns.
Some beginner developer use OR to apply search from multiple columns which makes slow query execution and your application takes more time to display search result. So in this topic i’ll tell you how to use CONCAT mysql function to do this task.

Suppose you have sample user profile table with some basic user profile column like name, address, email id and phoneno.

USER_PROFILE

What we need to do to create single global search box by which user can enter any keyword like name, address, phoneno to search user.

So your mysql query will be.

$search = $_REQUEST['search'];
$query = "SELECT * FROM USER_PROFILE WHERE CONCAT(FIRSTNAME, ' ', LASTNAME, ' ', ADDRESS, ' ', EMAILID, ' ', PHONENO) LIKE '%".$search."%'";

By using above query you can easily concat multiple column in one virtual column and apply search easily.

You can also use concat for displaying two column in one single virtual column,
Suppose you have mysql table with firstname and lastname column and you have to create a fullname virtual column to display user’s full name

Your query will be..

SELECT CONCAT(FIRSTNAME, ' ', LASTNAME) AS FULLNAME FROM USER_PROFILE

OUTPUT:

If you like this post please don’t forget to subscribe my public notebook for more useful stuff

Related