MySQL is looking for a number in the VARCHAR field

Question

I have a field in my database that contains values ​​separated by commas, these values ​​are numbers, and I'm trying to search and count the number of times the number appears in this column throughout the column,

$sql = "SELECT sector_id_csv, COUNT(sector_id_csv) as count FROM cv WHERE sector_id_csv LIKE '$sectorId'"; 

This seems slow and returns no results, and I know that the sector_id that exists exists in the table.

Answer 1

Basically, this should work fine if you use % wildcards:

 WHERE sector_id_csv LIKE '%$sectorId%'"; 

what usually causes problems in this scenario is that a search of 50 will also find 501 502 503 , etc.

If you can rely on your comma-separated list to have a capital comma behind each entry, it would be more reliable to search

50,

to catch only that value.

Answer 2

 WHERE CONCAT(',', sector_id_csv, ',') LIKE '%,$sectorId,%' 

or

 WHERE FIND_IN_SET('$sectorId', sector_id_csv); 

This ensures that your query will only return rows with the sector identifier in the specified field. Provided that sector identifiers in this field are separated by a comma.

Any query using LIKE or FIND_IN_SET will be slow because it cannot use indexes. Please consider placing all sector identifiers in a separate table.

Also, for security reasons, remember that $ sectorId is a number, dropping it like this:

 $sectorId = (int)$sectorId; 

before using it in the request.

Answer 3

You do not need to insert the value using the % pattern for LIKE to work?

 $sql = "SELECT sector_id_csv, COUNT(sector_id_csv) as count FROM cv WHERE sector_id_csv LIKE '%".$sectorId."%'"; 

At least what I understand by reading this article, your use of wildcards will depend on your desired condition.

Answer 4

... but if your stce was normalized, you would not have to jump over these hoops - and it did a lot faster.

FROM.

Answer 5

In fact, he can have a number at the beginning or at the end. Therefore you need to do

 WHERE sector_id_csv='$sectorId' OR sector_id_csv LIKE '%,$sectorId' OR sector_id_csv LIKE '$sectorId,%' OR sector_id_csv LIKE '%,$sectorId,%' 

Answer 6

 SELECT count(*) from TABLENAME where FIND_IN_SET('VALUE',FILDNAME)>0; 

Others may use instr, regexp .... It is recommended that you index FILDNAME.