2 Replies Latest reply on Dec 8, 2008 8:57 AM by CalTek

    CFQUERY a delimited text field

    CalTek
      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.



        • 1. Re: CFQUERY a delimited text field
          -==cfSearching==- Level 4
          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)">
          • 2. Re: CFQUERY a delimited text field
            CalTek Level 1
            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.