dynamic image woes

[rockhiker]

I am trying to display anywhere from 1- 7 thumbnails that may or may not be
available. The filename for each thumb is stored in the database. All i get is
a placeholder instead of an image. I only want to display the image if a
filename exists for it in the database. If it does not exist in the database ,
I do not want to display anything! (image place holder for example). Please see
code-- I am novice but getting better - thanks to this forum.


<!---seems to work -a list of projects is generated by passing values in a
hotspot's url--->
<cfquery name="cool" datasource="projects">

SELECT endDate, region, county, countyID, roadname, bmp, emp, proj, estcost,
authfunds, paid, narrative, software, image1, image2, image3, image4, image5,
image6, image7, thumb1, thumb2, thumb3, thumb4, thumb5, thumb6, thumb7

FROM completeTest, completeImagesRecent

WHERE completeTest.projectID=completeImagesRecent.projectID

</cfquery>

<!--- this query is used to pass values to the project details page when a
user clicks on a project-- it works--->
<cfquery name="cool" datasource="projects">

SELECT * FROM completeTest,completeImagesRecent
where countyid=#url.countyid#
and completeTest.projectID=completeImagesRecent.projectID
</cfquery>

<!--- finally this code displays six image place holders an no images but does
not generate an error--->

<cfoutput>
<cfif #thumb1# EQ " " >
<cfelse>
</cfif>
<img src="#thumb1#" /><br />


<cfif #thumb2# EQ " " >
<cfelse>
</cfif>
<img src="#thumb2#" /><br />


<cfif #thumb3# EQ " ">
<cfelse>
</cfif>
<img src="#thumb3#" /><br />


<cfif #thumb4# EQ " ">
<cfelse>
</cfif>

<img src="#thumb4#" /><br />



<cfif #thumb5# EQ " ">
<cfelse>
</cfif>
<img src="#thumb5#" /><br />


<cfif #thumb6# EQ " ">
<cfelse>
</cfif>

<img src="#thumb6#" /><br />

</cfoutput>

[Daverms]

Like this,




<cfif #thumb1# EQ " " >
<img src="#thumb1#" /><br />
<cfelse>
<!--- Do nothing --->
</cfif>

[Azadi]

actually, no, not like that. like this:

<cfoutput query="cool">
<cfloop from="1" to="7" index="x">
<cfif len(trim(cool['thumb' & x][currentrow]))>
<img src="#cool['thumb' & x][currentrow]#" /><br />
</cfif>
</cfloop>
</cfoutput>

but, really, you should probably normalize your db and move all images
into their own table (id [PK], projectID [FK to whatever table has
projectID as PK], image) - that way each project will have as many
images as it needs/has available, not fixed to 7 images.

you also do not need to store thumbnail image names in your db if you
employ a naming convention for your image files: i.e. all thumb images
have names as tn_ + name of full-size image. at least that's what i
usually do - in the least it saves some db space...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[rockhiker]

Thanks for the responses - I will try it! and if you look at the select statement the images are in a separate table.

Jim

[Ian Skinner]

Post by rockhiker
if you look at the select statement the images are in a separate table.
Jim

Then you just need to organize that table so that the images are in rows
not columns.

You should not have this.

projectID image1 image2 image3 ... image7
37 red.gif blue.gif green.gif purple.gif

You should have this.

projectID image
37 red.gif
37 blue.gif
37 green.gif
...
37 purple.gif

[rockhiker]

Thank you, I will reorganize the table.
Jim

[rockhiker]

Hello again, - the table has been redone. three fields (projectid, image,
thumb) and added the code suggested in previous posts. The project list now
displays 24 projects when there should be only 7, 24 happens to be the number
of images for a county??? when i click on a project in the list a currentRow
undefined message is returned. please see code below.











<!--- initial page (list of counties)--->

<cfquery name="cool" datasource="projects">

SELECT endDate, region, county, countyID, roadname, bmp, emp, proj, estcost,
authfunds, paid, narrative, software, image, thumb

FROM completeTest,completeImages
completeTest.projectID=completeImages.projectID

</cfquery>

<!---second page (list of projects for selected counties)--->

<cfquery name="cool" datasource="projects">
SELECT * FROM completeTest, completeImages
where countyid=#url.countyid# and
completeTest.projectID=completeImages.projectID

</cfquery>


<!---third page(selected project details page)--->

<cfquery name="cool" datasource="projects">
SELECT completeTest.projectid, endDate, region, county, countyID,
roadname, bmp, emp, proj, estcost, authfunds, paid, narrative, software,
completeImages.thumb

from completeTest, completeImages

where completeTest.projectID=completeImages.projectID </cfquery>

<cfoutput query="cool">
<cfloop from="1" to="7" index="x">
<cfif len(trim(cool['thumb' & x][currentrow]))>
<img src="#cool['thumb' & x][currentrow]#" />
</cfif>
</cfloop>
</cfoutput>

[Ian Skinner]

Post by rockhiker
The project list now
displays 24 projects when there should be only 7, 24 happens to be the number
of images for a county???

That is the expected and proper result. You have 24 records one for
each unique combination of county and image data.

One now uses grouping to handle this and output the desired display.

<cfoutput query="cool" group="projectID">
#cool.projectID#
<!--- output other information you want displayed once for each
porjectID --->
<cfoutput>
#cool.thumb#
<!--- output information you want to display for every record --->
</cfoutput>
</cfoutput>

[rockhiker]

I added
<cfoutput query="cool" group="projectID">
#cool.projectID#

to the project list page and it returned:

Element PROJECTID is undefined in COOL. and I do not see why it is not
defined.

jim

[Ian Skinner]

<cfdump var="#cool#">

Does that explain anything?

Be careful of multiple columns with the exact same name.

[rockhiker]

not sure where to put the

<cfdump var="#cool#">

[Ian Skinner]

Post by rockhiker
not sure where to put the
<cfdump var="#cool#">

Just before the line of code that is breaking so you can see what that
line of code is seeing.

[rockhiker]

I put the cfdump before the line of code that broke and got the cool query
results with 478 rows showing both project information and the images. see
error listed below.

Element PROJECTID is undefined in COOL.


The error occurred in
C:\Webroot\CRABWEB\crabweb1a\Funding\Grants\RAP\complete_00-09_byCounty.cfm:
line 158

156 :
157 : <cfoutput query="cool" group="projectID">
158 : #cool.projectID#
159 : <tr>
160 : <td style="text-align:center;">#cool.CurrentRow#</td>

[Ian Skinner]

Was "projectid" one of those columns in the results? Was there two or
more columns named "projectid"?

[rockhiker]

yes, two projectid fields

there is a projectid field for each table

[Azadi]

your first COOL query does not seem to be selecting projectID column, so
i guess your error is on your second or third page...

your second COOL query selects * - and that's why you get multiple
projectID columns in the query resultset, which creates your error on
cfoutput.

change your second page's COOL query to select just the columns you need
instead of SELECT *.

also, depending on your application logic, you may want to do a LEFT
JOIN on yuor tables instead of the cartesian join you are doing now.
this way if a project does not yet have any images it will still be
selected and returned by the query:

SELECT ct.projectid, ct.endDate, ct.region, ct.county, ct.countyID,
ct.roadname, ct.bmp, ct.emp, ct.proj, ct.estcost, ct.authfunds, ct.paid,
ct.narrative, ct.software, ci.image, ci.thumb
FROM completeTest ct LEFT JOIN completeImages ci ON ct.projectID =
ci.projectID
WHERE ct.countyID = <cfqueryparam cfsqltype="cf_sql_integer"
value="#url.countyid#">


a few other notes re code you posted:

btw, your third COOL query select ALL projects, not a specific one,
since you do not limit results by specific projectID.

and the cfoutput code you posted won't work since you no longer have
multiple thumbX columns in your table.

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[rockhiker]

okay! I started over. The county link goes to page that displays list of
projects for the selected county. This works. When I click on an indivdual
project this I go to the project details page for the selected project. This
works and the project details display correctly. The images that go with the
details do not. about 400 images display I have not been able to figure out
how to display the images for selected project. (got a little further this
time)--please see code thks jim

<!--- this query is returns a list of projects for county--->

<cfquery name="cool" datasource="projects" maxrows="20">
Select *
from completeTest
where countyid between 1 and 89
</cfquery>

<!--- This link passes the variables to the project list page. --->

<cfoutput>
<area shape="poly"
coords="146,65,129,76,134,88,143,95,146,101,150,109,156,123,158,106,157,94,155,8
0" href="complete_00-09_byCounty.cfm?countyid=15&county=#cool.COUNTY#"
alt="Summit County" />
</cfoutput>

<!--- 2nd page --list of projects for the county selected on page1--->

<cfquery name="cool" datasource="projects">
SELECT *
FROM completeTest
WHERE countyid = #url.countyid#
order by enddate desc
</cfquery>

<cfoutput query="cool">

<tr>
<td style="text-align:center;">#CurrentRow#</td>
<td style="text-align:left;">#county#</td>
<td><a
href="/crabweb1a/Funding/Grants/RAP/complete_details.cfm?roadname=#cool.roadname
#&county=#cool.county#&enddate=#cool.enddate#&proj=#cool.proj#

&bmp=#cool.bmp#&emp=#cool.emp#&paid=#cool.paid#&region=#cool.region#&story=#cool
.story#&narrative=#cool.narrative#&software=#cool.software#&projectID=completeTe
sts.projectID">#roadname#</a></td>
<td style="text-align:center;">#DateFormat(enddate,
"yyyy")#</td></tr></cfoutput></table>

<!---all of this does what I want. On the third page below (project details
page) everything except the images display correcty. i now have 400+ images
displaying when there should only be a couple at most for each project.--->

<cfoutput>
<p>Project Number: <strong>#proj#</strong><br />
From Milepost: <strong>#bmp#</strong> To
Milepost:<strong>#emp#</strong><br />
RATA/CAPP funds: <strong>#DollarFormat(paid)#</strong><br />Design Software:
<strong>Eagle Point</strong><br />

Completed: <strong>#DateFormat(enddate, "yyyy")#</strong>
</p>

<div id="narrative">#narrative#</p></div></cfoutput>





<CFQuery name="coolpicx" DATASOURCE="projects">
Select thumb
from completeImages right join CompleteTest on
completeImages.projectID= completeTest.projectID

<cfoutput query="coolpicx">
<Cfif #thumb# IS NOT "">
<img src="/crabweb1a/Images/CompleteProjectImages/completeThmbs/#thumb#"/>
</Cfif>
</cfoutput>

[Azadi]

<CFQuery name="coolpicx" DATASOURCE="projects">
Select thumb
from completeImages right join CompleteTest on
completeImages.projectID= completeTest.projectID
</CFQuery>

again, you are NOT restricting your query by projectID, thus it returns
ALL images of ALL projects.

add an appropriate WHERE clause to the above query, something like:

WHERE completeTest.projectID = <cfqueryparam cfsqltype="cf_sql_integer"
value="#some_variable_that_holds_selected_projectID#">

SIDE NOTES:

1) you do not need any JOINs, or even any other table, in the above
query if your completeImages table has a projectID field. this should
work just fine:
SELECT thumb
FROM completeImages
WHERE projectID = <cfqueryparam cfsqltype="cf_sql_integer"
value="#some_variable_that_holds_selected_projectID#">

2) the link to project details page on your page2 looks strange,
specifically this part of the query string:
&projectID=completeTests.projectID"

a) your query is named 'cool', not 'completeTests'
b) you are missing # around the variable

i think that part of your link should be:
&projectID=#cool.projectID#"

if i am right, than the query on your 3rd page should be:
SELECT thumb
FROM completeImages
WHERE projectID = <cfqueryparam cfsqltype="cf_sql_integer"
value="#url.projectID#">

hth

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[rockhiker]

It works !!! I changed the query to

SELECT thumb
FROM completeImages
WHERE projectID = <cfqueryparam cfsqltype="cf_sql_integer"
value="#url.projectID#">

and I changed the link to: &projectID=#cool.projectID#"


I am trying to dispaly an images unavailabe message if there are no images in
the database. pls see code.

<cfoutput query="coolpicx">
<cfif #thumb# EQ " ">

<img src="/crabweb1a/Images/CompleteProjectImages/completeThmbs/#thumb#"/>
<cfelse>
Images unavailable
</cfif>
</cfoutput>