SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Custom sorting rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlconfused
Starting Member

Canada
40 Posts

Posted - 09/08/2013 :  17:46:54  Show Profile  Reply with Quote
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 hometown
bridge1.jpg, fred, an old bridge in town
boat.jpg, fred, my boat at the dock
camp33.jpg, jane, my summer camp

When 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 table

filename, owner, description, sortby

bridge1.jpg, fred, an old bridge in town, 1
boat.jpg, fred, my boat at the dock, 2
camp33.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, sortby

bridge1.jpg, fred, an old bridge in town, 3
boat.jpg, fred, my boat at the dock, 2
camp33.jpg, jane, my summer camp, 1

Is 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!


Edited by - sqlconfused on 09/08/2013 17:52:23

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/09/2013 :  02:25:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
UPDATE f
SET SortBy = Yak
FROM (
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
Go to Top of Page

sqlconfused
Starting Member

Canada
40 Posts

Posted - 09/10/2013 :  03:31:45  Show Profile  Reply with Quote
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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000