Search code examples
mysqlcoldfusioncoldfusion-10

How to search for a value in a DB field containing a list of values?

I hope I'm asking this question correctly. I'm using ColdFusion 10 and have a mySQL table named companies. In the table there is a field named stateList. It contains a comma delimited list of state_IDs where the company has stores.

I have a search form for the user to select a state to find the companies in that state. I need to pass that form.state_ID to the search query to see if companies.stateList contains form.state_ID.

The only code I tried that returned any results, and they were not all correct results, was

<cfquery name="searchCompanies" datasource="#businessDSN#">
  SELECT company_id, company_name, stateList
  FROM companies
  WHERE stateList LIKE "%#form.state_ID#"
</cfquery>

If I searched for Arizona, state_ID 4, the above query returned any company that had 4, 14, 24, 34, and 44 in its stateList.

Is what I want to do possible? Thanks for any help provided.

Solution

  • ... there is a field named stateList. It contains a comma delimited list of state_IDs ...

    Oh God, why? If you can, change that to a proper relationship table immediately!

    But for the time being...

    <cfquery name="searchCompanies" datasource="#businessDSN#">
  SELECT company_id, company_name, stateList
  FROM companies
  WHERE CONCAT(',', stateList, ',') 
        LIKE
        CONCAT('%,', <cfqueryparam value="#form.state_ID#" cfsqltype="CF_SQL_VARCHAR">, ',%')
</cfquery>

    And, please use <cfqueryparam> instead of dropping unchecked user input into your SQL string.