0

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
Sérgio Miranda
  • 63
  • 7
  • Do you want to display Gallagher only without the first name? – Bluetree Nov 07 '17 at 00:42
  • Yes if the search string is only "Gallagher".... because there is a singer named only "Gallagher". If I enter "Liam Gallagher" then I get all results by "Liam Gallagher". – Sérgio Miranda Nov 07 '17 at 00:47
  • Replace `RLIKE` with `LIKE` or `=`. `RLIKE` is a *regular expression* comparison. If we want to match an *exact* string using a regular expression, we need to use a regular expression that does that e.g. `RLIKE '^Gallagher$'`. (What about case sensitivity, is that important?) – spencer7593 Nov 07 '17 at 00:54
  • 1
    **WARNING**: This has some severe [SQL injection bugs](http://bobby-tables.com/) because user data is used inside the query. Whenever possible use **prepared statements**. These are quite straightforward to do in [`mysqli`](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [PDO](http://php.net/manual/en/pdo.prepared-statements.php) where any user-supplied data is specified with a `?` or `:name` indicator that’s later populated using `bind_param` or `execute` depending on which one you’re using. **NEVER** put `$_POST`, `$_GET` or any user data directly in your query. – tadman Nov 07 '17 at 01:14
  • ^ AKA the "Bobby Tables" problem: https://xkcd.com/327/ – Brandon Minnick Nov 07 '17 at 01:45

3 Answers3

1

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';
SoDaHo
  • 48
  • 8
0

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;
Bluetree
  • 1,324
  • 2
  • 8
  • 25
0

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';
Jaydp
  • 1,029
  • 9
  • 17