Dynamic Table with Random Records

[SonOfNels]

What I am trying to do is select random records from a table and display them in a dynamic table with max columns set to 3 and the 4th record to be on a new row. Below is what I have right now and it works to randomly pick records but has no function to set columns in a table. If there is an easier way feel free to let me know. I have tried various ways to do this but none seem to work.

<CFQUERY NAME="getItems" DATASOURCE="absi">
SELECT catfit.*, modcats.*, prodmat.*, prod.* FROM catfit, modcats,
prodmat, prod WHERE prodmat.prodid=catfit.prodid And catfit.catid=modcats.catid
ORDER BY modl ASC </cfquery>

<cfif getItems.recordCount>
<cfset showNum = 3>
<cfif showNum gt getItems.recordCount>
<cfset showNum = getItems.recordCount>
</cfif>
<cfset itemList = "">
<cfloop from="1" to="#getItems.recordCount#" index="i">
<cfset itemList = ListAppend(itemList, i)>
</cfloop>
<cfset randomItems = "">
<cfset itemCount = ListLen(itemList)>
<cfloop from="1" to="#itemCount#" index="i">
<cfset random = ListGetAt(itemList, RandRange(1, itemCount))>
<cfset randomItems = ListAppend(randomItems, random)>
<cfset itemList = ListDeleteAt(itemList, ListFind(itemList, random))>
<cfset itemCount = ListLen(itemList)>
</cfloop>
<cfloop from="1" to="#showNum#" index="i">
<cfoutput>
<table width="205" border="0" align="left" cellpadding="0" cellspacing="0">
<tr>
<td width="235" height="116"> <div align="center"><img src="../Products/ProductPictures/#getitems.pic[ListGetAt(randomItems, i)]#" width="100"></div></td>
</tr>
<tr>
<td class="ProdTitle">#getitems.brand[ListGetAt(randomItems,
i)]# #getitems.modl[ListGetAt(randomItems, i)]#</td>
</tr>
<tr>
<td class="paragraph">$#getitems.prc[ListGetAt(randomItems,
i)]#</td>
</tr>
<tr>
<td><A href="../Products/details.cfm?prodid=#getItems.prodid[ListGetAt(randomItems, i)]#" class="linkcontact">more
info</a></td>
</tr>
<tr>
<td> </td>
</tr>
</table>
</cfoutput>
</cfloop>
</cfif>

[Newsgroup_User]

just search these forums - there are numerous posts with answers on the
same subject.
hint: get familiar with a MOD operator.

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[SonOfNels]

Yeah I know there are alot on inquiries about this already but I have found none of them deal with random records. I found this and tried it every way I could think of to no avail.
<cfoutput>
<table border="1">
<tr>
<cfloop query="qSelect">
<td>#qSelect.currentRow#</td>
<cfif qSelect.currentRow MOD 3 EQ 0>
</tr><tr>
</cfif>
</cfloop>
</tr>
</table>
</cfoutput>

The problem that I run into is with the code I already have when I insert this it just lists a long column. I will keep looking. I the mean time if anyone can help I would appreciate it.

[Dan_Bracuk · Accepted solution]

To start a new row after 3 records, do something like this.

<table>
<tr>
<cfoutput query="something">
<td>#data#<td>
<cfif currentrow mod 3 is 0>
</tr><tr>
</cfoutput>
</tr>
</table>

You should also know that your approach is very inefficient in that you are bringing in to cold fusion more data than you need. First of all you are selecting every field from 3 tables when you don't appear to be using all of them. Second, you are selecting every record and you only want to use 3. There are better ways out there, but they are db specific and you did not say what you are using.

[SonOfNels]

Sorry I am using an Access database. The query is a bit dirty right now but I plan on cleaning it up after I get the thing working right. Is there an easier way to select random records and display them the way I want to?

[SonOfNels]

It think I have found my answer.
<CFQUERY NAME="sewing2" DATASOURCE="absi">
SELECT TOP 12 *
FROM prodmat
ORDER BY Rnd(prodid)
</cfquery>



<table border="1">
<cfoutput>
<tr>
<cfloop query="sewing2">
<td>#sewing2.brand#</td>
<td>#sewing2.modl#</td>
<cfif sewing2.currentRow MOD 3 EQ 0>
</tr><tr>
</cfif>
</cfloop>
</tr>
</cfoutput>
</table>

Thank you for your help Dan your code works like a charm. Now I gotta try it with a full data set and see if I can get it to work. Thanks again!