Copy link to clipboard
Copied
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
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).
Copy link to clipboard
Copied
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).