Hey Dave,
The root of our differences here is that we're designing the
application differently - it affects both the upload and the insert,
and the same logic I used in my insert can (and, imho, should) be used
in the upload portion.
On a really low level, your design has a table that looks like this:
tblProductImages
-------------------------
ProductId
ImageFileName1
ImageFileName2
ImageFileName3
If I understand correctly, you'd do this to handle the upload:
<cffile action="upload" formfield="ImageFileName1"...>
<cfset ImageFileName1 = cffile.serverFile>
<!--- do it again --->
<cffile action="upload" formfield="ImageFileName2"...>
<cfset ImageFileName1 = cffile.serverFile>
<!--- do it...again --->
<cffile action="upload" formfield="ImageFileName3"...>
<cfset ImageFileName3 = cffile.serverFile>
And this for your insert:
<cfquery...>
INSERT INTO tblProductImages (ImageFileName1, ImageFileName2, ImageFileName3)
VALUES (...)
</cfquery>
And if you needed all image file names:
<cfquery name...>
SELECT ImageFileName1, ImageFileName2, ImageFileName3
FROM tblProductImages
WHERE productId = ....
</cfquery>
What Scott means by this being a "static" design is that the number of
images per product supported by your application is a static value,
unable to be changed without changing code. Why trap yourself like
this? One of the reasons relational databases _exist_ is to avoid
situations like this.
This is a design that would be more of a best practice:
tblProductImages
-------------------------
ProductId
ImageFileName
Then, in your app, have a configuration variable that stores how many
images are allowed per product. Let's say that's stored in a variable
called, well, "maxProductImages".
First, we put our upload and insert into one loop:
<cfloop from="1" to="#maxProductImages#" index="i">
<cffile action="upload" formfield="ImageFileName#i#"...>
<cfquery...>
INSERT INTO tblProductImages (ImageFileName)
VALUES ('#cffile.serverfile#')
</cfquery>
</cfloop>
Much more streamlined, and you can just change maxProductImages
instead copying/pasting code, introducting typos, etc.
And if you needed all image file names:
<cfquery name...>
SELECT ImageFileName
FROM tblProductImages
WHERE productId...
</cfquery>
Don't let your design box you in. If you're repeating code, there's
likely to be a better way to do it - try to find it! Numbered
fieldnames like "ImageFile1, ImageFile2, ImageFile3" or
"FavoriteUrl[1-17]" should set off warning klaxons.
Finding better design now also makes the future a lot less painful.
If your client suddenly wanted to choose a "preferred" image to show
on the product's main page, you'd have a few different options.
One would be to add a new column to tblProductImages that says which
other column is preferred. Yuck - you'd have to do something like two
queries or a subquery to find out what the preferred column name was.
Another would be to created a tblPreferredProductImages that kept
track of which column currently kept track of which image is preferred
for a given product. Again, selects would get ugly and costly.
The design I suggest, and that I think most developers would prefer,
make it easy and elegant. You'd just add a "Preferred" column to
tblProductImages that was a simple bit flag, and do "SELECT
ImageFileName FROM tblProductImages WHERE productId = #productId# AND
Preferred = 1". It's elegant, it's quick, and doesn't affect our
existing design or queries in the slightest. And yes, I skipped using
cfqueryparam to avoid monkeying up the example.
Have a good night, I've finished my beer now.
-Joe
--
For Tabs, Trees, and more, use the jComponents:
http://clearsoftware.net/client/jComponents.cfm
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Purchase Contribute 3 from House of Fusion, a Macromedia Authorized Affiliate
and support the CF community.
http://www.houseoffusion.com/banners/view.cfm?bannerid=53
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:190183
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
