Displaying images in rows of 5

[BookBook]

Hello,

I have about 500 images that I want to display in rows of 5. Currently, I only know how to display them in 1 vertical column like this:

<CFOUTPUT QUERY="List" GROUP="AuthorID">
<CFOUTPUT>
<TR>
<TD ALIGN="left" VALIGN="top" WIDTH="542" style="padding-left:2em;text-indent:-2em"><A HREF="#request.BaseURL#/book.cfm?-#URL_Title#-&BookID=#BookID#"><img src="#request.BaseURL#/images/#Image#" width="50"></A></TD>
</TR>
</CFOUTPUT>
</CFOUTPUT>

Thank you for your help!

[WolfShade · Accepted solution]

<CFOUTPUT QUERY="List" GROUP="AuthorID">
<!--- You should have something here for author, since you're grouping by author --->
    <TR>
<CFOUTPUT>
        <TD ALIGN="left" VALIGN="top" WIDTH="542" style="padding-left:2em;text-indent:-2em">
            <A HREF="#request.BaseURL#/book.cfm?-#URL_Title#-&BookID=#BookID#"><img src="#request.BaseURL#/images/#Image#" width="50"></A>
        </TD><cfif List.currentrow % 5 eq 0 AND List.currentrow neq List.recordCount>
    </tr>
    <tr>
</cfif>
</CFOUTPUT>
</TR>
</CFOUTPUT>

HTH,

^ _ ^

[BookBook]

Thank you, that worked great! You pointed out that I'm grouping by author. I meant to group, or order, the 500 book cover images by their Series Number (the "Series_no" Table in Inventory). This is as far as I got. Can you please help me do this, and teach me how to limit the list to the first 100 in the series? Here's as far as I got with your code and code that belonged to another page. 

<CFPARAM NAME="attributes.action" DEFAULT="">
<CFPARAM NAME="attributes.dsn" DEFAULT="">
<cfparam name="browseBy" default="Author">
<cfparam name="attributes.AuthorFirstName" default="">

<CFQUERY DATASOURCE="#request.MainDSN#" NAME="List">
SELECT Inventory.*,
Authors.AuthorID,
Authors.AuthorFirstName,
Authors.AuthorLastName,
Authors.Pseudonym
FROM Inventory
INNER JOIN
Authors
ON Authors.AuthorID=Inventory.AuthorID
ORDER BY AuthorLastName, AuthorFirstName, Title
</CFQUERY>

<TABLE BORDER="0" WIDTH="582" CELLSPACING="0" CELLPADDING="0" ALIGN="center">
<CFOUTPUT QUERY="List" group="Series_no">
<TR>
<CFOUTPUT>
<TD ALIGN="center" VALIGN="top" WIDTH="542" style="padding-left:2em;text-indent:-2em">
<A HREF="#request.BaseURL#/book.cfm?-#URL_Title#-&BookID=#BookID#"><img src="#request.BaseURL#/images/#Image#" width="75"></A>
</TD><cfif List.currentrow % 5 eq 0 AND List.currentrow neq List.recordCount>
</tr>
<tr>
</cfif>
</CFOUTPUT>
</TR>
</CFOUTPUT>

</TABLE>

[WolfShade]

Hi, 123proust,

I would like to offer some constructive criticism, if I may.  In your select, you are using * to get everything.  I can be a bit pedantic, and would like to suggest that you definitively name each column in your Inventory table, as using * forces the database to reference an internal table to get the column names.  Additionally, if you do not require every column in that table, then you are pushing unneeded data across the network and adding unnecessary processing to the CPU.

If you are grouping by series number, then that should be reflected in your query using the GROUP BY clause.

As far as listing only the first 100, that will depend upon what flavor of database server you are using, as MS-SQL is slightly different from Oracle, which (I believe) is slightly different than MySQL, and so on.  Some people will just grab every record and use CF code for pagination.  This is not recommended.  At least not by me.  One should set the limits within the query and pass a page number or some such to the database in the query, and get only the desired records.

Now that you are grouping by series number, do you plan to display the series number in the output?  Or is there a related name that you would display?  Or is it just for grouping and the value not important?

V/r,

^ _ ^

UPDATE:  Thank you for marking my answer as correct.  I do appreciate it.

[BookBook]

I'm using an MS-SQL database. Thank you for explaining why I should list my database columns instead of writing "Inventory.*". However, the page wouldn't load when I wrote "Inventory.Series_no".

Taking your 2nd suggestion, I changed the code to read "ORDER BY Series_no" which seemed to have the desired effect. Now I need to know how to limit the results to the first 100 numbers. I do not plan to display the numbers, just the corresponding images.

Since I borrowed code from another page where the books were grouped by Author to make my page where the books are grouped by Series Number, the code is cluttered with lines that are probably unnecessary. I'm hoping you can tell me which lines to delete.

[Dave Watts]

If you can't refer to Inventory.Series_No, this makes me think you don't have a field in the Inventory table called Series_No. In an earlier post, you mention having a "Series_No table in Inventory", which makes me a little more confused. Anyway, you can settle this really quickly by writing a simple test page that has something like this:

<cfquery name="test" ...>

SELECT TOP 10 * FROM Inventory

</cfquery>

<cfdump var="#test#"><cfabort>

This will create an HTML table that shows you all of the columns in the Inventory table.

That example, by the way, shows how you'd select the top 10 records in MS SQL Server. But this kind of question isn't something you need to ask here, you can just Google for things like "select top 10 ms sql server" to answer these things really quickly. There's nothing wrong with asking it here, of course! But you'll just get answers faster without having to ask them yourself.

Dave Watts, Eidolon LLC

[BookBook]

Can you please help me revise the code I pasted above so that my book cover images # 1-100 are sorted by Series Number (Inventory.Series_no), and not by Author? If you can show me what the code is supposed to be, I can probably fix any other problems with my database on my own. Thank you!

[Dave Watts]

Did you confirm that this is actually the name of your field, as I asked earlier? I'm not trying to be pushy, but if you got an error trying to refer to it directly in your query there's some sort of problem.

Dave Watts, Eidolon LLC

[BookBook]

Yes, that's the name of my field. I just typed it wrong before. 

[Dave Watts · Accepted solution]

OK, then you should be able to modify your ORDER BY clause in your SQL statement like so:

ORDER BY Inventory.Series_No, Authors.AuthorLastName, Authors.AuthorFirstName, Authors.Title

and reduce the number of CFOUTPUTs you have, since you're not grouping your output:

<table border="0" width="582" cellspacing="0" cellpadding="0" align="center">
<cfoutput query="List">
<tr>
<td align="center" valign="top" width="542" style="padding-left:2em;text-indent:-2em">
<a href="#request.BaseURL#/book.cfm?-#URL_Title#-&BookID=#BookID#"><img src="#request.BaseURL#/images/#Image#" width="75"></a>
</td><cfif List.currentrow % 5 eq 0 AND List.currentrow neq List.recordCount>
</tr>
<tr>
</cfif>
</cfoutput>

</table>

I hope I didn't make any typos, but this should get you pretty close. I do have a couple of comments, though. First: your DB schema seems a bit odd to me. Tables should represent entities in the real world. If you want a list of books, you should have a "books" table rather than an "authors" table. You should be able to join books against authors when you want to get a list of books and their corresponding authors or vice-versa.

Second, you don't really need #request.BaseURL# at all here. These are perfectly valid site-specific URLs:

<a href="/book.cfm">

<img src="/images/someimage.jpg">

The fewer things your code does, the less likely it is to have problems!

Dave Watts, Eidolon LLC

[BookBook]

Can you please help me revise the code I pasted above so that my book cover images # 1-100 are sorted by Series Number (Inventory.Series_no), and not by Author? If you can show me what the code is supposed to be, I can probably fix any other problems with my database on my own. Thank you!

[WolfShade · Accepted solution]

Actually, Dave's offering was pretty close to what you are looking for, except the opening TR needs to be before the CFOUTPUT and the closing TR should be after the /CFOUTPUT.

<table border="0" width="582" cellspacing="0" cellpadding="0" align="center">
	<tr>
	<cfoutput query="List">
		<td align="center" valign="top" width="542" style="padding-left:2em;text-indent:-2em">
			<a href="#request.BaseURL#/book.cfm?-#URL_Title#-&BookID=#BookID#"><img src="#request.BaseURL#/images/#Image#" width="75"></a>
		</td><cfif List.currentrow % 5 eq 0 AND List.currentrow neq List.recordCount>
	</tr>
	<tr>
	</cfif>
	</cfoutput>
	</tr>
</table>

This will start a TR, insert 5 TDs, then close and open another TR.  (% 5 is modulous of 5; when 0, the current row is evenly divisible by 5 and will start a new row as long as it's not the last record in the set.)

But Dave is right about reducing your CFOUTPUT.. since you're now ordering by serial_no first, the grouping is unnecessary, esp since you're not actually displaying anything about the serial_no.

HTH,

^ _ ^

[Dave Watts]

Good catch!

Dave Watts, Eidolon LLC

[BookBook]

Thank you, this works! Now I just need to know how to limit the images to #s 1-100.

[WolfShade · Accepted solution]

Dave provided that for you.

SELECT TOP 100 table.column1, table.column2, table2.column1, table2.column2

FROM table LEFT OUTER JOIN table2 ON table2.column1 = table.column1

ORDER BY blah blah blah

Now, this will limit to just the first 100.  If you are looking for something that does pagination, it gets a bit more complex.

HTH,

^ _ ^

UPDATE:  I haven't worked with MS SQL for a long time, so I had to look it up.  Here's a good tutorial on pagination in MS SQL..

[BookBook]

Hi WolfShade,

Can you please help me add code that will display a range in the Series Number, because the 100 books I want to feature are not necessarily #'s 1-100? For example, I may want to build a page that feature #'s 329-457.

Thank you!

[WolfShade]

I have provided a link in my last reply that can show you how to do pagination.

HTH,

^ _ ^

[BookBook]

Thank you, but I just want to build one page that will display a range of images that doesn't start at #1. As I said, I may need the page to display #'s 329-457. Is that possible?

[WolfShade]

Again, the link I provided will tell you exactly how to do that.  Have you looked at the content?  The answer is there.  I am not going to build this, for you.  This is a user to user forum for help with existing code.  Please make an effort, then ask if what you wrote isn't working.

^ _ ^

[Dave Watts]

At this point, WolfShade is right, you're leaving CF territory and just asking "how to program" questions. But if this is literally all you want to do, it's pretty simple. I don't know what field you're using for the number, but we're basically talking about

SELECT TOP 100 * FROM table

WHERE field > 328

Dave Watts, Eidolon LLC

[BookBook]

Sorry, my first read of the tutorial on pagination in MS SQL. made me think that it was just for adding pages. Reading the tutorial a couple more times, I now see how using OFFSET and FETCH can define a range on a single page. I got some results trying it out, but I'm still trying to master it. I did not get results when I tried to define a range using TOP and WHERE, as in the above example, but maybe I can learn more about that on Google.

[BookBook]

I learned that I couldn't define a range using TOP and WHERE because that method doesn't support alphanumeric content. Some of the numbers in my series have letters. Using OFFSET and FETCH is working for me. One thing I don't understand are the <cfparam> tags. Are they needed? I couldn't understand their function when I Googled them. I guessed when I wrote them into my code from what I'd seen on other pages.

<CFPARAM NAME="attributes.action" DEFAULT="">
<CFPARAM NAME="attributes.dsn" DEFAULT="">
<cfparam name="browseBy" default="Inventory">
<cfparam name="attributes.Series_no" default="">
<cfparam name="attributes.URL_Title" default="">
<cfparam name="attributes.BookID" default="">
<cfparam name="attributes.Image" default="">

<CFQUERY DATASOURCE="#request.MainDSN#" NAME="List">
SELECT Inventory.Series_no,
Inventory.URL_Title,
Inventory.BookID,
Inventory.Image
FROM Inventory
ORDER BY Series_no
OFFSET 135 ROWS
FETCH NEXT 243 ROWS ONLY;
</CFQUERY>

[Dave Watts · Accepted solution]

To add to WolfShade's correct CFPARAM answer, you can also use CFPARAM as a way for testing a variable's existence before the rest of your code executes, rather than in the middle of your code. That's not what's happening in your example, because you're providing a default value for all of the parameters. But if you weren't, you could throw an error before your CFQUERY starts running, and that error would be easier for you and automated error handlers to understand.

Dave Watts, Eidolon LLC

[WolfShade · Accepted solution]

CFPARAM is a way to define and set a value to a variable IF the variable does not already exist.  If it does exist, nothing happens, the value does not change.  It's akin to:

<cfif NOT StructKeyExists(variables,'thisVar')>

<cfset thisVar = "foo" />

</cfif>

HTH,

^ _ ^

[BookBook]

Thanks. It sounds like you're saying that I don't need any of the <cfparam> lines then. Is that correct?