Help with List Comparison, Output Logic

[existdissolve]

I have a fairly complex problem I am trying to solve with no luck.

I have a form for creating sponsors. On this form, the user is given a series of checkboxes for selecting which page(s) they would like the sponsor's advertisement to display on. This series of checkboxes is being dynamically generated from a list of "page names" which are listed in another table.

On submission, the selected check boxes are written to a list which is stored under the sponsor's table in my database, with "true" or "false" for the given pagename in the list: For example, let's say I have the checkboxes "index" "localnews" and "sports." Then, let's say I choose the first two. On submit, I loop over the dynamically named checkboxes, append them to a list, which results (in this example) with a list of "true,true,false."

What I need to do is this: On the index page, I need to display all of the sponsors whose lists contain a "true" value in the list position relative to the pagename. So, if "localnews" is an order of "1" in the list, I need to check the 1st item of each list in each sponsor's record to determine whether or not the first list item is "true."

So, let's say I have two sponsors, Jack and John. Their respective lists are as follows:

(Jack) true,true,false
(John) false,true,true

Now, let's say I am on the "localnews" page where the value of local news in the boolean list is the 1st position (e.g., in "true,true,false", the first "true" equals localnews). Somehow, I need to figure out how to create an output that will check the 1st position of Jack and John's lists, determining what the boolean values in those positions are, and output the results (e.g., an image for Jack, and nothing for John). Then, for each subsequent page, I need to move down the list depending upon the list position boolean equivalent of the pagename (e.g., "sports" =3, "obituaries"=4, and so on).

Any ideas?

[The_ScareCrow]

I assume you will have a query to return the sponsor's page list ?
Let's assume that the column that contains the list is call "sponsor_list", then

<!--- the page we need to check against --->
<cfset onPage = 1>
<cfoutput query="qry_sponsor_page">
<cfif ListGetAt(qry_sponsor_page.sponsor_list, onPage) Eq "true">
The value is true display the sponsor...
</cfif>
</cfoutput>

Ken

[efecto747]

Are you able to make changes to the database where this is being stored? The trouble with saving your data in lists is that its slow to access, complex (as you're discovering) and doesn't give a lot of flexibility when you want to make changes (ie. add or remove a page from the list).

If you can change the database, I'd suggest creating a separate table for storing the sponsor's selections so you can then get all your information with just one or two simple queries. Presumably the Sponsor and Page tables have a unique ID field? If they don't then you might want to consider creating them. Once you have two tables with unique ID's it becomes very easy to create a third table to store related data - see below:

Sponsor Table:
- SponsorID
- SposorName
etc...

Page Table:
- PageID
- PageName
etc...

Page/Sponsor Table (new table):
- SponsorID
- PageID
- DisplayFlag (boolean)

With tables similar to above, you can select sponsors for any page with a simple query:

SELECT SponsorID
FROM PageSponsorTable
WHERE DisplayFlag = true
AND PageID = #url.pageID#

You can expand on this and include joins to the other tables to include the page and sponsor names and other details, the query above is a good starting point.

Good luck.

[Dan_Bracuk]

To paraphrase efecto, normalize your database and you won't have problems like this.