Albums and Album Thumbnail (Thumbnail of latest photo inside album)

[eluser]JamieBarton[/eluser]
Hi guys,

Looking for a fresh way to get a photo from a album and use it as the album cover when viewing the albums.

You can see how it is to look: http://grab.by/2ScL

This is made up via the following components:

Controller:

Code:

$data['all_albums'] = $this->m_albums->get_all_albums($config['per_page'], $this->uri->segment(3));

Model:

Code:

function get_all_albums($limit=null, $offset=null)
    {
        $this->db->where('albums.family_id', $this->family_id);
        $this->db->join('users', 'users.user_id = albums.user_id');
        $this->db->order_by('created_on', 'DESC');
        return $this->db->get($this->table_name, $limit, $offset);
    }

Helper:

Code:

function get_thumbnail($album_id)
{
    $CI =& get_instance();
    $CI->db->where('album_id', $album_id);
    $CI->db->limit(1);
    $CI->db->order_by('created_on', 'DESC');
    
    $query = $CI->db->get('photos');
    
    $result = $query->row();
    
    if(!empty($result)) {
        $output = base_url().'uploads/photos/albums/'.$album_id.'/'.$result->file_name;
    } else {
        $output = base_url().'images/no-photo.png';
    }
    
    return $output;
}

View:

Code:

<?php foreach($all_albums->result() as $album):?>
            <div class="item">
              <a >id?&gt;"><img class="polaroid" width="120" height="100">id);?&gt;" /></a>
              <p>
                &lt;?=anchor('albums/view/'.$album->id, character_limiter($album->name, 21))?&gt;
                <small>by &lt;?=$album->first_name?&gt; &lt;?=$album->last_name?&gt;</small>
              </p>
            </div>
    &lt;?php endforeach;?&gt;

However, this menthod is causing a lot of queries. I will have 12 albums shown by default, and more if the user sets in their preferences.

So my question is, is there a better way to do this? Using less queries?

I'm thinking just to query the photos table, and then group them by album id, and only show photos.

Albums are to be shown only where the family_id is equal to the current family_id (as shown in the method above).

Can somebody help me with what alternate way there is to do this, and maybe help with SQL as that is where I lack confidence and knowledge.

You can see the current amount of queries, just for 4 albums! http://grab.by/2ScW

[eluser]umefarooq[/eluser]
yes you can do it using sub query here is your query, sub query works on mysql 5.+ not on mysql 4,sub query will return on one record not multiple

Code:

function get_all_albums($limit=null, $offset=null)
    {
$this->db->selec('albums.*');
$this->db->select('(select photo from photos where album_id = album.id limit 1 order by created_on desc) as photo', FALSE);
$this->db->join('users', 'users.user_id = albums.user_id');
        $this->db->order_by('created_on', 'DESC');
        return $this->db->get($this->table_name, $limit, $offset);
}

[eluser]JamieBarton[/eluser]
Hey thanks for the quick reply.

It's thrown up an error:

Code:

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 'order by created_on desc) as photo FROM (`albums`) JOIN `users` ON `users`.`user' at line 1

SELECT `albums`.*, (select * from photos where album_id = album.id limit 1 order by created_on desc) as photo FROM (`albums`) JOIN `users` ON `users`.`user_id` = `albums`.`user_id` ORDER BY `photos`.`created_on` DESC LIMIT 12

Sorry for being a pain! haha

[eluser]umefarooq[/eluser]
[quote author="umefarooq" date="1267911205"]yes you can do it using sub query here is your query, sub query works on mysql 5.+ not on mysql 4,sub query will return on one record not multiple

Code:

function get_all_albums($limit=null, $offset=null)
    {
$this->db->selec('albums.*');
$this->db->select('(select photo from photos where album_id = album.id limit 1 order by created_on desc) as photo', FALSE);
$this->db->join('users', 'users.user_id = albums.user_id');
        $this->db->order_by('created_on', 'DESC');
        return $this->db->get($this->table_name, $limit, $offset);
}

[/quote]

yes error just because of i put limit before order by in sub query now run it will work.

Code:

$this->db->select('(select photo from photos where album_id = album.id order by created_on desc limit 1) as photo', FALSE);

[eluser]JamieBarton[/eluser]

Reference 'photo' not supported (forward reference in item list)

Bugger!

[eluser]umefarooq[/eluser]
i don't know what is your Photo table structure and which column you are using as photo name just put it there and may be photo is your table name use some different name like image or any thing.

[eluser]JamieBarton[/eluser]
Okie dokie. How would I now access the user's first_name and last_name like I was before?

I get 'Message: Undefined variable: user' when trying to do $album->first_name, as I could do previously with the JOIN.

Thanks for your help Much appreciated!!

[eluser]umefarooq[/eluser]
call your user data also in query

Code:

$this->db->selec('albums.*, users.*');

[eluser]JamieBarton[/eluser]
How do I get anything from the photo query, like the file_name of it?

Sorry for being dumb, I just am very stupid when it comes to SQL.

Code:

<a >id?&gt;"><img class="polaroid" width="120" height="100">id?&gt;/&lt;?=$photos->file_name?&gt;" /></a>

[eluser]JamieBarton[/eluser]
Fixed it.

Code:

mg class="polaroid" width="120" height="100" src="<?=base_url()?>uploads/photos/albums/<?=$album->id?>/<?=$album->photo?>" />

Thanks for all your help