query MySQL by searching for a string where order doesn't matter

Question

I have a MySQL table that looks like the following:

    uid       title 
    ____     ________
    aaac      A, 73, B5 
    aaab      A, B6

"title" holds a string of values separated by commas. There is always at least two values in title. I need to query this Table and return the uid. I will be passing in a string via POST for title to check for. However, order of this string is not important. The string could look like:

    73, B5, A

and this would need to return aaaa

Any ideas how to setup the query?

Answer 1

First of all, storing multiple values in one field goes against the first rule in normalisation of a database. Things become a lot easier if you apply normalisation.

But given this situation, you could aim for a query with a where clause like this:

WHERE FIND_IN_SET(?, replace(title, ' ', ''))
AND   FIND_IN_SET(?, replace(title, ' ', ''))
AND   FIND_IN_SET(?, replace(title, ' ', ''))

You would create each line dynamically in PHP, for instance like this:

$items = explode(",", $_POST["search"]);
$conditions = implode(" AND ", 
     array_fill(0, count($items), "FIND_IN_SET(?, replace(title, ' ', ''))")
);
$sql = "SELECT ......... WHERE $conditions";

Then use a prepared statement and bind_params to bind the $items to the placeholders (?).

Normalised approach

It is better to create a new table with one record per individual piece: (uid, piece).

So instead of the following records in the main table (table1):

uid       title       other
____     ________     _______
aaac      A, 73, B5   x
aaab      A, B6       y

You would remove the title column:

uid       title       other
____     ________     _______
aaac      A, 73, B5   x
aaab      A, B6       y

And add these records in a new table table2

uid       piece      
____     ________    
aaac      A
aaac      73
aaac      B5
aaab      A
aaab      B6    

Then the SQL would look like:

FROM       table1 
INNER JOIN table2 ON table1.uid = table2.uid
WHERE      table2.piece in (?, ?, ?)
GROUP BY   table1.uid
HAVING     count(distinct table2.piece) = 3