Using CFQUERY to retrieve records sequentially 2

[ahksar]

I have a search form, where the user can select fields using checkboxes. There are 3 broad categories, (topic, age, media)and under these categories, are sub categories, which can be selected using checkboxes. A user can select checkboxes in any category, or none, or a single category.
How can I do a sequential search, for instance, if he selects topics and age, then get all records for the tyopics and within those topics, the age groups specified. Or topic and media, or topic and age and media? Can I use a CFLOOP to loop through the records?
Any help will be great, I dont even know how to start writing the CFML and SQL for this.
My query below doesnt work.
<cfquery name=&quot;retrieve&quot; datasource=&quot;#MyDsn#&quot; debug>

SELECT * FROM tblTools WHERE TopicIDFK ='#listgetat(SelectedTopic,SelectedTopicIndex,&quot;,&quot#'
AND AgeIDFK ='#listgetat(SelectedAge,SelectedAgeIndex,&quot;,&quot#'
AND MediaIDFK='#listgetat(SelectedMedia,SelectedMediaIndex,&quot;,&quot#'

</cfquery>

Thanks
ahksar

 

[GunJack]

Hey ahksar,

If you set your checkboxes to all have the same name in each category as I suspect you did, I think this might work for you:

On the form page:

<checkbox name=&quot;selectedTopic&quot; value=&quot;Topic1&quot;>
<checkbox name=&quot;selectedTopic&quot; value=&quot;Topic2&quot;>
...
<checkbox name=&quot;selectedMedia&quot; value=&quot;Media1&quot;>


On the action page:

SELECT * FROM tblTools WHERE 1=1
<cfif isdefined(&quot;form.selectedTopic&quot>
and TopicIDFK in ('#replace(form.selectedTopic,&quot;,&quot;,&quot;','&quot;,&quot;all&quot#')
</cfif>
<cfif isdefined(&quot;form.selectedAge&quot>
AND AgeIDFK in ('#replace(form.selectedAge,&quot;,&quot;,&quot;','&quot;,&quot;all&quot#')
</cfif>
<cfif isdefined(&quot;form.selectedMedia&quot>
AND MediaIDFK in ('#replace(form.selectedMedia,&quot;,&quot;,&quot;','&quot;,&quot;all&quot#')
</cfif>

Let me know if you have any trouble with it and just make sure you don't have any commas in the checkbox values.

GJ

 

[ahksar]

Thanks a lot GunJack. It worked perfectly. I have a question , I just realised that there is another field to be included, ie, apart from topic, age, media, I have to include Type. Are there any changes to the code(to the replace function) you wrote above, or can I just add another<cfif></cfif>statement similar to the ones above.

Thanks again for your help.
ahksar

 

[GunJack]

Hey Ahksar,

You can just copy and rename the fields. The replace function is simply there to replace the commas in the field list with ',' so that the &quot;in ()&quot; statement has single quotes around all of the values. That's why I said you need to make sure the values don't contain any commas as the value will get split. If you need to have commas in the values, there's a more lengthy way to do this that will work with commas.

Good luck!
GJ

 

[ahksar]

The code above works fine most of the time. But if I broaden my search parameters, and select all the checkboxes, nothing is found. And in some cases, if more than one checkbox is selected, for instance, items that have multiple
categories are not showing up in the search for either or both.
Do I have to change the way the DB is designed?Please help.
ahksar

 

[ahksar]

Gunjack, the solution that you helped me with works fine if there is just a single value in any of the AGEIDFK, TOPICIDFK, etc fields. Then all the records are retireved. But if there is more than one record, for instance, there are 2 age groups, or 3 types of media, and they are in a comma delimited list in a row of tblTools, then they dont show up because of the commas in the values.. How can I write the query to get these records to show up too...
any hints will be greatly appreciated.

ahksar

 

[GunJack]

Hey Ahksar,

Do you mean that the field &quot;TopicDFK&quot; contains several values in the same record? If so, I would break the records up into a record for each value so that TopicDFK contains only one value. If this won't work for other reasons, please post an excerpt of your database so I can get a little insight into what you're doing.

GJ

 

[ahksar]

ToolsID MediaIDFK AgeIDFK Name
1.AEABB542DA5B6813 AC7601A0-D021 AC7601AD-D021 ....

2.8BE3674DF6E93FE4 AC7601A0-D021, AC7601A5-D021 ....
AC7601A8-D021

3.A8815D6CC0D65F99 AC7601B4-D021, AC7601A5-D021, ....
AC7601B8-D021 AC7601A9-D021,
AC7601AD-D021


The IDs are hexadecimals generated using createUUID(). I've put in just parts of it. The ToolsID column is the primary key, it is unique.MediaIDFK, AgeIDFK are foriegn keys. Each tool can be categorised by different media(in some cases, more than one, like web, newspapers, etc) and can be used by different age groups(10-12, 12-14, 15+, etc). Thats why there is more than one value. Its hard to put only one value in each field, cause that will make the DB too large. There are 2 other fields which i havent put in, TopicIDFK, and TypeIDFK. The user can select any number of topics, or age levels, or one topic, and 2 ages, and the corresponding tools (for that topic and the 2 age levels )should be retrieved. Ifyou want, I can send you the url.

Thanks
ahksar

 

[GunJack]

Hey Ahksar,

I really think you need to re-structure your database tables to do what you want. Is this an option? Otherwise, the coding gets a little trickier.

GJ

 

[ahksar]

Gunjack, it would be great if the values could be retrieved without having to restructure the entire DB, as it is already in use.
Can I use a cfloop statement in the query to retrieve each individual record?


thanks
ahksar

 

[GunJack]

Hey Ahksar,

I think you'll have to structure each field something like this:

where 1 = 1
<cfif isdefined(&quot;form.selectedTopic&quot>
<cfloop list=#form.selectedTopic# index=&quot;x&quot;>
and ( TopicIDFK like '#x#'
or TopicIDFK like '%,#x#'
or TopicIDFK like '#x#,%'
or TopidIDFK like '%,#x#,%'
)
</cfloop>
</cfif>

There might be an easier way but this is how I would do it to check for the value being at the start, middle, end, or a single entry in that field.

Let me know if you have any trouble with it as I didn't test it.
GJ

 

[ahksar]

Many thanks Gunjack. It worked fine.
I couldnt figure out the &quot;WHERE 1=1&quot; part.

Thanks again.
ahksar

 

[GunJack]

Glad to help

Take care,
GJ