Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Replace In the output query with values from another table

Posted on 2008-10-25
4
Medium Priority
?
261 Views
Last Modified: 2013-12-16
Hello experts.
In the code below i want to replace the extras values with the values Extra_text from the Extras_All table
but i need only the first 2 separated with (, ) and if there are more than 2 i want to display in the end (....) to show that there are more than 2.
For in formation about the queries take a look at this question:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Cold_Fusion_Markup_Language/Q_23848316.html
Any help?
ArtExtras:
Artikel_ID        Extras                                
 2                     10
 2                     20
 2                     30                    
 5                     20
 5                     30
 
 a second
Extras_All :    
Extras_ID       Extras_Text                                
10                   text1
20                   text2
30                   text3
A third artikel
art_ID      Price  
2             10
5             15
In the above tables artikel.art_ID=ArtExtras.Artikel_ID
    and             Extras_all.Extras_ID=ArtExtras.Extras
    
<cfoutput query="dboartikel" group="#url.tfm_orderby#" startRow="#StartRow_dboartikel#" maxRows="#MaxRows_dboartikel#">
<cfoutput group="extras">
#price#
  <cfif #Extras# NEQ "">
   #extras#,</cfif>
 </cfoutput>
 </cfoutput>

Open in new window

0
Comment
Question by:Panos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 16

Accepted Solution

by:
duncancumming earned 1500 total points
ID: 22825974
Something like this?  Some words of warning:
1.  I'm not sure of the need for the url.tfm_orderby, but I've kept it in because that's what you've got
2.  If using that in url parameter in your ORDER BY statement, use a cfqueryparam around it
3.  Try and avoid using startrow and maxrows when grouping your cfoutput, it'll just cause confusion.  Better to try and find some other way (i.e. don't use the group attributes, write your own logic for working it out)
4.  The method I've used for building up a string as a list then looping through just the first 2 elements, etc, isn't ideal
<cfquery name="getArtikel">
	SELECT A.art_ID, A.price, E.Extras_ID, E.Extras_Text
	FROM Extras_All E
			INNER JOIN ArtExtras AE ON AE.Extras = E.Extras_ID
			INNER JOIN Artikel A ON AE.Artikel_ID = A.Art_ID
	ORDER BY #url.tfm_orderby#, A.art_ID, E.Extras_ID
</cfquery>
 
<cfset ExtraText = "">
 
<cfoutput query="getArtikel" group="#url.tfm_orderby#">
	<cfoutput group="art_ID">
		#getArtikel.price#
		<cfoutput>
			<cfset ExtraText = ListAppend(ExtraText, getArtikel.Extras_Text)>
		</cfoutput>
		
		<cfloop index="i" from="1" to="#Min(2, ListLen(ExtraText))#">
			#ListGetAt(ExtraText, i)##
			<cfif ListLen(ExtraText) GT 1 AND i EQ 1>, </cfif>
		</cfloop>
		<cfif ListLen(ExtraText) GT 2>...</cfif>
	</cfoutput>
</cfoutput>

Open in new window

0
 
LVL 2

Author Comment

by:Panos
ID: 22829428
Hi duncancumming.
Thank you for your help.
Here is one more question that has to do with this one so that you can have more details:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Cold_Fusion_Markup_Language/Q_23855306.html

The first mistake is that i have FROM artikel a.It must appending your code FROM Extras_ALL.
Now i try to use your code in this example page but i have error
Column A.Art_ID is invalid in theselect list because it is noy contained in either an aggregate function or the group by close.
0
 
LVL 2

Author Comment

by:Panos
ID: 22829541
Sorry for the error.
I did a mistace.
Now the code is giving results but it is not working good
0
 
LVL 2

Author Closing Comment

by:Panos
ID: 31509925
tHANK YOU
REGARDS
PANOS
0

Featured Post

How To Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
The purpose of this video is to demonstrate how to manually back up a WordPress Database. This will be demonstrated using a Windows 8 PC. The Host used will be IPage.com Log into your Hosting account. IPage will be used for demonstration : Locat…
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …
Suggested Courses

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question