Query to return exact match for item in comma delimited string using MYSQL and PHP

Question

I am struggling with writing a MYSQL query in PHP that can find an exact match with one item in a comma delimited string. Note, for various reasons I cannot change the data model

Musicians
id|artist
1|beatles
2|rolling stones

$searchterm = "beatles"

It is easy to match the string exactly with:

WHERE artist = '$searchterm'

How could I determine an exact match in the following

Musicians
id|artist
1|beatles,the beatles,the fab four,fab four,fab for
2|rolling stones,the rolling stones,stones,the stones

$searchterm = "fab four"

//something like the following although it does not work

WHERE $searchterm IN artist

Thanks for any suggestions

score 1 · Accepted Answer · answered May 28 '20 at 13:40

1

You could use find_in_set():

WHERE FIND_IN_SET(:searchterm, artist)

Important notes:

do not concatenate variables into the query string, as this makes your code inefficient and exposes you to SQL injection; instead, do use parameterized queries. Related reading: How can I prevent SQL injection in PHP?
storing delimited lists in a database column is bad practice; it basically defeats the purpose of a relational databases, and makes simple things unecessarily complicated (as you are starting to see). Recommended related reading: Is storing a delimited list in a database column really that bad?

answered May 28 '20 at 13:40

GMB

216,147
25
84
135

Thx I realize it is suboptimal in a dbase such as MYSQL. However, I can't change the data model and have to work with what is there. – zztop May 28 '20 at 15:17
I am getting a sql syntax error. with WHERE FIND_IN_SET(:'$searchterm',artist)...should it be something different? Also tried it without the '' around $searchterm with same error – zztop May 28 '20 at 16:21
OK. There should not be a : The syntax should be FIND_IN_SET('$needle',haystack) – zztop May 28 '20 at 16:34
@zztop: yes. The syntax I gave you makes use of a query parameter (see the first "important note"). – GMB May 28 '20 at 17:33

Query to return exact match for item in comma delimited string using MYSQL and PHP

1 Answers1