Solved

Help with Lists!!!! Please help!

Posted on 2003-11-13
9
299 Views
Last Modified: 2013-12-24
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>
0
Comment
Question by:jeffmace
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 19

Expert Comment

by:cheekycj
ID: 9743052
>> 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
 

Expert Comment

by:emadsenus
ID: 9743528
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
 
LVL 17

Accepted Solution

by:
anandkp earned 500 total points
ID: 9745407
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:jeffmace
ID: 9748634
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
 

Author Comment

by:jeffmace
ID: 9748947
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
 

Author Comment

by:jeffmace
ID: 9749462
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
 
LVL 17

Expert Comment

by:anandkp
ID: 9752862
Do u still need more help on the other question u posted ... or everythings fine ?
0
 

Author Comment

by:jeffmace
ID: 9753833
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
 

Author Comment

by:jeffmace
ID: 9753842
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Suggested Courses

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question