Getting a photo of inside an Album for icon - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: Getting a photo of inside an Album for icon (/showthread.php?tid=25240) |
Getting a photo of inside an Album for icon - El Forum - 12-05-2009 [eluser]JamieBarton[/eluser] Hey guys, (Oh no...It's me again..!) I have been wondering how to do the following: I have a page for showing the users Albums, and friends Albums, I've got the Albums listing fine. However, what I would like to do for the thumbnail of the Album show a picture (either random (if you can help me do that..) or the latest photo added to that album). How can I do this? I don't think posting any code here will help - As it's just theory behind this, I'll try my best to code it myself then liaise back with you guys to see if it is correct. I'm thinking that it is a function inside of a foreach to grab the image, I just don't know how to go about this. Regards, Jamie Getting a photo of inside an Album for icon - El Forum - 12-05-2009 [eluser]JoostV[/eluser] When you fetch the albums from the database you should be able to grab a single picture from that album as well, in the same query. You should end up with an SQL similar to (from the top of my head) Code: $sql = 'SELECT a., p.image MAX(p.date_added) as dateadded FROM albums as a LEFT JOIN photos as p ON (p.albumid = a.id) GROUP BY a.id'; -LEFT JOIN sees to it that you fetch every album, even if there are no photos in it -GROUP BY a.id sees to it that you fetch only one record per album, assuming that a.id is your unique album id -MAX(p.date_added) as dateadded sees to it that you fetch the most recent photo Getting a photo of inside an Album for icon - El Forum - 12-05-2009 [eluser]JamieBarton[/eluser] Wow, what a genius. I haven't tried it out yet. My tables are: Albums: album_id album_name user_id (for who uploaded album) Photos: photo_id album_id name file_path uploaded I want to only show albums by that user ( user_id of the album). My model Code: function get_my_albums(){ Getting a photo of inside an Album for icon - El Forum - 12-05-2009 [eluser]JamieBarton[/eluser] I changed your query to Code: $sql = 'SELECT albums.*, photos.pfile_name MAX(photos.puploaded) as dateadded FROM albums as a LEFT JOIN photos as p ON (p.album_id = a.album_id) GROUP BY a.album_id'; I've got the following error: Code: A Database Error Occurred Hopefully if you find a few minutes more you could help me get this solved. Thanks Getting a photo of inside an Album for icon - El Forum - 12-05-2009 [eluser]JoostV[/eluser] You forgot to seperate fields pfile_name and MAX() with a comma Try this: Code: $sql = 'SELECT a.*, p.pfile_name, MAX(p.puploaded) as dateadded FROM albums as a LEFT JOIN photos as p ON (p.album_id = a.album_id) GROUP BY a.album_id'; Getting a photo of inside an Album for icon - El Forum - 12-05-2009 [eluser]JamieBarton[/eluser] Yeah I tried that earlier after a friend on msn said i didn't comma it out, then it said I didn't have a table called 'albums' which was odd. But I do. My other option is to create separate thumbnails for each uploaded images; but that could introduce more load time. Not sure..? What do you think would be best? Getting a photo of inside an Album for icon - El Forum - 12-06-2009 [eluser]JoostV[/eluser] You use 'SELECT albums.*' when you should be using 'a.*' => because later on in your query you reference table albums as a. I always create one or more thumbs when an image is uploaded, and I display these whenever I need a thumb. This way, the thumbs are already on your filesystem. Most of the times, I create - big image - medium image - thumb - square thumb, squared from the center I set the sizes in a config file, so that I can adjust them for every app. Creating thumbs on-the-fly is too much of a burden on your server's CPU. |