1 Reply Latest reply on Sep 14, 2014 3:59 AM by BKBK

    set variables from recordset

    ghanna1

      I have an SQL table that I store OrderID, ItemID, Qty.  There are 15 possible ItemID's (will never be more).  I need to output the order into a single table row that shows the orderID and the qty for each item.

       

      For example:

      <cfquery name="orders" datasource="xyz"

      Select *

      From solditems

      Where orderid = 'Order1"

      </query>

       

      Would return something like:

       

      Order1, Item3, 6

      Order1, Item4, 7

      Order1, Item18, 10

       

      The table needs to look like:

      <tr>

      <td>#orderID#</td>

      <td>#item1qty#</td>

      <td>#item2qty#</td>

      <td>#item3qty#</td>

      <td>#item4qty#</td>

      <td>#item5qty#</td>

      <td>#item6qty#</td>

      <td>#item7qty#</td>

      <td>#item8qty#</td>

      <td>#item9qty#</td>

      <td>#item10qty#</td>

      <td>#item11qty#</td>

      <td>#item12qty#</td>

      <td>#item13qty#</td>

      <td>#item14qty#</td>

      <td>#item15qty#</td>

      </tr>

       

      I'm at a loss trying to figure out how to match the row from the results of the record set into the right #itemXqty#.

       

      Hope I explained this well and thanks in advance for pointers in the right direction!

       

      Gary

        • 1. Re: set variables from recordset
          BKBK Adobe Community Professional & MVP

          You could do something like

           

          <cfoutput><table border="1"><tr><th>#orders.orderID#</th></cfoutput>

          <cfoutput query="orders">

              <td>#itemID##qty#</td>

          </cfoutput>

          </tr></table>

           

          For a start, your query would be more efficient using "select orderID, itemID, qty" instead of "select *" (assuming, of course, that the table has more than the 3 columns).