ColdFusion Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Programming Languages - More > ColdFusion Development
Reload this Page

Display unexpected results???

Discuss Display unexpected results??? in the ColdFusion Development forum on Dev Shed.
Display unexpected results??? ColdFusion Development forum discussing CFML coding practices, tips on CFML, and other CFML related topics. Find out why ColdFusion is the tool of choice for many e-commerce developers.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
1200+ fellow developers rate and compare features of the top IDEs, like Visual Studio, Eclipse, RAD, Delphi and others, across 13 categories. Enjoy this FREE Download of the IDE User Satisfaction Study by Evans Data Corporation. Download Now!
  #1  
Old December 25th, 2004, 08:39 PM
dragonvn dragonvn is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Oct 2004
Posts: 79 dragonvn User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 12 h 31 m 23 sec
Reputation Power: 4
Display unexpected results???
I have two tables:

Category (Programming Languages, Web Design, Database)

Subcategory(PHP, Perl)

PHP, Perl, C, ASP belong to category Programming Languages

I wanna display category and subcategory by order from top to bottom. However I got unexpected results:

- Programming Languages
+--PHP
- WebDesign
+--Perl-Database

I wanna show the result like this:
- Programming Languages
+--PHP
+--Perl- WebDesign
-Database

My scripts:

Quote:
<cfloop query = "list_category" startrow = "1" endrow = "#number_of_category#">
<cfoutput>
Category- #list_category.name#<br>
<!--- Find the record. --->
<cfquery name="list_subcategory" datasource="name" username="user" password="pass">
SELECT * FROM news_subcategory WHERE parent = #list_category.id#
</cfquery>
<!--- Find Number of records. --->
<cfset number_of_subcategory = list_subcategory.RecordCount>
<cfloop query = "list_subcategory" startrow = "1" endrow = "#number_of_subcategory#">
Sub category: +-- #list_subcategory.name#<br>
</cfloop>
</cfoutput>
</cfloop>


Thank in advance,

Tony Tran
Reply With Quote
  #2  
Old December 25th, 2004, 09:04 PM
r937's Avatar
r937 r937 is offline
SQL Consultant
Dev Shed God 25th Plane (17000 - 17499 posts)
  
Join Date: Feb 2003
Location: Toronto Canada
Posts: 17,344 r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level)r937 User rank is Lieutenant General (80000 - 90000 Reputation Level) 
Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 27 m 14 sec
Reputation Power: 891
before we address the sorting problem, is there any reason why you can't do a join?

it looks like you're doing a query inside a loop, which is quite inefficient
__________________
r937.com | rudy.ca
Reply With Quote
  #3  
Old December 26th, 2004, 12:06 AM
dragonvn dragonvn is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Oct 2004
Posts: 79 dragonvn User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 12 h 31 m 23 sec
Reputation Power: 4
Hi r937,

Long time no see ya? How have you been? You are right, I am better to use the function JOIN. Let me try it first and report my results then.

Merry Christmas and Happy New Year,

Tony Tran
Reply With Quote
  #4  
Old December 26th, 2004, 08:44 PM
dragonvn dragonvn is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Oct 2004
Posts: 79 dragonvn User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 12 h 31 m 23 sec
Reputation Power: 4
I am trying with JOIN function but I got an error even though the variable ID, name are in the table news_category.

Quote:
Element ID is undefined in LIST_CATEGORY.


The error occurred in C:\www\cms\news.cfm: line 102

100 : <cfloop query = "list_category" startrow = "1" endrow = "#number_of_category#">
101 : <cfoutput>
102 : <option value=#list_category.id#>- #list_category.name#</option>


Here is my codes:

Quote:
<cfquery name="list_category" datasource="name" username="user" password="pass">
SELECT news_subcategory.name AS subcategory
FROM news_subcategory
LEFT JOIN news_category
ON news_subcategory.parent = news_category.id
</cfquery>

<!--- Find Number of records. --->
<cfset number_of_category = list_category.RecordCount>
.....
.....
.....
<select name="parent">
<cfloop query = "list_category" startrow = "1" endrow = "#number_of_category#">
<cfoutput>
<option value=#list_category.id#>- #list_category.name#</option>
</cfoutput>
</cfloop>
</select>


Note:

If I change the code

Quote:
<cfquery name="list_category" datasource="name" username="user" password="pass">
SELECT news_subcategory.name AS subcategory
FROM news_subcategory
LEFT JOIN news_category
ON news_subcategory.parent = news_category.id
</cfquery>


to

Quote:
<cfquery name="list_category" datasource="name" username="user" password="pass">
SELECT * FROM news_category
</cfquery>


then it works well.
Thank you so much!
Reply With Quote
  #5  
Old December 28th, 2004, 02:55 PM
glively glively is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Dec 2004
Posts: 17 glively User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 30 m 51 sec
Reputation Power: 0
Once you get your query as a JOIN to retrieve the Categories and sub-categories, you can use the CFOUTPUT with the GROUP option to do what you need.

<cfoutput query="getCategories" group="Category">
Output the #Category# here
<cfoutput>
Output the #Subcategories# here
</cfoutput>
</cfoutput>
Reply With Quote
Reply

Viewing: Dev Shed Forums > Programming Languages - More > ColdFusion Development > Display unexpected results???

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 4 hosted by Hostway.