displaying output - how?

[Micheller]

I'm sure this isn't too complicated, but I'm confused as to how to do it. I am
outputing a list of books - each book title has a few (up to about 3) related
web sites. I want to display the information something like this -

title1 - date, author, related website 1, related website 2, etc.
title2 - date, author, related website 1, related website2, etc.

I've got a query that gets the information I need, but I can't seem to output
it correctly. I keep getting something like this:


title1 - date, author, related website 1,
title1 - date, author, related website 2,
title1 - date, author, related website 3,
title2 - date, author, related website 1,
title2 - date, author, related website 2,
title3 - date, author, related website 1, etc.

I can't seem to get it to show each book once, along with its related web
sites and I'm not sure if the problem is with my initial query, or with the
output. The query is as follows:

<cfquery name="getbooks" datasource="books" dbtype="ODBC">
SELECT bookclubbooks.clubbook_id, bookclubbooks.clubbook_title,
bookclubbooks.clubbook_authorlname, bookclubbooks.clubbook_authorfname,
bookclubbooks.clubbook_isbn, bookclubbooks.clubbook_checkisbn,
bookclubbooks.bookclub_id, bookclubbooks.clubbook_schedule,
clubbookslinks.clubbooklink_id, clubbookslinks.clubbook_id,
clubbookslinks.clubbook_linktitle, clubbookslinks.clubbook_linkurl
FROM bookclubbooks, clubbookslinks
WHERE bookclubbooks.clubbook_id = clubbookslinks.clubbook_id
AND bookclub_id = #url.bookclub_id#
ORDER BY clubbook_schedule
</cfquery>

And here's the relevant output code -

<cfoutput query="getbooks">
<td align="center" valign="top"><img src="..."/></td>
<td align="left" valign="top">#DateFormat(clubbook_schedule, 'mmmm dd,
yyyy')#<br />
<a href="...">#clubbook_title#</a></span>
by #clubbook_authorfname#&nbsp;#clubbook_authorlname# <br />
<cfif #clubbook_linktitle# IS NOT "">Related Web Sites:
<ul><li><a
href="clubbook_linkurl">#clubbook_linktitle#</a></li></ul></cfif></td>
</tr>
</table>
</cfoutput>

I know I need some sort of loop somehow to get it to display all the links for
one title without repeating the title, but I'm confused as to how to do that.
Any suggestions would be greatly appreciated.

Thanks for reading this far.
Michelle

[GeorgeWS]

I think something like this:

<table>
<cfoutput query="getbooks" group="Title">
<tr>
<td>#title#</td>
<td>#author#</td>
<td>#date#</td>

<cfoutput>
<td>#website#</td>
</tr>
</cfoutput>
</cfoutput>
</table>

Hope this helps

[patb96]

Try using the "group" property in the cfoutput tag. This is going to involve
nesting cfoutput tags. See the code below.

<cfoutput query="getbooks"group="clubbook_id" >
<td align="center" valign="top"><img src="..."/></td>
<td align="left" valign="top">#DateFormat(clubbook_schedule, 'mmmm dd,
yyyy')#<br />
<a href="...">#clubbook_title#</a></span>
by #clubbook_authorfname#&nbsp;#clubbook_authorlname# <br />
<cfif #clubbook_linktitle# IS NOT "">Related Web Sites:
<ul><cfoutput><li><a
href="clubbook_linkurl">#clubbook_linktitle#</a></li></cfoutput></ul></cfif></td

>

</tr>
</table>
</cfoutput>

[Micheller]

Thanks so much for your help! I feel stupid for not remembering about the group
attribute. OK, the problem I'm having now is (I think) with my CFIF statement.
I want the book graphic, title, author name and date to show regardless. If
there are related links for a particular book, than I want to also display the
header "related books" with the bulleted list of links. As it is right now, if
there are no related links, than nothing shows at all (for that particular
book). I'm thinking this may mean I can't use the group attribute after all?

Here's the output code I have now:

<cfoutput query="getbooks" group="clubbook_title">
<table>
<tr>
<td align="center" valign="top"><img src="..." alt="book jacket for
#clubbook_title#" width="62" height="94" align="left"/></td>
<td align="left" valign="top">#DateFormat(clubbook_schedule, 'mmmm dd,
yyyy')#<br />
<a href="...">#clubbook_title#</a></span>
by #clubbook_authorfname#&nbsp;#clubbook_authorlname# <br />
<cfif #clubbook_linktitle# IS NOT "">Related Web Sites:<br />
<ul> <cfoutput><li><a
href="clubbook_linkurl">#clubbook_linktitle#</a></li></cfoutput></ul></cfif></td

>

</tr>
</table>
</cfoutput>

Thanks again.

[Stressed_Simon]

You can do thus but the problem is your SQL statement! I assuming that you are
using an inner join to get the books and the related links. That only returns
records that exist in both tables. So you need to use a Left or Right join
(whichever one is relevant) instead. That will return all the results from the
Left or Right table repectively regardless of results in the other!

Post your query and I can help more!

[Stressed_Simon]

Oh I see your query hang on!

[Stressed_Simon]

Try:-

<cfquery name="getbooks" datasource="books" dbtype="ODBC">
SELECT bookclubbooks.clubbook_id, bookclubbooks.clubbook_title,
bookclubbooks.clubbook_authorlname, bookclubbooks.clubbook_authorfname,
bookclubbooks.clubbook_isbn, bookclubbooks.clubbook_checkisbn,
bookclubbooks.bookclub_id, bookclubbooks.clubbook_schedule,
clubbookslinks.clubbooklink_id, clubbookslinks.clubbook_id,
clubbookslinks.clubbook_linktitle, clubbookslinks.clubbook_linkurl
FROM bookclubbooks
LEFT JOIN clubbookslinks ON bookclubbooks.clubbook_id =
clubbookslinks.clubbook_id
WHERE bookclub_id = #url.bookclub_id#
ORDER BY clubbook_schedule
</cfquery>

[Micheller]

It works! I've never been really clear about left and right inner joins, but now I think I'm beginning to understand.

Thanks for the help and the code.

[Stressed_Simon]

Glad to help. I cannot stress any more strongly that getting to grips with SQL
is paramount to building scaleable applications. The database is very powerful
and is where you should do as much work as possible. You will be glad you did
in the long run.