asked on

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"> 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"> </TD>
</TR>
<TR valign="top">
<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# <cfif getZones.totalRecds gt 1>Programs<cfelse>Program</cfif>)</SPAN></td>
</tr>

<cfset locStartRow=locStartRow + 1>
</cfoutput>
</table>
</td>

<td>
<table>

</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# <cfif getZones.totalRecds gt 1>Programs<cfelse>Program</cfif>)</SPAN></td>
</tr>
</cfoutput>
</table>
</td>
</TR>
</TABLE>

cheekycj

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

emadsenus

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.

ASKER CERTIFIED SOLUTION

anandkp

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jeffmace

ASKER

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.

jeffmace

ASKER

That worked really well, thank you. I posted another question to help finish this off. I hope you can help

https://www.experts-exchange.com/questions/20798256/Help-with-List-and-Query-Kinda-Part-2.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.

jeffmace

ASKER

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.

anandkp

Do u still need more help on the other question u posted ... or everythings fine ?

jeffmace

ASKER

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

jeffmace

ASKER

I reposted the question:

https://www.experts-exchange.com/questions/20798976/For-Anand-Help-with-list-and-query.html