Skip to main content
Participating Frequently
August 13, 2009
Question

Displaying First 5 Records

  • August 13, 2009
  • 2 replies
  • 888 views

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>

    This topic has been closed for replies.

    2 replies

    Participating Frequently
    August 13, 2009

    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

    Inspiring
    August 13, 2009

    <cfoutput query="something">

    #data# <cfif currentrow mod something is 0>

    start a new row

    Participating Frequently
    August 14, 2009

    Thanks, Dan!

    That's exactly what I needed.

    Inspiring
    August 13, 2009

    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