forum.coppermine-gallery.net

No Support => Modifications/Add-Ons/Hacks => Mods: Miscellaneous => Topic started by: philipmatarese on September 11, 2006, 11:34:34 pm

Title: Show alblist in order of most recent picture
Post by: philipmatarese on September 11, 2006, 11:34:34 pm

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]

    $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]

    $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;

Title: Re: Show alblist in order of most recent picture
Post by: Nibbler on September 11, 2006, 11:51:14 pm

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

Title: Re: Show alblist in order of most recent picture
Post by: philipmatarese on September 11, 2006, 11:54:24 pm

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?

Title: Re: Show alblist in order of most recent picture
Post by: Nibbler on September 12, 2006, 12:02:48 am

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

Title: Re: Show alblist in order of most recent picture
Post by: Langdon on December 27, 2006, 04:44:33 pm

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)

Title: Re: Show alblist in order of most recent picture
Post by: Nibbler on December 27, 2006, 06:27:31 pm

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

Title: Re: Show alblist in order of most recent picture
Post by: Langdon on December 27, 2006, 08:07:15 pm

Thanks for reply..

Here the message goes:

Code: [Select]

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.

Title: Re: Show alblist in order of most recent picture
Post by: thapame on April 23, 2007, 11:19:44 am

Quote from: philipmatarese on September 11, 2006, 11:34:34 pm

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]

    $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]

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