How to search for id separated by "," correctly?

Question

I have a table called attendance, within it I have a field called forwarding, in the forwarding field I save the ID of the "routing types" I have registered in the table cad_forwarding separated by "," . I need to count the amount of referral made for a certain period, and I need to know the amount of each one. So I looped using the for command to be searched for forwarding in this way:

for($n=1;$n<= $row[0];$n++){

$quant_enc = "SELECT encaminhamento FROM atendimento WHERE encaminhamento LIKE '%$n%' AND data_atendimento BETWEEN '$inicio' AND '$fim'";


    $ex_enc = mysqli_query($conn,$quant_enc);
    $row_quant = mysqli_num_rows($ex_enc);
    $quant[$n]=$row_quant;`

The problem that I am having is that when there is for example : Forwarding with id 12, and another with id 112, with LIKE %12% , counts twice because the number 112 also considers 12. Is this the best way to get this information or are there more practical ways? Or how to get only the code I’m actually searching for ?

Answer 1

To find a comma-separated value inside a string there is the FIND_IN_SET.

Applied to your case:

SELECT encaminhamento FROM atendimento WHERE FIND_IN_SET( '12', encaminhamento );

Only the performance is not good, just like the LIKE used with % on the left, it doesn’t take advantage of indexes. Maybe it’s better to refactor in some way. If it’s for low data volume, it might not be a problem.

Handbook:

http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_find-in-set