Mysql Concat two columns while searching with LIKE

Question

I am trying to make a MySQL query where I filter the records based on the search text using LIKE keyword.

For example if the user searches for Illusion Softwares where Illusion is First name and Softwares is last name so the query should search for columns FirstName, LastName and concat both and search.

I have tried this so far but it does not work for CONCAT

 Select Contacts.*, Contacts.ID as CID from Contacts left join
 website_Data  on Contacts.ID = website_Data.ContactID where
 Contacts.`FirstName` LIKE '%Illusion Softwares%' or 
 Contacts.`LastName` LIKE '%Illusion Softwares%' or
 concat(Contacts.FirstName, ' ', Contacts.LastName) 
 LIKE '%Illusion Softwares%'
 or Contacts.Email LIKE '%Illusion Softwares%' 
 order by Contacts.`Created` DESC

What am I missing? Please help me.

Drew · Accepted Answer · 2015-08-24 08:48:49Z

26

Your query should be like this

Select Contacts.*, Contacts.ID as CID 
from Contacts 
left join website_Data 
on Contacts.ID = website_Data.ContactID 
where CONCAT(Contacts.FirstName,' ', Contacts.LastName) LIKE '%Illusion Softwares%' 
order by Contacts.`Created` DESC

edited Aug 24, 2015 at 8:48

Drew

25k10 gold badges47 silver badges81 bronze badges

answered Aug 24, 2015 at 8:46

AbuHuraira Lakdawala

7861 gold badge9 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mathew · Accepted Answer · 2015-08-24 13:25:44Z

1

SELECT *,c.ID as CID 
FROM Contacts c
LEFT OUTER JOIN website_Data w ON c.ID=w.ContactID
WHERE c.Email OR CONCAT_WS(' ',c.FirstName,c.LastName) LIKE '%Illusion Softwares%'
ORDER BY c.Created DESC;

edited Aug 24, 2015 at 13:25

answered Aug 24, 2015 at 9:59

Mathew

2962 silver badges7 bronze badges

Comments

Rishikant Srivastava · Accepted Answer · 2020-11-26 14:10:57Z

0

you can try this also, this worked for me,

$this->db->or_like('CONCAT(Contacts.FirstName,' ', Contacts.LastName)',$search);

answered Nov 26, 2020 at 14:10

Rishikant Srivastava

191 silver badge4 bronze badges

1 Comment

w.Daya Over a year ago

Use CONCAT_WS() instead CONCAT()

w.Daya · Accepted Answer · 2021-11-15 11:18:08Z

0

Use CONCAT_WS in your query:

$this->db->or_like('CONCAT_WS(Contacts.FirstName,' ', Contacts.LastName)',$search);

answered Nov 15, 2021 at 11:18

w.Daya

5281 gold badge8 silver badges12 bronze badges

Collectives™ on Stack Overflow

Mysql Concat two columns while searching with LIKE

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related