need to simulate 2 cfoutput queries w/o nesting them

<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)
phillystyle123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hartCommented:
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
phillystyle123Author Commented:
img1, img2, img3, img4 afname1 alname1
img1, img2, img3, img4 afname2 alname2
img1, img2, img3, img4 afname3 alname3
0
hartCommented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Nathan Stanford SrSenior ProgrammerCommented:
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
James RodgersWeb Applications DeveloperCommented:
hart's answer will work
0
phillystyle123Author Commented:
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
James RodgersWeb Applications DeveloperCommented:
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
James RodgersWeb Applications DeveloperCommented:
>>1. i'm going to have more than 4 images in the output but i only want to show the 1st 4.


0
James RodgersWeb Applications DeveloperCommented:
sorry hit submit before i was finished
0
phillystyle123Author Commented:
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
James RodgersWeb Applications DeveloperCommented:
sorry typo...

should be one word

<cfqueryparam value="getArtist.id">
0
phillystyle123Author Commented:
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
James RodgersWeb Applications DeveloperCommented:
what db are you using?
0
phillystyle123Author Commented:
MySQL
0
James RodgersWeb Applications DeveloperCommented:
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
phillystyle123Author Commented:
<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
hartCommented:
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
hartCommented:
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
hartCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hartCommented:
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
hartCommented:
gr8 it worked :-)

Regards
Hart
0
phillystyle123Author Commented:
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
hartCommented:
oh thats not so gr8 ;-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.