Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlconfused
Yak Posting Veteran
50 Posts |
 Posted - 2013-09-08 : 17:46:54
|
Hi...I've got a large MS SQL database with photo filenames. My ASP website displays thumbnails of the photos in a given gallery by taking the filename from the database and calling up the "thumbnail" filenames. For example table "photogallery"filename, owner, description===========================town.jpg, fred, my hometownbridge1.jpg, fred, an old bridge in townboat.jpg, fred, my boat at the dockcamp33.jpg, jane, my summer campWhen a member uploads an image, a thumbnail is created with a "small" appended to the filename so it's just a matter of displaying "smalltown.jpg", "smallbridge1.jpg", etc. with a link to the filename without the "small".Users are asking if they can custom sort the images so that they appear in a certain order. Currently I display the images in the order they appear from the database. There is no current field to possibly ORDER BY except for filename which would be alphabetical. I'm looking at a custom sort where they could rearrange the order of the images. This would involve displaying the gallery and using a drop down form fields beside each photo to have a number from 1-10 (for 10 pics) or 1-13 for 13 pics, etc. There they would sort the photos by number.From reading other posts I believe I'd need to add an extra column which would simply be a numeric value (eg. a column named "sortby") and then use the ORDER BY sortby.A few questions though going back to my original tablefilename, owner, description, sortbybridge1.jpg, fred, an old bridge in town, 1boat.jpg, fred, my boat at the dock, 2camp33.jpg, jane, my summer camp, 3- if Fred deletes boat.jpg is there a way to reorder the sort so that it doesn't go 1..3...4...5 (2 is missing)- if fred uploads more images to a gallery with 10 images already is there a way to tell what values to insert into 'sortby'. example: fred uploads "fishingtrip.jpg" to a gallery with 10 images. I don't know what value to put into sortby, presumably it should be 11 but how would one calculate the total?- once a user has changed the sort order through the form such as:filename, owner, description, sortbybridge1.jpg, fred, an old bridge in town, 3boat.jpg, fred, my boat at the dock, 2camp33.jpg, jane, my summer camp, 1Is there an efficient update command to do this or would I simply do a loop and go through all the files like this:update photogallery set sortby = 3 where filename = 'bridge1.jpg'update photogallery set sortby = 2 where filename = 'boat.jpg'Thanks for any help!  |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-09-09 : 02:25:00
|
UPDATE fSET SortBy = YakFROM (SELECT SortBy, ROW_NUMBER() OVER (ORDER BY [FileName]) AS Yak FROM dbo.Table1) AS f
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
 |
|
|
sqlconfused
Yak Posting Veteran
50 Posts |
Posted - 2013-09-10 : 03:31:45
|
| Thank you - but uhm what does this do?It looks to update the table column sortby using filenames... however I would like members to be able to change their order.eg: trip1.jpg (first)fountain.jpg (second)tower.jpg (third)not necessarily alphabetically |
|
|
|
|
|
|
|
|
Custom sorting rows