SQL selecting distinct items by date?
More on SQL-topic than CF (MS SQL), but hopefully is an easy one. I have items with a datetime field that stores when the items are added to the DB. There is a pivot table that links items to categories. I'm trying to pull out the top 5 unique categories with the newest-added items. This is what I'm trying to do even though the syntax doesn't work: SELECT TOP 5 DISTINCT pivot.CategoryID, Category.Name, Item.DateAdded FROM Category INNER JOIN pivot ON Category.CategoryID = pivot.CategoryID INNER JOIN Item ON pivot.ItemID = Item.ItemID ORDER BY Item.DateAdded DESC I can get close, but the DateAdded fields are always unique (sometimes only seconds apart, but unique) so no matter what I do it always thinks the result records are unique and won't give me unique CategoryIDs. Ideas? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342178 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL selecting distinct items by date?
Here's what I ended up with SELECT distinct TOP 5 pc.nCategoryID, c.sCategory, YEAR(p.dPartDate) AS theyear, MONTH(p.dPartDate) AS themonth, DAY(p.dPartDate) AS theday FROM Category c INNER JOIN PartCat pc ON pc.nCategoryID=c.nCategoryID inner join part p on pc.npartid=p.npartid order by theyear DESC, themonth desc, theday desc Adding any part-specific fields to the select list results in duplicate CategoryIDs, and I don't fully understand why the DISTINCT can't remain exclusive to the CategoryID field, but so far I think it'll do what's needed. The results can be looped over to get more detail which means subqueries, but it functions. Combining TOP and DISTINCT with GROUP/ORDER remains somewhat uncertain... -Original Message- From: John M Bliss [mailto:bliss.j...@gmail.com] Sent: Sunday, February 13, 2011 10:55 AM To: cf-talk Subject: Re: SQL selecting distinct items by date? SELECT TOP 5 DISTINCT pivot.CategoryID, Category.Name, YEAR(Item.DateAdded) AS theyear, MONTH(Item.DateAdded) AS themonth, DAY(Item.DateAdded) AS theday On Sun, Feb 13, 2011 at 12:51 PM, wabba must...@wabba.net wrote: More on SQL-topic than CF (MS SQL), but hopefully is an easy one. I have items with a datetime field that stores when the items are added to the DB. There is a pivot table that links items to categories. I'm trying to pull out the top 5 unique categories with the newest-added items. This is what I'm trying to do even though the syntax doesn't work: SELECT TOP 5 DISTINCT pivot.CategoryID, Category.Name, Item.DateAdded FROM Category INNER JOIN pivot ON Category.CategoryID = pivot.CategoryID INNER JOIN Item ON pivot.ItemID = Item.ItemID ORDER BY Item.DateAdded DESC I can get close, but the DateAdded fields are always unique (sometimes only seconds apart, but unique) so no matter what I do it always thinks the result records are unique and won't give me unique CategoryIDs. Ideas? ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342181 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Watermarking images
Anyone have a good tool for watermarking images either as they are displayed to the client, or on the backside as they are uploaded? Working on a parts database of 30k+ items and the part images are updated frequently. We regularly receive images discs or flash drives from manufacturers which we then just dump over the top of the old images, so doing the watermarking on the fly ensures it's always displayed without having to do the entire thing over. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341829 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Watermarking images
Yep...Will look. Any ideas on what sort of processing impact that puts on the server when done in real time, or if it's worth worrying about? -Original Message- From: Steve Milburn [mailto:scmilb...@gmail.com] Sent: Thursday, February 03, 2011 8:29 AM To: cf-talk Subject: Re: Watermarking images If you are running a version of CF that supports cfimage, have a look at this http://www.bennadel.com/blog/775-Learning-ColdFusion-8-CFImage-Part-III-Wate rmarks-And-Transparency.htm On Thu, Feb 3, 2011 at 11:14 AM, wabba must...@wabba.net wrote: Anyone have a good tool for watermarking images either as they are displayed to the client, or on the backside as they are uploaded? Working on a parts database of 30k+ items and the part images are updated frequently. We regularly receive images discs or flash drives from manufacturers which we then just dump over the top of the old images, so doing the watermarking on the fly ensures it's always displayed without having to do the entire thing over. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341832 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Watermarking images
Thanks for the ideas, a few seconds per display is definitely unacceptable with any sort of volume. Gets tricky when you have hundreds to update at a time and obviously can't run images through again which have already been watermarked. Many of the images are uploaded/updated via the web in which case it can easily be handled at that time. When batches are done (which is often times hundreds or thousands, and with no way to tell which are new and which are just duplicate copies of what already exists, particularly after the old ones are watermarked/modified...) it might be smart to just setup a temp directory with a script that could either be on a schedule or manually invoked to watermark and move. Keeping a second folder of clean images offline to pull form would be fine, anyone have any suggestions on a good command line utility? -Original Message- From: Bobby Hartsfield [mailto:bo...@acoderslife.com] Sent: Thursday, February 03, 2011 9:29 AM To: cf-talk Subject: RE: Watermarking images This can easily be done with cfimage. I just wanted to offer my experience with doing it on the fly. If you hit my site, in my signature, you will see a watermarked mast head image. If you refresh, you will get another. My super cool plan was to have the watermark added on each request. It worked quickly enough that it was no problem at all... however, the image had some very strange caching issues on the browser side... I tried everything I could think of to get around it but eventually ended up watermarking the images and saving the watermarked file for display. When a new image is added to a directory, I can click a button to loop oever them, watermark them and move them to the directory that they will be displayed from. Ray Camden offered a good idea for a gateway that would watch the diectory and handle the watermakrs without any interaction... But my suggestion is to, somehow, watermark the images permanently and use those for display. You can always keep an original copy and watermark them again later (if your watermark changes or something). But the headaches of doing it on the fly became too much to worry with and I ended up saving watermarked versions instead. You can see some code and more detail here: http://acoderslife.com/index.cfm/blog/Mast-head-image-bug The code there is what I was using to watermark on the fly. .:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: wabba [mailto:must...@wabba.net] Sent: Thursday, February 03, 2011 11:15 AM To: cf-talk Subject: Watermarking images Anyone have a good tool for watermarking images either as they are displayed to the client, or on the backside as they are uploaded? Working on a parts database of 30k+ items and the part images are updated frequently. We regularly receive images discs or flash drives from manufacturers which we then just dump over the top of the old images, so doing the watermarking on the fly ensures it's always displayed without having to do the entire thing over. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341858 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
nested SQL update statement to replace CF script
Hi all, read a lot, post rarely. Anyway, try to keep this simple as possible - I have a database of items stored within nested categories using a pivot table to establish parent/childs for the categories (the can virtually nest indefinitely, one child cat can be under multiple parents, etc). There are roughly 30,000 items within 3,000 categories. There are a handful of top level categories. One of the top level categories gets published to an external site regularly, while the others don't. I use a bit flag on the items to determine which items get published and which don't, so that only the items we want published get transferred. Currently I use a nested CF custom tag to set the publish flags - I feed it a top level categoryID, it sets Publish=1 on the items in that category, then calls itself in a loop with all of the CategoryID where the ParentCatID=CategoryID. Something like this: (cf_pushlishtree custom tag): cfquery Update item SET Publish=1 WHERE CategoryID=CurrentCat /cfquery cfquery name=getsubcats Select CategoryID FROM CatPivot WHERE ParentCatID=CurrentCat /cfquery cfloop query=getsubcats cf_publishtree CurrentCat=#CurrentCat# /cfloop ...It works fine but is slow and will timeout before it finishes, and I can't restart it where it leaves off. I would think this could be done right in MS SQL by having a nested function or stored procedure that can do the update and then loop itself from the select, but I can't figure out how - I don't think an UPDATE is allowed in a function, and a stored procedure can't be called from a select statement. I considered adding a TopCategoryID to the item or category table as it would make publishing very simple, but we can't as categories can exist anywhere in the category structure, often under multiple parents simultaneously. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341608 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Converting JPG/etc to ICO
If a standalone works, IcoFX is small/fast -Original Message- From: Rick Root [mailto:rick.r...@gmail.com] Sent: Saturday, January 08, 2011 5:02 PM To: cf-talk Subject: Converting JPG/etc to ICO Hey folks, I'm looking for a tool that can convert JPG, GIF, and other images to true .ICO files. We currently use cfx_imagecr3 for most of our image conversion (it works better than cfimage) but neither actually support writing of ICO files. Rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340595 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: CF 9 Hosting
Also been using hostek for years and always been very happy, never had the downtime experienced by others. Their support has always been very quick, and 90% of the time it's my problem anyway :P ... On a reseller package with some other options, spending a bunch more than $5/month, but still very cost effective. -Original Message- From: Paul Alkema [mailto:paulalkemadesi...@gmail.com] Sent: Thursday, May 20, 2010 8:21 AM To: cf-talk Subject: RE: CF 9 Hosting I've been using hostek for awhile and really like it. http://hostek.com/ One of the best features I think is that you can run cf9 applications, asp.net applications and php applications all from 1 hosting package which is awesome since I develop in all of those languages. Also they're CF package is only $5/month which is awesome. One down side I think is the hosting control panel isn't all that great, but it works well and does that I want. Regards, Paul Alkema http://paulalkema.com/ -Original Message- From: James Holmes [mailto:james.hol...@gmail.com] Sent: Thursday, May 20, 2010 11:10 AM To: cf-talk Subject: Re: CF 9 Hosting http://www.viviotech.net/ mxAjax / CFAjax docs and other useful articles: http://www.bifrost.com.au/blog/ On 20 May 2010 06:10, Kelly Matthews ke...@webdiva.org wrote: Anyone know any good, reasonable CF 9 hosting companies w/ mysql. Thanks! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:333897 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: null500 on nested custom tag
Lolcat :P Good thought about logging to an external file. On SQL 2k. The depth is unknown, it is usually 2-6 levels but theoretically could go way down. A fixed DB structure would make this a million times easier but also be virtually worthless for the application. And no, the cfif nParentCatID LT 1 shouldn't error out because it's with a loop over the query its referencing, so if it returns no records it's never run and just returns but up to the last nesting. -Original Message- From: Brad Wood [mailto:b...@bradwood.com] Sent: Thursday, March 11, 2010 9:33 PM To: cf-talk Subject: Re: null500 on nested custom tag You should try a variable called lolcat. :) Seriously though, it sounds like an endless loop to me. If you check your server's coldfusion-out log you will probably see an out of memory exception. Put in some external logging to a text file or something to follow what is happening to your variables. Or use a counter in session to keep yourself from recursing more than 10 times so you can output debugging info in your page. Without really digging in to your code I can't really say where the problem is. It sounds like your issue is pretty straight forward-- you have a series of categories which are arranged in a hierarchy via your pivot table. Given a leaf node of your tree, you wish to climb until you reach the root node. I guess you're trying to make sure that none of your categories are orphaned with no active parents? Honestly, it might be a little simpler to write this using iteration, but don't let that keep you from recursive tags if that's what you like. As to whether or not you can do it in a single SQL statement-- maybe. If you are on SQL Server 2005 or later CTE's (common table expressions) allow recursion but I don't know that it would be any simpler. If your hierarchy of categories always has a known depth, you can just keep joining to your pivot table that many times. Other than that, you are out of luck using an adjacency list model. Nested sets are much better for calculating ancestors and descendants. One question: if your query returns no records, won't the line cfif nParentCatID LT 1 error trying to convert to a number? ~Brad - Original Message - From: Me Too must...@wabba.net To: cf-talk cf-talk@houseoffusion.com Sent: Thursday, March 11, 2010 10:31 PM Subject: null500 on nested custom tag This is driving me nuts. Pretty simple, I have a little custom tag that runs through a pivot table trying to reach the bottom (categoryID=0) of a category tree by way of active (bActive=1) categories. The logic is: ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331665 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
