Getting a photo of inside an Album for icon

[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

[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

[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(){
    //    $this->db->where('user_id', $this->user_id);
            
        $sql = 'SELECT albums, photos.pfile_name MAX(photos.puploaded) as uploaded FROM albums as album LEFT JOIN photos as photo ON (photos.album_id = album.album_id) GROUP BY album.album_id';
            
        $this->db->query($sql);
            return $this->db->get($this->table_name);
    }

[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
Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MAX(photos.puploaded) as dateadded FROM albums as a LEFT JOIN photos as p ON (p.' at line 1

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

Hopefully if you find a few minutes more you could help me get this solved.


Thanks

[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';

[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?

[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.