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.
|ROHIT||KUMAR||BARRA KANPUR UTTAR PRADESH 208027||[email protected]||999999999|
|MANISH||KUMAR||OKHALA DELHI||[email protected]||8888888888|
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.
<input type="text", name="search" placeholder="Input search keyword">
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