jeffmace
asked on
For Anand: Help with list and query
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:
https://www.experts-exchange.com/questions/20797355/Help-with-Lists-Please-help.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.CMEProgramTagl ine, CMEPrograms.CMEProgramDate , CMEPrograms.CMEProgramCate gory, CMEPrograms.CMEProgramVolu me, CMEPrograms.CMEProgramNumb er, year(CMEPrograms.CMEProgra mDate) AS DateYear, month(CMEPrograms.CMEProgr amDate) AS DateMonth
FROM CMEPrograms INNER JOIN [Zone] ON CMEPrograms.CMEProgramCate gory = [Zone].ZoneID
WHERE CMEPrograms.CMEProgramCate gory = #URL.ID#
Order By DateYear DESC, DateMonth DESC
</cfquery>
<TABLE width="100%" border="0" cellpadding="0" cellspacing="0">
<TR>
<TD class="TDtitle"> Show 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>
<TD colspan="4" class="issueCategoryText"> <CFOUTPUT> #getProgra ms.ZoneNam e#</CFOUTP UT></TD>
</TR>
<TR>
<TD colspan="4">
<table width="100%">
<cfoutput query="getPrograms" group="DateYear">
<tr>
<td class="TDTitle" colspan="2"> #DateYea r# Programs</td>
</tr>
<cfoutput>
<tr>
<td class="issueHeaderText" colspan="2"><a href="template.cfm?TEMPLAT E=include_ program.cf m&ID=#CMEP rogramID#& PageName=# CMEProgram Name#">#CM EProgramNa me#</a></t d>
</tr>
<tr>
<td class="secondary" colspan="2"><STRONG>#CMEPr ogramTagli ne#</STRON G></td>
</tr>
<tr>
<td class="normaltextreg"><STR ONG>Progra m 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"> </td></t r>
</cfoutput>
</cfoutput>
</table>
</TD>
</TR>
</TABLE>
This refers back to this question:
https://www.experts-exchange.com/questions/20797355/Help-with-Lists-Please-help.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
FROM CMEPrograms INNER JOIN [Zone] ON CMEPrograms.CMEProgramCate
WHERE CMEPrograms.CMEProgramCate
Order By DateYear DESC, DateMonth DESC
</cfquery>
<TABLE width="100%" border="0" cellpadding="0" cellspacing="0">
<TR>
<TD class="TDtitle"> Show
</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>
<TD colspan="4" class="issueCategoryText">
</TR>
<TR>
<TD colspan="4">
<table width="100%">
<cfoutput query="getPrograms" group="DateYear">
<tr>
<td class="TDTitle" colspan="2"> #DateYea
</tr>
<cfoutput>
<tr>
<td class="issueHeaderText" colspan="2"><a href="template.cfm?TEMPLAT
</tr>
<tr>
<td class="secondary" colspan="2"><STRONG>#CMEPr
</tr>
<tr>
<td class="normaltextreg"><STR
<td class="normaltextreg"></td
</tr>
<tr><td colspan="2"> </td></t
</cfoutput>
</cfoutput>
</table>
</TD>
</TR>
</TABLE>
Oops, here is a correction to the where statement as I forgot the delimeters:
WHERE CMEPrograms.CMEProgramCate gory LIKE('%,#URL.ID#,%')
WHERE CMEPrograms.CMEProgramCate
hmmm using :
WHERE CMEPrograms.CMEProgramCate gory 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.CMEProgramTagl ine, CMEPrograms.CMEProgramDate , CMEPrograms.CMEProgramCate gory, CMEPrograms.CMEProgramVolu me, CMEPrograms.CMEProgramNumb er, year(CMEPrograms.CMEProgra mDate) AS DateYear, month(CMEPrograms.CMEProgr amDate) AS DateMonth
FROM CMEPrograms INNER JOIN [Zone] ON CMEPrograms.CMEProgramCate gory = [Zone].ZoneID
WHERE CMEPrograms.CMEProgramCate gory LIKE <cfqueryparam CFSQLTYPE="CF_SQL_VARCHAR" VALUE="%#URL.ID#%">
Order By DateYear DESC, DateMonth DESC
</cfquery>
HTH
let me know
K'Rgds
Anand
WHERE CMEPrograms.CMEProgramCate
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
FROM CMEPrograms INNER JOIN [Zone] ON CMEPrograms.CMEProgramCate
WHERE CMEPrograms.CMEProgramCate
Order By DateYear DESC, DateMonth DESC
</cfquery>
HTH
let me know
K'Rgds
Anand
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.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.CMEProgramCate
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.