Relating tables in an output

[jmoshier]

Hello all,
Hopefully I can explain this well enough to undersand what I am trying to do.
I have a page that displays all of our glossary terms in alphabetical order
grouped by the first letter. Above the list of terms I have a table with all 26
letters with links to an anchor that will take you to the selected letter.

What I would like to do is only have a link to the anchor if there is a
related anchor further on the page.
Here are the two tables that I am using:
Alphabet Table: alpha_id (primary key)field , letter field
Glossary Table: term_id (primary key)field, name field, meaning field,
first_character field

If I just use the one query that gets all the information from the glossary
table I can't seem to figure out a way to show letters that are not defined in
the first_character field. If I use the alphabet table to show all the letters
and just use the glossary table to set the links I get a link on all the
letters, letters that don't have an anchor to jump to obviously don't go
anywhere.
What I am looking to try to do with an if/else statement is, Put the link on
the letter if there is a corresponding letter in the glossary table if not just
show the letter in my specified color without a link.

I hope this all makes sense and thank you inadvance for your help.
jmoshier



<!---select all the glossary terms to be displayed on the page--->
<cfquery name="getGlossary" datasource="erinc_docs">
SELECT * FROM glossary ORDER BY first_character, name
</cfquery>

<!---get letters for links at top of page--->
<cfquery name="getLetters" datasource="erinc_docs">
SELECT * FROM alphabet ORDER BY letter
</cfquery>

Here is the code that dislays all the letters in a table:
<cfoutput query="getLetters">
<td width="11" bgcolor="##FFFFFF">
<div align="center"><a href="###letter#">#letter#</a></div>
</td>
</cfoutput>

Here is the code to display the terms:
<table>
<cfoutput query="getGlossary" group="first_character">
<tr bgcolor="##f7f7f7">
<td valign="top" bgcolor="##ffffff"><div
align="center"><strong>#first_character#</strong><a
name="#first_character#"></a></div></td>
<td bgcolor="##ffffff">
<table width="100%" cellpadding="0" cellspacing="2"
border="0">
<cfoutput>
<tr>
<td
bgcolor="##ffffff"><strong>#getGlossary.name#</strong> -
#getGlossary.meaning#</td>
</tr>
<tr>
<td bgcolor="##ffffff"><img
src="../_common/images/spacer_grey.gif" width="100%" height="1"></td>
</tr>
</cfoutput>
<tr>
<td align="right" bgcolor="##ffffff"><a
href="##top">Top of Page</a></td>
</tr>
</table></td>
</tr>
</cfoutput>
</table>

Try the something like the following...

<!-- *************************** -->
<!-- Insert after the GetLetters Query -->
<!-- *************************** -->

<!-- create index tracking structure to determine if index item has
information -->
<cfset alphabetArray = structnew()>

<!-- Mark all items having no link -->
<cfloop query = "GetLetters">
<cfset alphabetArray[letter] = "-">
</cfloop>

<!-- Determine index items with links -->
<cfquery name="getIndex" dbtype="query">
SELECT DISTINCT first_character FROM GetGlossary ORDER BY first_character
</cfquery>
<cfoutput query="getIndex">
<cfset alphabetArray [#ucase(first_character)#] = first_character>
</cfoutput>

<!-- output the index, needs to be modified to get output in desired
format -->
<cfoutput>
<cfloop query = "GetLetters">
<cfif #alphabetArray[letter]# IS NOT "-">
&nbsp;<a href="###alphabetArray[letter]#">#letter#</a>&nbsp;
<cfelse>
&nbsp;#letter#&nbsp;
</cfif>
</cfloop>
</cfoutput>

Brian

"jmoshier" <webforumsuser@macromedia.com> wrote in message
news:d0q4vl$koh$1@forums.macromedia.com...

> Hello all,
> Hopefully I can explain this well enough to undersand what I am trying to
> do.
> I have a page that displays all of our glossary terms in alphabetical
> order
> grouped by the first letter. Above the list of terms I have a table with
> all 26
> letters with links to an anchor that will take you to the selected letter.
>
> What I would like to do is only have a link to the anchor if there is a
> related anchor further on the page.
> Here are the two tables that I am using:
> Alphabet Table: alpha_id (primary key)field , letter field
> Glossary Table: term_id (primary key)field, name field, meaning field,
> first_character field
>
> If I just use the one query that gets all the information from the
> glossary
> table I can't seem to figure out a way to show letters that are not
> defined in
> the first_character field. If I use the alphabet table to show all the
> letters
> and just use the glossary table to set the links I get a link on all the
> letters, letters that don't have an anchor to jump to obviously don't go
> anywhere.
> What I am looking to try to do with an if/else statement is, Put the link
> on
> the letter if there is a corresponding letter in the glossary table if not
> just
> show the letter in my specified color without a link.
>
> I hope this all makes sense and thank you inadvance for your help.
> jmoshier
>
>
>
> <!---select all the glossary terms to be displayed on the page--->
> <cfquery name="getGlossary" datasource="erinc_docs">
> SELECT * FROM glossary ORDER BY first_character, name
> </cfquery>
>
> <!---get letters for links at top of page--->
> <cfquery name="getLetters" datasource="erinc_docs">
> SELECT * FROM alphabet ORDER BY letter
> </cfquery>
>
> Here is the code that dislays all the letters in a table:
> <cfoutput query="getLetters">
> <td width="11" bgcolor="##FFFFFF">
> <div align="center"><a href="###letter#">#letter#</a></div>
> </td>
> </cfoutput>
>
> Here is the code to display the terms:
> <table>
> <cfoutput query="getGlossary" group="first_character">
> <tr bgcolor="##f7f7f7">
> <td valign="top" bgcolor="##ffffff"><div
> align="center"><strong>#first_character#</strong><a
> name="#first_character#"></a></div></td>
> <td bgcolor="##ffffff">
> <table width="100%" cellpadding="0" cellspacing="2"
> border="0">
> <cfoutput>
> <tr>
> <td
> bgcolor="##ffffff"><strong>#getGlossary.name#</strong> -
> #getGlossary.meaning#</td>
> </tr>
> <tr>
> <td bgcolor="##ffffff"><img
> src="../_common/images/spacer_grey.gif" width="100%" height="1"></td>
> </tr>
> </cfoutput>
> <tr>
> <td align="right" bgcolor="##ffffff"><a
> href="##top">Top of Page</a></td>
> </tr>
> </table></td>
> </tr>
> </cfoutput>
> </table>
>

[CriticalIM]

If I understand you correctly, very easy. If I miss the boat, let me know and
I will fix. After you run the glossary query, creat a list of
first_character(s). <!---select all the glossary terms to be displayed on the
page---> <cfquery name='getGlossary' datasource='erinc_docs'> SELECT * FROM
glossary ORDER BY first_character, name </cfquery> <cfset temp =
Valuelist(getGlossary.first_character)> Then simply only query letters in the
list. <!---get letters for links at top of page---> <cfquery name='getLetters'
datasource='erinc_docs'> SELECT * FROM alphabet ORDER BY letter Where Letter
IN(#temp# ) </cfquery> No you only have result letters that are in the
clossary result set. Allen

[jmoshier]

Allen,
This is close but if I am understanding your code correctly. The getLetters
query will only show letters that are in both querys. I want to show all 26
letters and only have links on the letters that are in the getGlossary query.

Thanks
Jennifer

[[CJ]]

you really don't need a seperate column to store 'firstcharacter'. you can
just do a left(name, 1) AS firstCharcter in your SQL. try the following:
<cfquery name='getGlossray' datasource='erinc_docs'> SELECT
left(name, 1) AS firstCharacter, name FROM glossary
ORDER BY left(name, 1) AS firstCharacter, name </cfquery>
<cfset alphaList = 'a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y ,z' />
<cfset glossaryAlphaList = valueList(getGlossary, firstCharacter) />
<cfoutput> <cfloop list='#alphaList#' index='alpha'> <cfif
listContains(glossary, alpha)> <a href='###alpha#'>#alpha#</a>
<cfelse> #alpha# </cfif> </cfloop>
</cfoutput> *disclaimer: not tested :D