Query of Query (QoQ) help 2

[jwdcfdeveloper]

I am looking for a way to optimize the queries for a website I am working on. I am displaying a page with a bunch of products. The page includes:

1. Product id
2. Product name
3. Prices (depends on the customers status but there 5 different prices for each product)
4. text field to hold number of each product ordered

The first three fields come from the database. The way I am getting them is by creating a query to list the needed product ids to display on the page. I then create a loop with the first query and then use a second query inside of the loop to get the prices for each product. In this case, there are 69 products to display on the page times 5 prices per product equals a long load time for the page. How can I use QoQ, or an array, structure or anything that would cause me not to create 69 loops to accomplish this task?

Thanks,

JW

 

[webmigit]

I'm guessing you have two tables that look something like this..

Products
pid | pname

Prices
pid | price | status[code]

I hope I'm right... this should be really easy...

The below code assumes your status identifiers are numeric

[code]<cfquery name="getPs"...>
  select * from products, prices
   where products.pid = prices.pid
     and status = #val([red]status_identifier[/red])#
  order by pname
</cfquery>

<cfoutput query="getPs" group="pid">
  #pname# - #price#<br><br>
</cfoutput>

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[r937]

never mind the QoQ, just do a join in your initial query

tip: any time you're doing a query inside a loop, there's probably better way

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)