CODEWITHSUNDEEP
mysqlcoldfusion
Blue Da Noob
Blue Da Noob

Reputation: 317

Looping over query results inside another query? MySQL & Coldfusion

On my "product editing" page I have a Select List displaying all the 'docs' (from a table) that a user can associate with a product.

 <select name="pdid1" size="15" multiple id="pdid1" style="width:450px"> 
      <cfoutput query="get">
           <option value="#get.pdid#">

            #pdname#

           </option>
      </cfoutput>
  </select>

If a doc is already associated with this product, I do not want it to show up in the <select> list.

The following code is only eliminating the first doc it finds in the table from the Select List. So it is like I need to loop over the first query, 'inside' the second, to make sure the first query removes all of the 'matches' from the 2nd query.

 <!---Checks for any docs already associated with this product--->

 <cfquery name="find" datasource="#application.dsn#">
    SELECT * FROM prodrelated
    WHERE prprodid = '#url.id#'
 </cfquery>

 <!---Now remove any docs from the Select List query that are already associated with this product--->

 <cfquery name="get" datasource="#application.dsn#">
    SELECT * FROM productdocs
    WHERE NOT productdocs.pdid = '#find.pritemid#'
 </cfquery>

How can I make sure that the <select> list does not show any docs that are already associated with this product?

Upvotes: 2

Views: 207

Answers (3)

Matt Busche
Matt Busche

Reputation: 14333

Instead of 

WHERE NOT productdocs.pdid = '#find.pritemid#'

You want to do an IN on all the rows from the original query

WHERE productdocs.pdid NOT IN 
  (<cfqueryparam cfsqltype="cf_sql_integer" value="#valueList(find.pritemid)#" list="true">)

You should always use cfqueryparam for all dynamic database values

valueList(find.pritemid) creates a comma separated list of all the pritemid values from the "find" query and list="true" tells the cfqueryparam that it's expecting a list or values.

Upvotes: 2

Leigh
Leigh

Reputation: 28873

Unless you actually need the data from the first query for something else, it is more efficient use a single query with an OUTER JOIN, or even NOT EXISTS. That avoids the overhead of an additional query. Especially one that retrieves more columns than are needed i.e. select *. Also, performance of a NOT IN (...) clause tends to degrade the more values there are in the list.

Instead, use an OUTER JOIN to return only records where a matching record does not exist in the prodrelated table:

 SELECT pd.pdname, pd.pdid
 FROM   productdocs pd LEFT JOIN prodrelated pr 
            ON pd.pdid = pr.pritemid
            AND  pr.prprodid = <cfqueryparam value="#url.id#" cfsqltype="cf_sql_integer">
 WHERE  pr.prprodid IS NULL

NB: Also, only SELECT the columns you need, rather than SELECT *.

Upvotes: 1

Steve Judd
Steve Judd

Reputation: 228

When you use #find.pritemid# it will only be the first pritemid returned from the query. You can change the WHERE clause to be:

WHERE productdocs.pdid NOT IN (#ValueList(find.pritemid)#)

This uses the li

Upvotes: 1

Related Questions