Solved

Help with Lists!!!! Please help!

Posted on 2003-11-13
9
289 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
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
 

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now