Solved

need to simulate 2 cfoutput queries w/o nesting them

Posted on 2003-11-20
23
263 Views
Last Modified: 2013-12-24
<cfoutput query="Artists"><tr>
     <td><img src="../images/sm/#sm#"></td>
    <td><p><a href="details.cfm?ArtistID=#Artists.ArtistID#">#Artists.FirstName#&nbsp;#Artists.LastName#</a></p></td>
  </tr></cfoutput>

not only do i need this to repeat vertically (which it does) but i need to generate 4 <td> each with a #sm# in them (horizontally)
0
Comment
Question by:phillystyle123
  • 8
  • 7
  • 7
  • +1
23 Comments
 
LVL 11

Expert Comment

by:hart
ID: 9794621
i am not so sure what u want..
could u show an example buddy..

the above code will show details as

img1 afname1 alname1
img2 afname2 alname2
img3 afname3 alname3
... etc..

now what do u want this to be

Regards
Hart


0
 

Author Comment

by:phillystyle123
ID: 9796244
img1, img2, img3, img4 afname1 alname1
img1, img2, img3, img4 afname2 alname2
img1, img2, img3, img4 afname3 alname3
0
 
LVL 11

Expert Comment

by:hart
ID: 9796323
ok try this out

i am assuming that u have only 4 images
<cfset Lst_Image = ValueList(Artists.sm)>

<cfoutput query="Artists">
<tr>
     <cfloop list="#Lst_Image#" index="sm_img">
     <td><img src="../images/sm/#sm_img#"></td>
     </cfloop>
    <td><p><a href="details.cfm?ArtistID=#Artists.ArtistID#">#Artists.FirstName#&nbsp;#Artists.LastName#</a></p></td>
</tr>
</cfoutput>

Regards
Hart
0
 
LVL 5

Expert Comment

by:nathans
ID: 9796820
Question....
img1, img2, img3, img4 afname1 alname1
img1, img2, img3, img4 afname2 alname2
img1, img2, img3, img4 afname3 alname3

or is it more like this...
img1, img2, img3, img4 afname1 alname1
img4, img5, img6, img4 afname2 alname2
img7, img8, img9, img4 afname3 alname3


TEST (query1=a, query2=b)
a.img1, a.img2, a.img3, a.img4 b.afname1 b.alname1
a.img4, a.img5, a.img6, a.img4 b.afname2 b.alname2
a.img7, a.img8, a.img9, a.img4 b.afname3 b.alname3



So one outputs the Image and one out puts the afname, alname ?

or what?

Make up a some fake queries and tables so we can see what you mean but let us know the column names and primary keys etc.
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9797485
hart's answer will work
0
 

Author Comment

by:phillystyle123
ID: 9798170
http://huffamoose.com/ikonnew/artists/ is the link to hart's solution.  almost works:

1. i'm going to have more than 4 images in the output but i only want to show the 1st 4.
2. they have to be ArtistID specific. so, the 4 horiz. images match the artist name.
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9798598
how are the artists and images stored in the db

artist
id, lname, fname

img
img_id,atrist_id,img_name

?

if so

<cfquery name="getArtist">
select id, lname, fname
from artist
</cfquery>

<cfloop query="getArtist">
<cfquery name="getArtistImage">
select top 4 img_id,atrist_id,img_name
from images
where atrist_id = <cfquery param value="getArtist.id">
</cfquery>

<cfloop query="getArtistImage">
<td><img src="../images/sm/#img_name#"></td>
</cfloop>
<td> #lname# , #fname#</td>
</cfloop>
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9798897
>>1. i'm going to have more than 4 images in the output but i only want to show the 1st 4.


0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9798899
sorry hit submit before i was finished
0
 

Author Comment

by:phillystyle123
ID: 9798920
Jester_48

using the code below i'm getting the following error;

Error Diagnostic Information
Just in time compilation error

An unknown attribute 'param' has been encountered at document position (12:25) to (12:29) while processing tag CFQUERY. This tag can only take the following attributes:

code:


<cfquery name="Artists" datasource="Ikonltd">
SELECT Artists.FirstName, Artists.LastName, Artists.ArtistID, Artwork.ArtistPage, Artwork.sm
FROM Artists, Artwork
Where Artists.ArtistID=Artwork.ArtistID and Artwork.ArtistPage="1"
GROUP BY Artists.LastName ORDER BY Artists.LastName Asc
</cfquery>

<cfloop query="Artists">
<cfquery name="getArtistImage" datasource="Ikonltd">
SELECT top 4 ArtistID,  Artwork.sm
FROM  Artwork
Where ArtistID=<cfquery param value="Artists.ArtistID">
</cfquery>
</cfloop>

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

<table width="60%" border="0" cellspacing="0" cellpadding="0">
                        
  <tr>
     <cfloop query="getArtistImage"><td><img src="../images/sm/#sm#"></td>
      
    <td><p><a href="details.cfm?ArtistID=#Artists.ArtistID#">#Artists.FirstName#&nbsp;#Artists.LastName#</a></p></td></cfloop>
  </tr>
</table>
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9798936
sorry typo...

should be one word

<cfqueryparam value="getArtist.id">
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:phillystyle123
ID: 9798979
my code:

<cfquery name="Artists" datasource="Ikonltd">
SELECT Artists.FirstName, Artists.LastName, Artists.ArtistID, Artwork.ArtistPage, Artwork.sm
FROM Artists, Artwork
Where Artists.ArtistID=Artwork.ArtistID and Artwork.ArtistPage="1"
GROUP BY Artists.LastName ORDER BY Artists.LastName Asc
</cfquery>

<cfloop query="Artists">
<cfquery name="getArtistImage" datasource="Ikonltd">
SELECT top 4 ArtistID,  Artwork.sm, ArtistPage
FROM  Artwork
Where ArtistID=<cfqueryparam value="Artists.ArtistID"> and Artwork.ArtistPage="1"
</cfquery>
</cfloop>

error:

ODBC Error Code = 42000 (Syntax error or access violation)


[TCX][MyODBC]You have an error in your SQL syntax near '4 ArtistID, Artwork.sm, ArtistPage FROM Artwork Where ArtistID='Artists.Arti' at line 1


is top 4 formatted properly?
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9799056
what db are you using?
0
 

Author Comment

by:phillystyle123
ID: 9799117
MySQL
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9799178
might not be supported try this, get rid ogf the top 4
and in the inner cfloop where you are outputing the images use
<cfloop query="getArtistImage" endrow="4">

<cfquery name="getArtist">
select id, lname, fname
from artist
</cfquery>

<cfloop query="getArtist">
<cfquery name="getArtistImage">
select img_id,atrist_id,img_name
from images
where atrist_id = <cfquery param value="getArtist.id">
</cfquery>

<cfloop query="getArtistImage" endrow="4">
<td><img src="../images/sm/#img_name#"></td>
</cfloop>
<td> #lname# , #fname#</td>
</cfloop>
0
 

Author Comment

by:phillystyle123
ID: 9799293
<cfloop query="Artists">
<cfquery name="getArtistImage" datasource="Ikonltd">
SELECT top 4 ArtistID,  Artwork.sm, ArtistPage
FROM  Artwork
Where ArtistID=<cfqueryparam value="Artists.ArtistID"> and Artwork.ArtistPage="1"
</cfquery>
</cfloop>

<cfloop query="getArtistImage" endrow="4">
<cfquery name="Artists" datasource="Ikonltd">
SELECT Artists.FirstName, Artists.LastName, Artists.ArtistID, Artwork.ArtistPage, Artwork.sm
FROM Artists, Artwork
Where Artists.ArtistID=Artwork.ArtistID and Artwork.ArtistPage="1"
GROUP BY Artists.LastName ORDER BY Artists.LastName Asc
</cfquery>
</cfloop>

error: QUERY

The QUERY attribute of the tag does not specify the name of an available query
0
 
LVL 11

Expert Comment

by:hart
ID: 9801659
the ideal practice of coding is to use CFSQLTYPE in cfqueryparam
<CFQUERYPARAM CFSQLTYPE="cf_sql_numeric" VALUE="#Artists.ArtistID#">
if the field is numeric.

and if itsvarchar or text or char then
<CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#Artists.ArtistID#">

by default the sqltype is varchar...

and in ur query u r just saying <cfqueryparam value="Artists.ArtistID">
use #Artists.ArtistID#

and the above written code is not proper becaus eur query is inside a loop of artists and u r accessing that query outside the loop..

look at jesters code more closely he is running a loop on the artists and then running a query

<cfquery name="Artists" datasource="Ikonltd">
SELECT Artists.FirstName, Artists.LastName, Artists.ArtistID, Artwork.ArtistPage, Artwork.sm
FROM Artists, Artwork
Where Artists.ArtistID=Artwork.ArtistID and Artwork.ArtistPage = <CFQUERYPARAM CFSQLTYPE="cf_sql_numeric" VALUE="1">
GROUP BY Artists.LastName ORDER BY Artists.LastName Asc
</cfquery>

<cfoutput query="Artists">
    <cfquery name="getArtistImage" datasource="Ikonltd" maxrows="4">
           SELECT Artwork.sm
           FROM  Artwork
           Where ArtistID = <CFQUERYPARAM CFSQLTYPE="cf_sql_numeric" VALUE="#Artists.ArtistID#"> and Artwork.ArtistPage = <CFQUERYPARAM CFSQLTYPE="cf_sql_numeric" VALUE="1">
   </cfquery>
   
   <cfset sFname = Artists.FirstName>
   <cfset sLname = Artists.LastName>
   <cfset nArtistID = Artists.ArtistID>
   <tr>
       <cfloop  query="getArtistImage">
       <td><img src="../images/sm/#sm#"></td>
       </cfloop>
       <td><p><a href="details.cfm?ArtistID=#nArtistID #">#sFname #&nbsp;#sLname#</a></p></td>
   </tr>
</cfoutput>

Regards
Hart






0
 
LVL 11

Expert Comment

by:hart
ID: 9801662
u can remove the variables
<cfset sFname = Artists.FirstName>
  <cfset sLname = Artists.LastName>
  <cfset nArtistID = Artists.ArtistID>

and use Artists.FirstName directly

Regards
Hart
0
 
LVL 11

Accepted Solution

by:
hart earned 75 total points
ID: 9801706
i don't know an outter join syntax in MYSQL.. but i think
one query should do it for u..

<cfquery name="Artists" datasource="Ikonltd">
SELECT Artists.FirstName, Artists.LastName, Artists.ArtistID, Artwork.ArtistPage, Artwork.sm
FROM Artists, Artwork
Where Artists.ArtistID = Artwork.ArtistID(+)
and (Artwork.ArtistPage = <CFQUERYPARAM CFSQLTYPE="cf_sql_numeric" VALUE="1">
or Artwork.ArtistPage is null)
ORDER BY Artists.LastName Asc
</cfquery>

i have used (+) as outer join because this is how i use it in oracle...
<cfset ctr = 0>
<cfset sLastName = ''>
<cfoutput query="Artists">
   <tr>
    <cfif ctr lte 4>
       <td><img src="../images/sm/#sm#"></td>
    </cfif>
   <cfif CompareNoCase(LastName,sLastName) EQ 0>
      <cfset ctr = IncrementValue(ctr)>
   <cfelse>
      <td><p><a href="details.cfm?ArtistID=#Artists.ArtistID#">#Artists.FirstName#&nbsp;#Artists.LastName#</a></p></td>
      <cfset ctr = 1>
      <cfset sLastName = Artists.LastName>
     </cfif>
    </tr>
</cfoutput>
 

Try this an let me know

Regards
Hart
0
 
LVL 11

Expert Comment

by:hart
ID: 9801741
sorry a small change in the above code

<CFSET ctr = 0>
<CFSET sLastName = ''>
<CFOUTPUT query="Artists">
    <CFIF CompareNoCase(LastName,sLastName) EQ 0>
            <CFSET ctr = IncrementValue(ctr)>
      <CFELSE>
      <TR>
            <CFSET ctr = 1>
            <CFSET sLastName = LastName>
      </CFIF>            
      <CFIF ctr lte 4>                        
            <TD><IMG src="../images/sm/#sm#"></TD>            
      </CFIF>
      <CFIF CTR EQ 4>                        
            <TD><P><A href="details.cfm?ArtistID=#ArtistID#">#FirstName#&nbsp;#LastName#</A></P></TD>
      </TR>      
      </CFIF>            
</CFOUTPUT>


Regards
Hart
0
 
LVL 11

Expert Comment

by:hart
ID: 9808250
gr8 it worked :-)

Regards
Hart
0
 

Author Comment

by:phillystyle123
ID: 9809261
yes - me too and after all that, the client didn't like the look of it anyway  so, we didn't use the idea afterall! ;-)
0
 
LVL 11

Expert Comment

by:hart
ID: 9809299
oh thats not so gr8 ;-)
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now