Solved

SQL Coldfusion question

Posted on 2004-10-17
5
158 Views
Last Modified: 2013-12-24
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
Comment
Question by:Wasistdas
5 Comments
 
LVL 17

Accepted Solution

by:
Tacobell777 earned 125 total points
ID: 12334409
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
 
LVL 7

Assisted Solution

by:black0ps
black0ps earned 125 total points
ID: 12334416
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Using Route 53, Record Sets & Health Checks 2 Node Exchange 2016 environment 2 105
WEB Service vs ??? 7 122
Intranet building Software 8 124
Problem to echo 6 109
Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
The purpose of this video is to demonstrate how to properly insert a Vimeo Video into a WordPress site or Blog. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp…
The purpose of this video is to demonstrate how to manually back up a WordPress Database. This will be demonstrated using a Windows 8 PC. The Host used will be IPage.com Log into your Hosting account. IPage will be used for demonstration : Locat…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question