Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

For Anand:  Help with list and query

Posted on 2003-11-15
6
Medium Priority
?
266 Views
Last Modified: 2013-12-24
Thanks to Anand from my last question, I am hoping he or someone else can help me with this to finish it off.

This refers back to this question:
http://experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_20797355.html

So now I just want to know when i click on the category(http://www.jhasim.net/example_categories.jpg)
how can i sort through the single records and lists to only show the correct ID number, for instance in the the SQL image, ID 17.(http://www.jhasim.net/example_showcategories.jpg)

So basically, all i need to know now is how can i show all the programs that belong in a particular category when its ID is in a list.


Here is the code below that works fine when i was just showing 1 ID in the category field and not having a list:


<cfquery name="getPrograms" datasource="#DataTables#">
SELECT [Zone].ZoneID, [Zone].ZoneName, CMEPrograms.CMEProgramID, CMEPrograms.CMEProgramName, CMEPrograms.CMEProgramTagline, CMEPrograms.CMEProgramDate, CMEPrograms.CMEProgramCategory, CMEPrograms.CMEProgramVolume, CMEPrograms.CMEProgramNumber, year(CMEPrograms.CMEProgramDate) AS DateYear, month(CMEPrograms.CMEProgramDate) AS DateMonth
FROM CMEPrograms INNER JOIN [Zone] ON CMEPrograms.CMEProgramCategory = [Zone].ZoneID
WHERE CMEPrograms.CMEProgramCategory = #URL.ID#
Order By DateYear DESC, DateMonth DESC
</cfquery>

<TABLE width="100%" border="0" cellpadding="0" cellspacing="0">
      <TR>
        <TD class="TDtitle">&nbsp;ShowPrograms</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>
    <TD colspan="4" class="issueCategoryText"><CFOUTPUT>#getPrograms.ZoneName#</CFOUTPUT></TD>
  </TR>
  <TR>
    <TD colspan="4">
     <table width="100%">
     <cfoutput query="getPrograms" group="DateYear">
      <tr>
       <td class="TDTitle" colspan="2">&nbsp;#DateYear# Programs</td>
      </tr>
      <cfoutput>
      <tr>
      <td class="issueHeaderText" colspan="2"><a href="template.cfm?TEMPLATE=include_program.cfm&ID=#CMEProgramID#&PageName=#CMEProgramName#">#CMEProgramName#</a></td>
      </tr>
      <tr>
      <td class="secondary" colspan="2"><STRONG>#CMEProgramTagline#</STRONG></td>
      </tr>
      <tr>
      <td class="normaltextreg"><STRONG>Program Date:</STRONG> #Dateformat(CMEProgramDate, 'MMMM YYYY')#<cfif CMEProgramVolume GT 0 AND CMEProgramNumber GT 0> - <STRONG>Program Info:</STRONG> Volume #CMEProgramVolume#, (#CMEProgramNumber#)</cfif></td>
      <td class="normaltextreg"></td>
      </tr>
      <tr><td colspan="2">&nbsp;</td></tr>
      </cfoutput>
      </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
  • 3
  • 2
6 Comments
 
LVL 9

Expert Comment

by:shooksm
ID: 9756453
Can you change the design of the database?  Having a single column defining your category relationships is not normalized.  You should have a seperate table that acts as a bridge table or many to many type of relationship.  It should containt the primary key for your Zone table and the primary key for your CMEProgramCategory table.  This would allow you do simply join the two tables based on that bridge table.

Option 2.  A real simple way to implement lists in a SQL column is to make sure you prefix and suffix the field with the delimeter.  IE, instead of

9,1,3

you would have

,9,1,3,

At that point, you can use the following in your where statement:

WHERE CMEPrograms.CMEProgramCategory LIKE('%#URL.ID#%')

Although this will work, because of the wildcards being on both sides of the string, if an index exists, it will be ignored.

Again, your best bet is to get the database design fixed.  Less problems down the rode the next time you have to add functionality.
0
 
LVL 9

Expert Comment

by:shooksm
ID: 9756457
Oops, here is a correction to the where statement as I forgot the delimeters:

WHERE CMEPrograms.CMEProgramCategory LIKE('%,#URL.ID#,%')
0
 
LVL 17

Expert Comment

by:anandkp
ID: 9757819
hmmm using :
WHERE CMEPrograms.CMEProgramCategory LIKE('%,#URL.ID#,%')

wld avoid the entries with just "17" as the value

this shld do the job - use CFQueryparam for added advantages !

<cfquery name="getPrograms" datasource="#DataTables#">
SELECT [Zone].ZoneID, [Zone].ZoneName, CMEPrograms.CMEProgramID, CMEPrograms.CMEProgramName, CMEPrograms.CMEProgramTagline, CMEPrograms.CMEProgramDate, CMEPrograms.CMEProgramCategory, CMEPrograms.CMEProgramVolume, CMEPrograms.CMEProgramNumber, year(CMEPrograms.CMEProgramDate) AS DateYear, month(CMEPrograms.CMEProgramDate) AS DateMonth
FROM CMEPrograms INNER JOIN [Zone] ON CMEPrograms.CMEProgramCategory = [Zone].ZoneID
WHERE CMEPrograms.CMEProgramCategory LIKE <cfqueryparam CFSQLTYPE="CF_SQL_VARCHAR" VALUE="%#URL.ID#%">
Order By DateYear DESC, DateMonth DESC
</cfquery>

HTH

let me know

K'Rgds
Anand
0
Looking for a new Web Host?

Lunarpages' assortment of hosting products and solutions ensure a perfect fit for anyone looking to get their vision or products to market. Our award winning customer support and 30-day money back guarantee show the pride we take in being the industry's premier MSP.

 
LVL 9

Expert Comment

by:shooksm
ID: 9761268
Negative, not if you put the delimeters around single values too.  The problem with yours is that 17 is also in 117, 1700 or any other possible number with 17 in it.  That is why a delimeter is needed at the begining and ending of each value.  But as I said earlier, this is just a bandaid on a bigger problem of a bad database design.
0
 

Author Comment

by:jeffmace
ID: 9761370
I addressed the bad database design.  As stated in my previous question I altered it to no point to another table to look for the categories.  But what I was still looking to do was see if it is possilbe to do a query based on lists in a cell.  Thats all.
0
 
LVL 17

Accepted Solution

by:
anandkp earned 2000 total points
ID: 9761517
depending on what is the maximum no of categories u have - lets say 9999

u can modify teh "where" condition as :

WHERE CMEPrograms.CMEProgramCategory LIKE (<CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="%#URL.ID#%">)
(
<CFLOOP INDEX="i" FROM="0" TO="99">
      And Not like (<CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="%#i##URL.ID#%">)
      And Not like (<CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="%#URL.ID##i#%">)
</CFLOOP>
And Not like (<CFQUERYPARAM CFSQLTYPE="CF_SQL_VARCHAR" VALUE="%#URL.ID#00%">)
)

& it wld only get u the ones with 17 in it - avoiding all the other possible combinations

HTH - run the query to chk if it solves the requirement ... it shld :)

K'Rgds
Anand
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

636 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