[cfaussie] Re: Dealing with a random number of images

Steve Onnis Sun, 26 Mar 2006 20:26:32 -0800

this might be easier if your going to go down that way

<cfquery datasource="#application.misc.dsn#" name="q_getProperties">
    SELECT        *
    FROM        re_property AS p, re_agent AS a
    WHERE        a.agentID = p.agentID
    ORDER BY    propPrice,propSuburb
</cfquery>

<cfloop query="q_getProperties">
    <cfquery datasource="#application.misc.dsn#" name="q_getImages">
        SELECT    *
        FROM    re_image
        WHERE    propID = #q_getProperties.propID#
    </cfquery>
    <cfset querySetCell(q_getProperties, "Images", valueList(getImages.imageURL), currentRow)>
</cfloop>

If the query returns no records the valueList will be empty anyway. no need to test it to see if the query returned anything. Also, rather than going to the trouble of creating an array, populating it and then adding the column into the query after it all, may as well just populate the query as you go.

Looking at the process though, I dont see why a join wouldnt have worked.

<cfquery datasource="#application.misc.dsn#" name="q_getProperties">
SELECT *
FROM re_property LEFT OUTER JOIN re_image ON

re_property.propID = re_image.propID,

re_agent

WHERE re_agent.agentID = re_property.agentID
ORDER BY propPrice,propSuburb
</cfquery>

Either way the bit at the top should cut some overhead and reduce the code your having to deal with.

Regards

Steve Onnis

-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED]On Behalf Of Seona Bellamy
Sent: Monday, March 27, 2006 10:45 AM
To: [email protected]
Subject: [cfaussie] Re: Dealing with a random number of images

Hi guys,

Thanks for all of the wonderful help and advice I got from you last week. I've played with it a bit over the weekend, and ended up going with the following solution based on an off-list suggestion from Brett. He suggested that rather than trying to create one really complex query, create them as seperate queries and combine them programatically. So what I have, for anyone interested in seeing the results of this, is the following:

<cfquery datasource="#application.misc.dsn#" name="q_getProperties">
    SELECT        *
    FROM        re_property AS p, re_agent AS a
    WHERE        a.agentID = p.agentID
    ORDER BY    propPrice,propSuburb
</cfquery>
<cfset count = 1>
<cfset aImages = ArrayNew(1)>
<cfloop query="q_getProperties">
    <cfquery datasource="#application.misc.dsn#" name="q_getImages">
        SELECT    *
        FROM    re_image
        WHERE    propID = #q_getProperties.propID#
    </cfquery>
    <cfif q_getImages.recordCount>
        <cfset temp = "">
        <cfloop query="q_getImages">
            <cfset temp = temp & "," & q_getImages.imageURL>
        </cfloop>
        <cfset length = Len(temp) - 1>
        <cfset temp = right(temp,length)>
    <cfelse>
        <cfset temp = "">
    </cfif>
    <cfset aImages[#count#] = temp>
    <cfset count = count + 1>
</cfloop>
<cfset temp = queryAddColumn(q_getProperties,"images","varchar",aImages)>

Not sure if it's the most elegant solution, but it works and it's easy enough for me to mess around with when I need to.

I'm still having trouble figuring out the binding part of this, however. I've read the tutorials on outputting an image this way, which suggests something like the following:

<cfformitem type="html" width="150" height="150" bind="<p><img src="" /></p>"></cfformitem>

Good in theory, and when I had the original query where there was one image attached to each row it was working fine to output the single image. I have decided to simply display the set of images in a row for now, to keep things simple, but I'm not having any luck making it work.

The new query I've generated gives me a column called "images" with a comma-delimited list of image names (this.jpg,that.jpg,theother.jpg). I need a way to loop over this list and output the images, but it seems as if you can't do that.

Is there a way to use the binding call (or somehow get the information from the row selected in the grid) in a <cfloop>? I haven't found
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "cfaussie" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/cfaussie
-~----------~----~----~----~------~----~------~--~---

[cfaussie] Re: Dealing with a random number of images

Reply via email to