Advanced search  

News:

cpg1.5.48 Security release - upgrade mandatory!
The Coppermine development team is releasing a security update for Coppermine in order to counter a recently discovered vulnerability. It is important that all users who run version cpg1.5.46 or older update to this latest version as soon as possible.
[more]

Pages: [1]   Go Down

Author Topic: Show alblist in order of most recent picture  (Read 10761 times)

0 Members and 1 Guest are viewing this topic.

philipmatarese

  • Contributor
  • Coppermine newbie
  • ***
  • Offline Offline
  • Gender: Male
  • Posts: 8
    • matarese.org - the pictures
Show alblist in order of most recent picture
« 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;
« Last Edit: September 11, 2006, 11:53:00 pm by Nibbler »
Logged

Nibbler

  • Guest
Re: Show alblist in order of most recent picture
« Reply #1 on: September 11, 2006, 11:51:14 pm »

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

philipmatarese

  • Contributor
  • Coppermine newbie
  • ***
  • Offline Offline
  • Gender: Male
  • Posts: 8
    • matarese.org - the pictures
Re: Show alblist in order of most recent picture
« Reply #2 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?
Logged

Nibbler

  • Guest
Re: Show alblist in order of most recent picture
« Reply #3 on: September 12, 2006, 12:02:48 am »

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

Langdon

  • Coppermine newbie
  • Offline Offline
  • Posts: 2
Re: Show alblist in order of most recent picture
« Reply #4 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)
Logged

Nibbler

  • Guest
Re: Show alblist in order of most recent picture
« Reply #5 on: December 27, 2006, 06:27:31 pm »

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

Langdon

  • Coppermine newbie
  • Offline Offline
  • Posts: 2
Re: Show alblist in order of most recent picture
« Reply #6 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.
Logged

thapame

  • Coppermine regular visitor
  • **
  • Offline Offline
  • Posts: 72
Re: Show alblist in order of most recent picture
« Reply #7 on: April 23, 2007, 11:19:44 am »

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.
Logged
Pages: [1]   Go Up
 

Page created in 0.019 seconds with 19 queries.