SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Nested cfquery loops

    Hello forum I am having a little problem with nested query loops that I figured I would turn to the forum to ask for help on. I have been racking my brain trying to figure this one out and drawing a blank. So the situation is I have two queries one for an outer loop and the other for an inner loop. The outer loop gets information about a project or job that has been inserted into the Database previously by the owner and the other get information about the user that has placed a bid on the same project or job. When everything works like it supposed to there should be a table header row with the project details and beneath the header should be a row with people who placed a bid on that particular project or job.
    The outer loop works fine the inner loop is where I am having the issue. Its basically repeating the same row under each column in the second loop which makes sense as to why it would do that but not getting the results as intended. Below is the Html I am using:

    Code:
    <cfif gJobsCreated.recordCount>
    	    <table id="mytable" cellspacing="0" summary="The technical specifications of the Apple PowerMac G5 series">	
    		 <cfoutput query="gJobsCreated" group="jobid">   
    	      <tr>
    	        <th scope="col" abbr="Configurations" class="startBox">#title#</th>
    	        <th scope="col" abbr="Dual 2GHz">#Type#</th>
    	        <th scope="col" abbr="Dual 2GHz">#DateFormat(StartDate,'mm/dd/yyy')#</th>
    	        <th scope="col" abbr="Dual 2GHz">#DateFormat(endDate,'mm/dd/yyy')#</th>
    	        <th scope="col" abbr="Dual 2GHz"><cfif Budget NEQ ''>#DollarFormat(budget)#<cfelseIf Hourly NEQ ''>#DollarFormat(hourly)#/hr<cfelse>#dollarFormat(Fixed)#</cfif></th>
    	        <th scope="col" abbr="Dual 2GHz"><a href = "?app=job&view=details&projectID=#JobID#">View Job</a></th>
    	        <th scope="col" abbr="Dual 2GHz">Award Project</th>
    	      </tr>
    			<cfif guserBids.recordCount>
    			 <cfloop query="guserBids"> 
    		        <tr>
    		            <th scope="row" class="spec"></th>
    		            <td>#dollarformat(price)#</td>
    		            <td ></td>
    		            <td >#iif(Session.subscript EQ 'Free',DE('#Left(email, 5)#...'),de('#email#'))#</td>
    		            <td >
    			          <cfif Round(rating)is 0><img src="#application.path.img#/star-zero.png">
                            <cfelseif Round(rating)is 1 ><img src="#application.path.img#/starrating1.png">
                            <cfelseif Round(rating)is 2 ><img src="#application.path.img#/starrating2.png">
                            <cfelseif Round(rating)is 3 ><img src="#application.path.img#/starrating3.png">        
                            <cfelseif Round(rating)is 4 ><img src="#application.path.img#/starrating4.png"> 
                            <cfelseif Round(rating)is 5 ><img src="#application.path.img#/starrating5.png">
                          </cfif>
    					</td>
    		            <td ><a href = "javascript:void(0);">View Profile</a</td>
    		            <td ><a href = "##" title = "Click here to award this project" onclick = "return confirm('Are you sure you want to award this project to this user?')">Award</a></td>
    		        </tr>
    		    </cfloop>
    		  <cfelse>
    		        <table id="mytable" cellspacing="0" summary="The technical specifications of the Apple PowerMac G5 series">
    			        <tr>
    			           <th scope="row" align="center" style="color:red" class="empty">NO RECORDS AVAILABLE!</th>
    			        </tr>
                    </table>
    		   </cfif>
    	     </cfoutput>
    	    </table>
    </cfif>
    Am I going about this the wrong way???

    here are the queries as well:

    Projects Created:
    Code:
    SELECT     j.JobID, j.City, j.stateID, j.zipcode, j.countryID, j.dateCreated, j.Jobdescription, j.certificationNeeded, j.skillNeeded, j.languagePref, j.experienceNeeded, j.type, j.budget, 
                          j.hourly, j.fixed, j.startDate, j.endDate, a.FullTime, a.partTime, a.AsNeeded, a.WeekendsOnly, a.Contract, jc.title, js.name, s.stateName, c.countryName
    					FROM         tbl_job AS j INNER JOIN
                          tbl_availability AS a ON j.availabilityID = a.availabilityID INNER JOIN
                          tbl_job_category AS jc ON j.categoryID = jc.categoryID INNER JOIN
                          tbl_job_status AS js ON j.statusID = js.statusID INNER JOIN
                          tbl_state AS s ON j.stateID = s.stateID INNER JOIN
                          tbl_country AS c ON j.countryID = c.countryID
    					WHERE     j.userID = ?
    Bids Created:

    Code:
    SELECT     jb.bidID, jb.price, jb.description, jb.dateCreated, jb.dateUpdated, jb.isAccepted, jb.isActive, jb.isArchived, up.firstName, up.lastName, 
                          up.firstName + '  ' + up.lastName AS FullName, jb.userID, up.address1, up.address2, up.phone1, up.phone2, up.city, up.zip, up.stateID, up.countryID, up.license, 
                          up.UrlFacebook, up.profileimage, up.ServiceDesc, up.views, j.userID AS ProjectOwnerID, up.bestContact, up.email, up.title, j.jobID, j.isActive AS JobIsActive, 
                          j.isComplete, COALESCE (B.TotalVotes, 0) AS Votes, CAST(COALESCE (B.totalRate, 0) AS Real) AS Rating, ISNULL(up.views, 0) AS Views, COALESCE (p.totalCost, 0) 
                          AS Total
                FROM      tbl_job_bid AS jb INNER JOIN
                          tbl_user_profile AS up ON jb.userID = up.userID INNER JOIN
                          tbl_state AS s ON up.stateID = s.stateID INNER JOIN
                          tbl_country AS c ON up.countryID = c.countryID INNER JOIN
                          tbl_job AS j ON jb.jobID = j.jobID LEFT OUTER JOIN
                              (SELECT     feedbackID, jobID, profileID, SUM(total_votes) AS TotalVotes, SUM(professionalism + performance + completion + timeliness + overall) 
                                                       / CONVERT(decimal(8, 4), COUNT(total_votes)) / 5 AS totalRate
                                FROM          tbl_user_rate AS UR
                                GROUP BY jobID, feedbackID, profileID, total_votes) AS B ON B.jobID = jb.jobID LEFT OUTER JOIN
                              (SELECT     b.profileID, SUM(a.price) AS totalCost
                                FROM          tbl_job_invoice_charge AS a LEFT OUTER JOIN
                                                       tbl_job_invoice AS b ON a.invoiceID = b.invoiceiD
                                GROUP BY b.profileID, a.price) AS p ON p.profileID = up.profileID
                WHERE   (j.userID = ?)
                AND (j.isActive = ?)

  2. #2
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It essence what I am trying to do is Grab all the jobs the user created from the DB and display it with all the users that placed a bid on that particular job beneath the job display.In this case more than one user can bid on the job so I am having trouble coding that out and displaying it in a table considering the inner loop.

  3. #3
    SitePoint Member
    Join Date
    Sep 2011
    Location
    Romania
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The second query is using a parameter from the first query right? So, you have to place the second query inside the first loop (<cfoutput query> in your case). Anyhow, i would not use <CFOUTPUT QUERY=""> but also a <CFLOOP>. So, where is the second query in your code?

  4. #4
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,074
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    If I was doing this, I would create one query and join all the tables that you need, then use an ORDER BY clause that specified both the Project/Job and the Owner. Then I'd do something like this:

    <cfoutput query="queryname" group="project">
    - stuff from outer loop here -
    <cfoutput group="owner">
    -stuff from inner loop here -
    <cfoutput> individual bid info for each bidder </cfoutput>
    ...
    </cfoutput>
    ...
    </cfoutput>
    <cfset myblog = "http://cydewaze.org/">

  5. #5
    SitePoint Member
    Join Date
    Sep 2011
    Location
    Romania
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "group" is an option too. Not very used though...

  6. #6
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,074
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Yeah, "group" is the best kept secret of <cfoutput>

    I use it like mad!
    <cfset myblog = "http://cydewaze.org/">


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •