Welcome to WebmasterWorld Guest from 3.84.139.101

Forum Moderators: ocean10000

Message Too Old, No Replies

SQL/ classic asp issue

SQL, joins, asp, photos

     
1:42 pm on Apr 27, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:Sept 11, 2009
posts: 108
votes: 0



Hi All,
I am trying to put together a number of photo albums. The photo albums show up as images on a page using a repeat region. When you click on the photo album image that will show the photos in the album.

I have set up 2 tables Photos and PhotoAlbums. Each record in the Photos table has an PHotoAlbumID to link it to the photo album.

What I want is to show the first image in the album for each photo album cover. So if the first imageID is 6 then image 6 should show up on the photo albums page as the Album cover if you understand what I mean?

This is the SQL statement I have at the moment but I know it won't do what I want:
"Select * FROM PhotoAlbums left join Photos on PhotoAlbums.AlbumID = Photos.AlbumID ORDER BY Photos.AlbumID asc"

I wonder can anyone enlighten me on what statement I could be putting here?
3:21 pm on Apr 27, 2011 (gmt 0)

New User

5+ Year Member

joined:Aug 21, 2009
posts: 15
votes: 0


Are you trying to grab just the first photo assigned to each album? If that's the case, you may have to loop through the AlbumID numbers in that table and pull each one separately.

Something like this might work in that case:

SELECT TOP 1 *
FROM Photos, PhotoAlbums
WHERE Photos.AlbumID = PhotoAlbums.AlbumID AND PhotoAlbums = 1
8:15 am on Apr 28, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:Sept 11, 2009
posts: 108
votes: 0


Thanks for the reply kb5nju.
I will try this and see what happens. Thanks again.
10:41 am on Apr 28, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:Sept 11, 2009
posts: 108
votes: 0


I keep getting this error:

Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters
1:35 pm on Apr 28, 2011 (gmt 0)

New User

5+ Year Member

joined:Aug 21, 2009
posts: 15
votes: 0


Yup - I forgot a part. Fix the WHERE clause to end like this:

AND PhotoAlbums.AlbumID = 1
1:43 pm on Apr 28, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:Sept 11, 2009
posts: 108
votes: 0


I should have seen that myself tbh :). Thanks for the help. I'll try it again.

Many thanks
3:43 pm on Apr 28, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:Sept 11, 2009
posts: 108
votes: 0


Hi again,
I have tested this select statement on my page "select * from PhotoAlbums left join Photos on PhotoAlbums.AlbumID = Photos.AlbumID ORDER BY Photos.picID desc" and it is working okay. There is only 1 problem with it. It is taking every picture in the "Photos" table and showing them as an Album cover but I am just looking for 1 image out of each album to be the album cover. Does anyone know how I can achieve this?

I didn't get any results for the above using this statement "SELECT TOP 1 *
FROM Photos, PhotoAlbums
WHERE Photos.AlbumID = PhotoAlbums.AlbumID AND PhotoAlbums = 1 " I'm afraid.
4:13 pm on Apr 28, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2637
votes: 5


you can simply add a limit clause to the end of the query

select * from PhotoAlbums left join Photos on PhotoAlbums.AlbumID = Photos.AlbumID ORDER BY Photos.picID desc limit 1
4:53 pm on Apr 28, 2011 (gmt 0)

New User

5+ Year Member

joined:Aug 21, 2009
posts: 15
votes: 0


"limit 1" - that's a new one on me. Thanks.
6:38 pm on Apr 28, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:Sept 11, 2009
posts: 108
votes: 0


I am using MsAccess database at the moment and "Limit 1" isn't working. I plan to migrate to MySQL but not until I develop the software properly first. Any other suggestions?
6:42 pm on Apr 28, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:Sept 11, 2009
posts: 108
votes: 0


Okay, I have this statement working "Select Top 1 * from PhotoAlbums left join Photos on PhotoAlbums.AlbumID = Photos.AlbumID ORDER BY Photos.picID desc" but it is only returning the first photo album and not the rest. Is there anything I can do to fix this?
7:02 pm on Apr 28, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2637
votes: 5


Try removing the TOP 1... that is telling it to only return 1
7:19 pm on Apr 28, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:Sept 11, 2009
posts: 108
votes: 0


Okay but if I do that it returns every photo as a photo album but not every album with an image from the photos table :s
7:37 pm on Apr 28, 2011 (gmt 0)

New User

5+ Year Member

joined:Aug 21, 2009
posts: 15
votes: 0


KRM - you will need to write a query in to pull out all the AlbumID records from that table, then loop through that list and pull the first picture out of each Album running the query you just got working for each number in the list.
7:47 pm on Apr 28, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Dec 15, 2003
posts:2637
votes: 5


kb5 is right. You need 2 queries and a loop

pseudo code looks like this:

sql = "SELECT * FROM Photos"

photo_data = sql_execute(sql)

for (photo in photo_data) {
cover_sql = "Select Top 1 * from PhotoAlbums where PhotoAlbums.AlbumID = " photo['AlbumID']

cover_data = sql_execute(cover_sql)

print cover_data[0][0]
}
1:48 pm on June 9, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:Apr 15, 2011
posts: 45
votes: 0


Could you do something like this?

SELECT * FROM
PhotoAlbums
INNER JOIN
(SELECT MIN(ImageId) AS ImageId,AlbumID
FROM Photos
GROUP BY AlbumId) FirstImages
ON
(FirstImages.AlbumId = PhotoAlbum.AlbumId)
INNER JOIN
Photos
ON
(FirstImages.ImageId = Photos.ImageId)