Displaying First 5 Records

[MaxVictoria]

The code below outputs a shopping cart info. I need to achive the following: If a user ordered more than 5 items then all other items  need to show up on the next row (see last output with SKU and Quantity). How do I do that?


<cfquery datasource="#DATASOURCE#" name="xml">
SELECT  orders.date_ordered, users.id, users.firstname, users.lastname,users.organization,users.address1,users.address2,users.city,
users.state,users.zipcode,users.country,users.phonenumber,users.email,users.mailcode,users.ff_date,users.typegroup,users.heard_from,users. CRSBriefing, users.Advocate, users.mailcode,  users.FairTrader,  users. ORBOC,users.GGWY,orders.Quantity,orders.itemName,orders.item_lang,orders.SKU,
orders.itemid, orders.userid,orders.item_lang
FROM orders
LEFT OUTER JOIN users
ON users.id = orders.userid
WHERE orders.userid = users.id
AND
((date_ordered BETWEEN #CreateODBCDate(startDate)# AND #CreateODBCDate(endDate)#+1))
</cfquery>



<cfoutput query="xml" group="lastname">

  <TR>
<td align="left"> </td>
   <td align="left">#firstname#</td>
    <td align="left">#lastname#</td>
      <td align="left">#organization#</td>
        <td align="left">#address1#</td>
          <td align="left">#address2#</td>
            <td align="left">#city#</td>
              <td align="left">#state#</td>
              <td align="left">#zipcode#</td>
      
          <td align="left">#phonenumber#</td>
            <td align="left">#email#</td>
               <td align="left">#mailcode#</td>
               <td align="left"> </td>
               <td align="left"> </td>
              
              
<cfoutput>

<TD align="left" >#SKU#</TD>
  <TD align="left">#Quantity#</TD>
</cfoutput>
</tr>
</table>

[Dan_Bracuk]

Sounds like you want to do something like

select somefields, count(*) thecount,

case when count(*) >= 5 then 1 else 2 sortfield

from sometables

where whatever

group by somefields

order by sortfield, thecount desc

[MaxVictoria]

Thanks Dan for replying.

What I need is to be able to display 5 orders per row. For example if a user ordered 15 different items, then I need to have the following layout:

Mr. James Smith Item 1  Item 2 Item 3 Item 4 Item 5

                          Item 6 Item 7 Item 8 Item 9 Item 10

                          Item 11 Item 12 Item 13 Item 14 Item 15

[Dan_Bracuk]

<cfoutput query="something">

#data# <cfif currentrow mod something is 0>

start a new row

[MaxVictoria]

Thanks, Dan!

That's exactly what I needed.

[MaxVictoria]

Here's the code that I currently have. It does almost exactly what I need, however the end result looks like this:

Mr. John Smith SKU1, SKU2, SKU3, SKU4, SKU5

Mr. John Smith SKU6, SKU7, SKU8, SKU9, SKU10

There is no need to have `Mr. John Smith` twice. How do I get rid off it and instead have spaces so ISKU6 is under SKU1, SKU7 is under ISKU2 and etc.

Here's the code that I have:

<table>
<cfoutput query='xml' >
<cfif CurrentRow MOD 5 EQ 1>
<tr><td align="left">#firstname#</td>
<td align="left">#lastname#</td>
     </cfif>
<td align="left">#SKU#</td>
<td align="left">#quantity#</td>
<cfif CurrentRow MOD 5 EQ 0>
</tr> </cfif> </cfoutput> <cfif xml.RecordCount MOD 5 EQ 1>
<td align="left"></td> <td align="left"></td> <td></td> </td> </cfif>
</table>

What am I doing wrong?

[Dan_Bracuk]

Use the group attribute of cfoutput.