Skip to main content
C
CalTek
Participant
December 8, 2008
Answered

CFQUERY a delimited text field

  • December 8, 2008
  • 1 reply
  • 367 views
I have a table (Banners) with one field (ShowOnPages) that has delimited text values ( ie: 1,3,4,7,6 ) representing the ID numbers of pages I want the photos to show up on.

What I want to happen is when I load a page up I want to query the table (Banners) to see which banners are to show up on that page.

Is this possible to do in one query or will it require multiple queries?

Any assistance would be GREATLY appreciated ( as ASAP if possible ).

Thanks in advance.

I just do not have any clue how to code it or if it is possible.



This topic has been closed for replies.
Correct answer -__cfSearching__-
CalTek wrote:
> with one field (ShowOnPages) that has delimited text values ( ie: 1,3,4,7,6 )

You have already have discovered one of the reasons against storing delimited data: it is tough to query. You would be much better off normalizing the data. Change your table to store a single record for each pageID + bannerID combination. Then a simple query is all you will need to return the banners that should show up on the current page ID.

SELECT BannerID, OtherColumns...
FROM Banners
WHERE PageID = <cfqueryparam value="#thePageID#" cfsqltype="(whatever type)">

1 reply

-
-__cfSearching__-Correct answer
Inspiring
December 8, 2008
CalTek wrote:
> with one field (ShowOnPages) that has delimited text values ( ie: 1,3,4,7,6 )

You have already have discovered one of the reasons against storing delimited data: it is tough to query. You would be much better off normalizing the data. Change your table to store a single record for each pageID + bannerID combination. Then a simple query is all you will need to return the banners that should show up on the current page ID.

SELECT BannerID, OtherColumns...
FROM Banners
WHERE PageID = <cfqueryparam value="#thePageID#" cfsqltype="(whatever type)">
C
CalTekAuthor
Participant
December 8, 2008
Well that makes more sense...I like that approach better. That way I will be able to provide better stats for which pages got the most views/clicks.

I guess too many late nights working on this has fried my brain!

Thanks for the assistance.
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices