Help with Lists!!!! Please help!

I have a section in my backend that allows me to choose multiple categories/zones for my content.  I have a page on my frontend that will display the category names and how many content pieces are in each zone.  I can get it to display without any problems when the content goes into only 1 category, but I do not know how to code it correctly when there are multiple categories in the record(multiple categories puts the IDs into a list in the database).

Please look at my images below to further help you along:

This works fine when i have just single category.
Here is what my SQL database looks like:
http://www.jhasim.net/sql_example1.jpg

This is what the output would look like:
http://www.jhasim.net/example1.jpg


But this is what my sql database looks like when i have multiple categories:
http://www.jhasim.net/sql_example2.jpg

So then my output should read (2) Adolecent Medine articles and (13) Cardiology articles.

So basically my question is.  How do i display this page that will have many many categories and articles to count.  Many of them will have multiple categories.  Is it possible to be able to group all the content and count all the articles with lists in different records.



Here is the code of my current page that displays single category records fine.



<cfquery name="getZones" datasource="#DataTables#">
SELECT [Zone].ZoneID, [Zone].ZoneName, count(*) as totalRecds
FROM CMEPrograms INNER JOIN [Zone] ON CMEPrograms.CMEProgramCategory = [Zone].ZoneID
WHERE [Zone].ProgramZone = 1 AND CMEPrograms.CMEProgramActive = 1
Group by [Zone].ZoneID, [Zone].ZoneName
Order By [Zone].ZoneName ASC
</cfquery>

<cfset locTotalRecordCount=getZones.RecordCount>
<cfset locMaxRows=Ceiling(locTotalRecordCount / 2)>
<cfset locStartRow=1>

<TABLE width="100%" border="0" cellpadding="0" cellspacing="0">
      <TR>
        <TD class="TDtitle">&nbsp;Programs</TD>
      </TR>
      <TR>
        <TD>
<TABLE width="100%" border="0">
  <TR align="center">
    <TD colspan="4" class="TDdashed">Text goes here</TD>
  </TR>

  <TR>
    <TD colspan="4">&nbsp;</TD>
  </TR>
  <TR valign="top">
<td>
<table><!---1/2 of column 1 on left side of page--->
<cfoutput query="getZones" startrow="#locStartRow#" maxrows="#locMaxRows#">
<tr valign="top"><td class="title"><a href="template.cfm?TEMPLATE=include_viewzone.cfm&ID=#ZoneID#">#getZones.ZoneName#</a> -
    <SPAN class="secondary">(#getZones.totalRecds#&nbsp;<cfif getZones.totalRecds gt 1>Programs<cfelse>Program</cfif>)</SPAN></td>
</tr>
<!--- Increment the locStartRow var --->
<cfset locStartRow=locStartRow + 1>
</cfoutput>
</table>
</td>

<td>
<table>
<!---nondatabase information--->
</table>
</td>

<td>
<table>
<cfoutput query="getZones" startrow="#locStartRow#" maxrows="#locMaxRows#">
<tr valign="top"><td class="title"><a href="template.cfm?TEMPLATE=include_viewzone.cfm&ID=#ZoneID#">#getZones.ZoneName#</a> -
    <SPAN class="secondary">(#getZones.totalRecds#&nbsp;<cfif getZones.totalRecds gt 1>Programs<cfelse>Program</cfif>)</SPAN></td>
</tr>
</cfoutput>
</table>
</td>
  </TR>
</TABLE>
jeffmaceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cheekycjCommented:
>> multiple categories puts the IDs into a list in the database

Is there a reason for designing it this way?

Since you can have multiple values in one column of each record, Group By cannot be leveraged.

You have to retrieve each record, loop over it.  Increment counts for each value in the column.

CJ
0
emadsenusCommented:
You should consider a design that uses a one to many relationship.

Table A
-----------
A_ID (pk)

Table B
------------
B_ID (pk)

Table C
------------
A_ID
B_ID

Then you should be able to join the tables to each other and group the output.
0
anandkpCommented:
Hi Jeff,

Since the design is not the way it shld be - but u still need to use the same design & get the work done ... then use the follwoing logic & see if it gives u waht ur looking for

I have used simple queries [2 of them] - pls change these queries to suit ur DB & let me know
Ive commented the unwanted code ... & have put my code inside ... so just copy the entire things & run it [after changing teh queries]

hope it helps ...

<!--- <CFQUERY name="getZones" datasource="#DataTables#">
      SELECT [Zone].ZoneID, [Zone].ZoneName, count(*) as totalRecds
      FROM CMEPrograms INNER JOIN [Zone] ON CMEPrograms.CMEProgramCategory = [Zone].ZoneID
      WHERE [Zone].ProgramZone = 1 AND CMEPrograms.CMEProgramActive = 1
      Group by [Zone].ZoneID, [Zone].ZoneName
      Order By [Zone].ZoneName ASC
</CFQUERY> --->

<CFQUERY NAME="Qry_Cat" DATASOURCE="Dsn"><!--- Get All the Categories --->
    Select CMEProgramCategory from tablename      
</CFQUERY>
<CFQUERY NAME="Qry_Zone" DATASOURCE="Dsn"><!--- Get all the Diff ZoneIDs --->
    Select ZoneID,ZoneName from tablename      
</CFQUERY>

<CFSET CatList = ValueList(Qry_Cat.CMEProgramCategory)>
<CFSET ZoneList = ValueList(Qry_Zone.ZoneID)>
<CFSET ZoneName = ValueList(Qry_Zone.ZoneName)>

<!--- <CFSET locTotalRecordCount=getZones.RecordCount>
<CFSET locMaxRows=Ceiling(locTotalRecordCount / 2)>
<CFSET locStartRow=1> --->

<TABLE width="100%" border="0" cellpadding="0" cellspacing="0">
      <TR>
            <TD class="TDtitle">&nbsp;Programs</TD>
      </TR>
      <TR>
            <TD>
            <TABLE width="100%" border="0">
                  <TR align="center">
                        <TD colspan="4" class="TDdashed">Text goes here</TD>
                  </TR>
                  <TR>
                        <TD colspan="4">&nbsp;</TD>
                  </TR>
                  <TR valign="top">
                        <TD>
                        <TABLE>                              <!---1/2 of column 1 on left side of page--->
                              <!--- <CFOUTPUT query="getZones" startrow="#locStartRow#" maxrows="#locMaxRows#">
                                    <TR valign="top">
                                          <TD class="title"><A href="template.cfm?TEMPLATE=include_viewzone.cfm&ID=#ZoneID#">#getZones.ZoneName#</A>-
                                          <SPAN class="secondary">(#getZones.totalRecds#&nbsp;
                                          <CFIF getZones.totalRecds gt 1>
                                                Programs
                                          <CFELSE>
                                                Program
                                          </CFIF>
                                          )</SPAN></TD>
                                    </TR>                                    <!--- Increment the locStartRow var --->
                                    <CFSET locStartRow=locStartRow + 1>
                              </CFOUTPUT> --->
                              <CFLOOP INDEX="i" FROM="1" TO="#ListLen(ZoneList)#">
                                    <TR valign="top">
                                          <TD class="title">
                                          <A href="template.cfm?TEMPLATE=include_viewzone.cfm&ID=#ListGetAt(ZoneList,i)#">#ListGetAt(ZoneName,i)#</A>
                                          -
                                          <SPAN class="secondary">(#ListValueCount(CatList,ListGetAt(ZoneList,i))#&nbsp;
                                          <CFIF getZones.totalRecds gt 1>
                                                Programs
                                          <CFELSE>
                                                Program
                                          </CFIF>
                                          )</SPAN></TD>
                                    </TR>
                              </CFLOOP>
                        </TABLE></TD>
                        <TD>
                        <TABLE>                              <!---nondatabase information--->
                        </TABLE></TD>
                        <TD>
                        <TABLE>
                              <!--- <CFOUTPUT query="getZones" startrow="#locStartRow#" maxrows="#locMaxRows#">
                                    <TR valign="top">
                                          <TD class="title"><A href="template.cfm?TEMPLATE=include_viewzone.cfm&ID=#ZoneID#">#getZones.ZoneName#</A>-
                                          <SPAN class="secondary">(#getZones.totalRecds#&nbsp;
                                          <CFIF getZones.totalRecds gt 1>
                                                Programs
                                          <CFELSE>
                                                Program
                                          </CFIF>
                                          )</SPAN></TD>
                                    </TR>
                              </CFOUTPUT> --->
                        </TABLE></TD>
                  </TR>
            </TABLE>

let me know ...

K'Rgds
Anand
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

jeffmaceAuthor Commented:
I thank everyone for their input, but can someone then explain how they would really setup the multiple category option by selecting it from a select box.   Anandkp, i am going to try your solution now to see if it works for me, but I am really interested to see what some of you are thinking of.  I alread use alot of one-to-many relationships in this project.  For instant, this Program that i am trying to categorize can have many articles in the program, so I have a whole other table linking to this one to display those articles of this program.  So i do not understand how you would like me to setup another table to show the categories.  I am  still in production mode so changes can be made now which i would prefer to do rather than  later.  So if you have a better solution, i would greatly appreciate it.
0
jeffmaceAuthor Commented:
That worked really well, thank you.  I posted another question to help finish this off. I hope you can help

http://experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_20798256.html

I am still interested in seeing a better way to do this so that i am not running into problems down the road.  I would be greatful if you can show me, because now you got me worried that I will regret this type of design later.
0
jeffmaceAuthor Commented:
Don't worry about the other question.  I redid the whole section and created another Table called ProgramZones.  So now all the zones are stored seperately and I am counting and grouping everything from from that table.  It's also good because it opened up a new possilbity to have programs in different zones, but active at different times, so there may be a time that i want it to only be on one zone and not the others, and then i can easily activate all them.

Thanks for your suggestions, i think this is the much better way to go, all i had to do is sit there and think for a few minutes about how it would work.  It wasn't that hard at all.

If you do have any more insight into this, I am always open to reading about it.
0
anandkpCommented:
Do u still need more help on the other question u posted ... or everythings fine ?
0
jeffmaceAuthor Commented:
Well i would like to know how to do that, so if you have the time to show how that can be done, it would nice for me to add to my code snippet collection. I really appreciate that you care to show people how to do things and you have taught me alot.  I don't know if i can reopen the question so that you can get points for answer.  I will look into it
0
jeffmaceAuthor Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.