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:
$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:
$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;
What's going on with the GROUP BY? Grouping by aid should be sufficient.
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?
Seems to work ok both ways. Thankyou for your contribution.
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)
Enable debug mode and post the mysql error message you get when you replicate the error...
Thanks for reply..
Here the message goes:
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.
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:
$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:
$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.