[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 163
  • Last Modified:

SQL Coldfusion question

Well, I met another problem I cant see a solution to.

In my database a column contains a list of IDs as a string which is generated by checkboxes. I need a right solution to query the database.

Say, one record can contain just number 1 and another 1,3,4,9.
In my search form I also use checkboxes and got a list.
Say a search string is 1,4,9

I need a query which will look at the list from search form, compares value with the recorded string and if it matches the record is queried.
Say in my case I must get both records, because first one contains "1" and second one contains "1", "4" and "9".


0
Wasistdas
Asked:
Wasistdas
2 Solutions
 
Tacobell777Commented:
If you want to query strings in the database you are going to have to do some very complicated and resource hoggin stuff - what you need to do is add another table and insert each value from the string into that table and link it to the record where the string was stored with.

Trust me, its the worst thing you can do store a list of values in a database and then search through it.
0
 
black0psCommented:
That might be pretty difficult to query it in that way. I don't know a ton about MSSQL, but I don't think you will be able to loop through lists as strings.

You could try something like this:

<cfquery>
Select * From MyColumn
Where
<cfloop from="1" to="#ListLen(Form.Search)#" index="i">
<cfif i NEQ 1>OR </cfif>
MyColumn Like '#ListGetAt(Form.Search,i)#'
</cfloop>
</cfquery>

Instead of looping through the values in the column, you loop through the numbers in form field. A potential problem could be in you have a row with the values 1,3,10,101. If you searched on 1, you could pull up 3 different records.

You might also want to consider creating another table in the database specifically for the search. Perhaps someone else might have some more input on this topic.

-- Ian
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now