 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion GroupsGeneralPHPASPPerlColdFusionFlashHTML, CSS, ScriptsBrowsers |
Webmaster Forum / ColdFusion / Advanced Techniques / September 2007SQL / Coldfusion display question
I am not sure if this can be done in one SQL pull / Coldfusion display or not. If a table has an employee name and department, can you list the people using the department in the title and listing the people below it. i.e.) Table: Employees employeeName department ---------------- ------------- Steve Smith Admissions Susan Day Admissions Bob Turner HR Billy West HR Tom Jones Accounting And output this to a page: <b>Admissions</b> Steve Smith Susan Day <b>HR</b> Bob Turner Billy West <b>Accounting</b> Tom Jones Thanks.  SignatureWally Kolcz MyNextPet.org Founder / Developer 586.871.4126 Very easily, check out the <cfoutput ... group=""> parameter. You will want to order the query by the department then employee. The slightly un-intuitive aspect of the group parameter of the <cfquery...> tag is that it is based on the 'ORDER BY' SQL clause NOT the 'GROUP BY' clause. Yes. Use the group attribute of the cfoutput tag. Details are in the cfml reference manual. If you don't have one, the internet does. Make sure you sort your query by the field you are going to group on. That would work and is probably more efficient...you can also order by department, name then check what department you are on and if it is deiffernt...display the new department and list the name under it. Dan's way is the proper way though ;-) Yes, both suggestions will work. As it was already typed, here's an untested cfoutput example. SELECT department, EmployeeName FROM Employees ORDER BY department, EmployeeName <cfoutput query="yourQuery" group="department"> <b>#department#</b><br> <cfoutput>#EmployeeName#<br></cfoutput> <br> </cfoutput> Wow, that didn't work at all :-) I got all the employees listed under all the departments. <cfquery name="deptDirectory" datasource="sql_abt"> Select s.firstname, s.lastname, s.ext, s.title, s.email, d.dept FROM lsstaff S, lsdepts D Where s.deptid = d.deptid AND s.rank <> 2 OR s.rank <> 5 ORDER BY d.dept </cfquery> And then used the suggested CFML to output it.... <cfset deptDirectory = viewState.getValue("deptDirectory")> <cfoutput query="deptDirectory" group="dept"> <b>#dept#</b><br> <cfoutput> <table width="98%" border="0" align="center"> <tr> <td>#firstname# #lastname#</td> <td>#title#</td> <td> <cfif LEN(#ext#) GT 4> #ext# <cfelse> 734.827.#ext# </cfif></td> </tr> </table> <br></cfoutput> <br> </cfoutput> What did I miss? No obvious syntax errors that I can see. So HOW did it not work at all? Error messages? Improper data? Improper display? What went awry? P.S. WHERE s.deptid = d.deptid AND s.rank <> 2 OR s.rank <> 5 This looks a bit odd to me. Would this not return all rank values? I presume every rank value is going to either not be two OR not be five? It looped everyone in each department header. Admissions Everyone in the database Academic Affairs Everyone in the database Career Services Everyone in the database Nevermind, got it to work. [q][i]Originally posted by: [b][b]Newsgroup User[/b][/b][/i] Wow, that didn't work at all :-) I got all the employees listed under all the departments. <cfquery name="deptDirectory" datasource="sql_abt"> Select s.firstname, s.lastname, s.ext, s.title, s.email, d.dept FROM lsstaff S, lsdepts D Where s.deptid = d.deptid AND s.rank <> 2 OR s.rank <> 5 ORDER BY d.dept </cfquery> And then used the suggested CFML to output it.... <cfset deptDirectory = viewState.getValue("deptDirectory")> <cfoutput query="deptDirectory" group="dept"> [B]#dept#[/B]<br> <cfoutput> <table width="98%" border="0" align="center"> <tr> <td>#firstname# #lastname#</td> <td>#title#</td> <td> <cfif LEN(#ext#) GT 4> #ext# <cfelse> 734.827.#ext# </cfif></td> </tr> </table> <br></cfoutput> <br> </cfoutput> What did I miss? [/q] My guess is that you missed column aliases in the select clause. Actually, for anyone else out there, I messed the WHERE clause of rank. I changed it and it worked like a charm. Thanks everyone! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.