Show alblist in order of most recent picture

[philipmatarese]

This mod will let you override the manual album list ordering, and order by most recent upload.  It's definitely a hack unless this style of ordering could be chosen from the category configuration screen.

This update is to index.php.
Find this code:

Code Select Expand

    $sql = 'SELECT a.aid, a.title, a.description, category, visibility, filepath, ' . 'filename, url_prefix, pwidth, pheight ' . 'FROM ' . $CONFIG['TABLE_ALBUMS'] . ' as a ' . 'LEFT JOIN ' . $CONFIG['TABLE_PICTURES'] . ' as p ' . 'ON a.thumb=p.pid ' . 'WHERE category=' . $cat . $album_filter . ' ORDER BY a.pos ' . $limit;
Change to this code:

Code Select Expand

    $sql = 'SELECT max(pm.ctime) max_ctime, a.aid, a.title, a.description, a.visibility, p.filepath, '.
           'p.filename, p.url_prefix, p.pwidth, p.pheight '.
           'FROM '.$CONFIG['TABLE_ALBUMS'].' as a '.
           'LEFT JOIN '.$CONFIG['TABLE_PICTURES'].' as p '.
           'ON a.thumb=p.pid '.
           'INNER JOIN '.$CONFIG['TABLE_PICTURES'].' as pm '.
           'ON a.aid=pm.aid '.
           'WHERE category='.$cat.$album_filter.' '.
           'GROUP BY a.aid, a.title, a.description, a.visibility, p.filepath, '.
           'p.filename, p.url_prefix, p.pwidth, p.pheight '.
           'ORDER BY max_ctime DESC '.
           $limit;

[Nibbler]

What's going on with the GROUP BY? Grouping by aid should be sufficient.

[philipmatarese]

At work I do a lot of SQL Server, which I'm pretty sure requires all non-aggregate select fields to be in the group by.

Does MySQL not require that?

[Nibbler]

Seems to work ok both ways. Thankyou for your contribution.

[Langdon]

After applying the code, everything works as wish except one situation: If there is an album created, and before any pics uploaded to that album, will be showing the following message:

Critical error 

There was an error while processing a database query


Can someone help me with this problem?
(v1.4.10)

[Nibbler]

Enable debug mode and post the mysql error message you get when you replicate the error...

[Langdon]

Thanks for reply..

Here the message goes:

Code Select Expand

While executing query "SELECT a.aid, count( p.pid )  AS pic_count, max( p.pid )  AS last_pid, max( p.ctime )  AS last_upload, a.keyword FROM cpg143_albums AS a  LEFT JOIN cpg143_pictures AS p ON a.aid = p.aid AND p.approved =  'YES' WHERE a.aid IN ()GROUP BY a.aid" on 0

mySQL error: 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 ')GROUP BY a.aid' at line 1


File: functions.inc.php - Line: 249


PS.  I'm using PHP 4.4.2 and MySQL 4.1.7-nt on a windows machine.

[thapame]

This mod will let you override the manual album list ordering, and order by most recent upload.  It's definitely a hack unless this style of ordering could be chosen from the category configuration screen.

This update is to index.php.
Find this code:

Code Select Expand

    $sql = 'SELECT a.aid, a.title, a.description, category, visibility, filepath, ' . 'filename, url_prefix, pwidth, pheight ' . 'FROM ' . $CONFIG['TABLE_ALBUMS'] . ' as a ' . 'LEFT JOIN ' . $CONFIG['TABLE_PICTURES'] . ' as p ' . 'ON a.thumb=p.pid ' . 'WHERE category=' . $cat . $album_filter . ' ORDER BY a.pos ' . $limit;
Change to this code:

Code Select Expand

    $sql = 'SELECT max(pm.ctime) max_ctime, a.aid, a.title, a.description, a.visibility, p.filepath, '.
           'p.filename, p.url_prefix, p.pwidth, p.pheight '.
           'FROM '.$CONFIG['TABLE_ALBUMS'].' as a '.
           'LEFT JOIN '.$CONFIG['TABLE_PICTURES'].' as p '.
           'ON a.thumb=p.pid '.
           'INNER JOIN '.$CONFIG['TABLE_PICTURES'].' as pm '.
           'ON a.aid=pm.aid '.
           'WHERE category='.$cat.$album_filter.' '.
           'GROUP BY a.aid, a.title, a.description, a.visibility, p.filepath, '.
           'p.filename, p.url_prefix, p.pwidth, p.pheight '.
           'ORDER BY max_ctime DESC '.
           $limit;

this works perfectly inside Home > Albums but not in Index page.
how can i apply same on the index listing.