I have a table "songs" and column "singers". I want to perform a search on table "songs" by the name of the singer. My problem is, if the name of the singer is "Gallagher" the query gives me not only the singer "Gallagher" but also all other names with "Gallagher" in it, for example "Noel Gallagher" "Liam Gallagher", etc...
How can I refine my search to only give me "Gallagher" if the search string is "Gallagher"?
My query is this:
SELECT * FROM songs WHERE singers RLIKE \"" . $searchstring . "\" ORDER BY title
Edit: After searching stackoverflow, here is a solution you maybe looking for: MySQL - How to search for exact word match using LIKE?
What‘s you‘r searchquery exactly?
SELECT * FROM songs WHERE singers RLIKE '^Gallagher';
If you want to display 'Gallagher only then use SUBSTRING_INDEX
SELECT DISTINCT SUBSTRING_INDEX(singers, " ", -1) FROM songs WHERE singers LIKE '%{$search_string}%' ORDER BY title;
If you want search single name based on last name (surname) then you need to write below query
SELECT * FROM songs WHERE singers LIKE '%Gallagher';