Skip to main content
S
SonOfNels
Known Participant
September 9, 2008
Answered

Dynamic Table with Random Records

  • September 9, 2008
  • 3 replies
  • 713 views
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>
    This topic has been closed for replies.
    Correct answer Dan_Bracuk
    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.

    3 replies

    S
    SonOfNelsAuthor
    Known Participant
    September 9, 2008
    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!
    D
    Dan_BracukCorrect answer
    Inspiring
    September 9, 2008
    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.
    S
    SonOfNelsAuthor
    Known Participant
    September 9, 2008
    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?
    N
    Newsgroup_User
    Inspiring
    September 9, 2008
    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/
    S
    SonOfNelsAuthor
    Known Participant
    September 9, 2008
    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.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices