forum.coppermine-gallery.net

Support => cpg1.5 plugins => cpg1.5.x Support => cpg1.5 plugin contributions => Topic started by: Αndré on February 11, 2010, 03:34:53 pm

Title: More meta albums for cpg1.5.x
Post by: Αndré on February 11, 2010, 03:34:53 pm
This plugin adds new meta albums to your gallery.

Currently available meta albums:
Title: Re: More meta albums for cpg1.5.x
Post by: Timos-Welt on February 11, 2010, 04:22:08 pm
Great! May I ask for more meta albums? I have some ideas.
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on February 11, 2010, 05:03:16 pm
Sure :) That are only the meta albums I have in my gallery since some years :D
Title: Re: More meta albums for cpg1.5.x
Post by: Timos-Welt on February 11, 2010, 05:15:42 pm
Here are some ideas I would find very useful:
- meta album videos (all video file types)
- meta album images (all image file types)
- meta album files (all files that are not videos or images)
- meta album mp3s
- meta album portrait format images (width < height)
- meta album landscape format images (height < width)
Title: Re: More meta albums for cpg1.5.x
Post by: Phill Luckhurst on February 11, 2010, 06:55:57 pm
Good call Timo but how about a custom meta plugin. We have loads of different things meta albums could be created by such as "uploaded by" or even ones based on IPTC data (brand taken by, date, lens etc). Not sure if possible but might be usefull.
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on February 11, 2010, 07:53:02 pm
how about a custom meta plugin
Please elaborate. Isn't this a custom meta plugin ???

@Timo: I'll add your suggestions soon.

Maybe a config screen to enable/disable meta albums will be needed if the plugin grows. I plan to add config options for menu items anyway :)
Title: Re: More meta albums for cpg1.5.x
Post by: Phill Luckhurst on February 11, 2010, 08:05:03 pm
Andre,

Maybe I should have explained better. By custom meta albums I really mean the user could create premanent meta albums based on search results(ish). So he inputs a name for the album then gives the criteria for this album such as *.mov, *.flv, IPTC=Camera Manufacturer, taken by user x etc

Uses for example, a large photography website where each month a particular photographer could be showcased simply by creating a meta album of all the images they have uploaded. Or maybe they want to show off wide angle photography so could create a meta album of all wide angle shots (taken from lens data in IPTC). Maybe a car website wants to show off all images tagged Ford.

Again, not sure if it is technically possible but would be usefull.
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on February 11, 2010, 08:25:31 pm
That's more a 'search' feature imo. As I already mentioned somewhere, I created an advanced search for my website. You can call the 'result' with the appropriate parameters. Maybe I can port that to this (or another) plugin. Currently it's customized to my website and my needs.
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on February 12, 2010, 03:39:19 pm
Added new meta albums image, movie, audio, document, landscape, portrait in version 1.1.
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on February 23, 2010, 11:41:03 am
Added new meta album newalb in version 1.2. That version uses the recently introduced plugin hook theme_thumbnails_album_types.
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on March 12, 2010, 12:00:57 pm
Version 1.3 takes care of the new meta album validation (http://forum.coppermine-gallery.net/index.php/topic,63977.msg318086.html#msg318086).
Title: Re: More meta albums for cpg1.5.x
Post by: mipavluk on November 03, 2010, 05:12:10 pm
Hi André
in Most Voted Table, I get the Most Comment Title
could you please point me to where fix this?
thanks in advance
Marta

My gallery: http://elrefugiodeanimales.org.ar/concurso/index.php
Most Voted Table said: Los más comentados
Most Commented Table said: Los más comentados
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on November 03, 2010, 06:48:15 pm
Fixed in svn. Do you want to share your Spanish language file with us? If so, I'll include it to the next release.
Title: Re: More meta albums for cpg1.5.x
Post by: mipavluk on November 04, 2010, 12:33:19 am
svn? sorry, don't understand you...

attached is my spanish file, thanks
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on November 04, 2010, 09:28:26 am
Thanks. Version 1.4 attached to initial post. I edited your language file slightly (encoded in UTF-8 without BOM and replaced HTML entities accordingly).
Title: Re: More meta albums for cpg1.5.x
Post by: mipavluk on November 06, 2010, 12:42:37 am
ok, thank you!
but the mistake with most commented title in most voted table continue... at least in spanish version...
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on November 06, 2010, 10:45:27 am
the mistake with most commented title in most voted table continue
Cannot confirm. You have to replace all files of the plugin, not just the Spanish language file.
Title: Re: More meta albums for cpg1.5.x
Post by: mipavluk on November 06, 2010, 02:49:37 pm
I already do it, but the mistake is still there...
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on November 06, 2010, 03:13:23 pm
Works as expected for me:
http://elrefugiodeanimales.org.ar/concurso/thumbnails.php?lang=spanish&album=mostcom - Los más comentados
http://elrefugiodeanimales.org.ar/concurso/thumbnails.php?lang=spanish&album=mostvot - Los más votados
Title: Re: More meta albums for cpg1.5.x
Post by: mipavluk on November 06, 2010, 03:20:09 pm
yep, but the problem is the title of the table...

the table for most voted (Los más votados) still said: Most commented (Los más comentados)

here: http://elrefugiodeanimales.org.ar/concurso/thumbnails.php?lang=spanish&album=mostvot

and in my index: http://elrefugiodeanimales.org.ar/concurso/index.php
second table... with the star... should said: Los más votados (most voted) but said: Los más comentados (most commented) like the next one which is the really most commented
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on November 06, 2010, 04:15:26 pm
Sorry I was a little bit briefly and just had a look at the page title and not the title of the table. Seems that something went wrong while creating the package. Please try the updated package from the initial post.
Title: Re: More meta albums for cpg1.5.x
Post by: mipavluk on November 06, 2010, 07:36:21 pm
is perfect now!
thanks a lot!
 ;D
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on May 06, 2011, 05:03:12 pm
Version 1.5 attached to initial post. New meta albums 'panorama', 'randalb', 'randuseralb' and 'randpublicalb'.
Title: Re: More meta albums for cpg1.5.x
Post by: profili on August 22, 2011, 12:23:16 am
Something is not working properly here. For the meta album "Movie" it show only the first uploaded videos (in my case 4 videos) and not the new ones. My default order for the thumbnails is data descending.

Another problem is the fact that under the thumbnails is the album name, not the file name.

http://www.galsh.com/
http://www.galsh.com/pic-movie.html
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on August 22, 2011, 05:40:28 pm
For the meta album "Movie" it show only the first uploaded videos (in my case 4 videos) and not the new ones.
Seems to work as expected in my gallery and I also can see 6 videos in your gallery.


My default order for the thumbnails is data descending.
The current version disregards that setting. Maybe it will use it in a future version.


Another problem is the fact that under the thumbnails is the album name, not the file name.
Confirmed.
Title: Re: More meta albums for cpg1.5.x
Post by: profili on August 22, 2011, 10:44:03 pm
Quote
The current version disregards that setting. Maybe it will use it in a future version.

Can't you write a hack that would solve this?  :)
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on August 26, 2011, 08:38:26 pm
Can't you write a hack that would solve this?  :)
Sure, but I'm currently very busy and it's not on top of my to-do list, sorry.
Title: Re: More meta albums for cpg1.5.x
Post by: mayhem72 on October 25, 2011, 10:52:38 am
Hi thanks for this.

Is there a way to show the number of album views under the thumbnail when using 'newalb'? At the moment it is showing the number of views of the image used for the album's thumbnail.
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on October 25, 2011, 02:35:25 pm
Version 1.6 (attached to initial post) fixes the thumbnail titles issue and the number of views for the 'newalb' meta album.
Title: Re: More meta albums for cpg1.5.x
Post by: mayhem72 on October 26, 2011, 10:23:52 am
Thank you! Works great ;D
Title: Re: More meta albums for cpg1.5.x
Post by: scifiguy on November 14, 2011, 03:31:22 am
Can you provide an example of how I use the meta albums this plugin creates? The references I find the docs don't provide any detail.
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on November 14, 2011, 05:41:19 pm
You can use them exactly as the built-in meta albums of cpg1.5.x. That means you can add them to "The content of the main page (http://documentation.coppermine-gallery.net/en/configuration.htm#admin_album_list_content)" or access them directly via:
Quote
thumbnails.php?album=<meta album name>
Title: Re: More meta albums for cpg1.5.x
Post by: scifiguy on November 14, 2011, 11:08:41 pm
That's what I thought, but I kept getting critical errors so I thought I must be doing something wrong. I've tested it again and now realize that its only the randalb, randuseralb, and randpublicalb choices that give critical errors (database error) but the others work. I wanted to use randuseralb to pull some pics from user galleries to the home page.

My gallery (bridged with phpbb3) is at: http://speculativevision.com/art/index.php

Works: http://speculativevision.com/art/thumbnails.php?album=portrait

Fails: http://speculativevision.com/art/thumbnails.php?album=randuseralb

Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on November 15, 2011, 12:01:05 pm
Please enable debug mode for everyone.
Title: Re: More meta albums for cpg1.5.x
Post by: Hanna. on November 15, 2011, 05:24:51 pm
The link in the main post isnt working for me? :/
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on November 15, 2011, 06:50:53 pm
The link in the main post isnt working for me? :/
Works for me as expected. However, this plugin is also available in our plugin download section (http://sourceforge.net/projects/coppermine/files/Plugins/1.5.x/).
Title: Re: More meta albums for cpg1.5.x
Post by: scifiguy on November 15, 2011, 10:06:32 pm
Debug is on.
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on November 16, 2011, 10:58:12 am
Open codebase.php, find (right at the end)
Code: [Select]
        case 'randalb': // Random albums
        case 'randuseralb': // Random albums in user categories
        case 'randpublicalb': // Random albums in public categories
            $condition = array(
                'randalb' => '',
                'randuseralb' => 'AND a.category > '.FIRST_USER_CAT,
                'randpublicalb' => 'AND a.category < '.FIRST_USER_CAT
            );

            $album_name = cpg_fetch_icon('alb_mgr', 2)." ".$lang_plugin_more_meta_albums[$meta['album'].'_title'];
            if ($CURRENT_CAT_NAME) {
                $album_name .= " - $CURRENT_CAT_NAME";
            }

            $query = "SELECT COUNT(*)
                    FROM {$CONFIG['TABLE_PICTURES']} AS r
                    INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS a ON a.aid = r.aid
                    $RESTRICTEDWHERE
                    AND approved = 'YES'
                    {$condition[$meta['album']]}
                    GROUP BY r.aid
                    HAVING COUNT(r.pid) > 0
                    ORDER BY RAND()";
            $result = cpg_db_query($query);
            $count = mysql_num_rows($result);
            mysql_free_result($result);

            $query = "SELECT p.*
                    FROM {$CONFIG['TABLE_PICTURES']} AS r
                    INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS a ON a.aid = r.aid
                    $RESTRICTEDWHERE
                    AND approved = 'YES'
                    {$condition[$meta['album']]}
                    GROUP BY r.aid
                    HAVING COUNT(r.pid) > 0
                    ORDER BY RAND()
                    {$meta['limit']}";
            $result = cpg_db_query($query);
            $rowset = cpg_db_fetch_rowset($result);
            mysql_free_result($result);

            build_caption($rowset, array('ctime'));
            break;
and replace with
Code: [Select]
        case 'randalb': // Random albums
        case 'randuseralb': // Random albums in user categories
        case 'randpublicalb': // Random albums in public categories
            $condition = array(
                'randalb' => '',
                'randuseralb' => 'AND r.category > '.FIRST_USER_CAT,
                'randpublicalb' => 'AND r.category < '.FIRST_USER_CAT
            );

            $album_name = cpg_fetch_icon('alb_mgr', 2)." ".$lang_plugin_more_meta_albums[$meta['album'].'_title'];
            if ($CURRENT_CAT_NAME) {
                $album_name .= " - $CURRENT_CAT_NAME";
            }

            $query = "SELECT COUNT(*)
                    FROM {$CONFIG['TABLE_PICTURES']} AS p
                    INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS r ON r.aid = p.aid
                    $RESTRICTEDWHERE
                    AND approved = 'YES'
                    {$condition[$meta['album']]}
                    GROUP BY p.aid
                    HAVING COUNT(p.pid) > 0
                    ORDER BY RAND()";
            $result = cpg_db_query($query);
            $count = mysql_num_rows($result);
            mysql_free_result($result);

            $query = "SELECT p.*
                    FROM {$CONFIG['TABLE_PICTURES']} AS p
                    INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS r ON r.aid = p.aid
                    $RESTRICTEDWHERE
                    AND approved = 'YES'
                    {$condition[$meta['album']]}
                    GROUP BY p.aid
                    HAVING COUNT(p.pid) > 0
                    ORDER BY RAND()
                    {$meta['limit']}";
            $result = cpg_db_query($query);
            $rowset = cpg_db_fetch_rowset($result);
            mysql_free_result($result);

            build_caption($rowset, array('ctime'));
            break;
Title: Re: More meta albums for cpg1.5.x
Post by: scifiguy on November 16, 2011, 10:21:44 pm
Worked. Thank you :)
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on November 17, 2011, 03:35:46 pm
Version 1.7 attached to initial post.

Changelog:
Title: Re: More meta albums for cpg1.5.x
Post by: altenae on November 18, 2011, 07:13:03 pm
Hi Andre,

I am trying to sort the Video meta album so that the last uploaded video is the most on the left.

I see you haven sort this with ASC.
When I change this into DESC the thumbnails are ordered correctly.
However when clikcing on the thumbnail the wrong movie is displayed (like the ASC sort)

Check http://www.edwardvanaltena.org/gallery/index.php

Scroll down to Latest Video (changed this in the english.php)
Click on the thumbnails and you see what I mean.

I  changed the ASC into DESC in this query (case Movie)

$query = "SELECT p.* FROM {$CONFIG['TABLE_PICTURES']} AS p
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS r ON r.aid = p.aid
                $RESTRICTEDWHERE
                AND approved = 'YES'
                AND ($filetypes_sql)
                ORDER BY pid DESC
                {$meta['limit']}";
            $result = cpg_db_query($query);
            $rowset = cpg_db_fetch_rowset($result);
            mysql_free_result($result);

What did I do wrong.

Edward
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on November 18, 2011, 08:04:49 pm
You have to change
Code: [Select]
AND pid < $pid";to
Code: [Select]
AND pid > $pid";for that meta album in the mma_get_pic_pos function.
Title: Re: More meta albums for cpg1.5.x
Post by: altenae on November 18, 2011, 09:25:10 pm
That did the trick.

Thanks.

Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on November 23, 2011, 10:36:14 am
Fixed issue with recently added meta album 'toprateda' and improved code if there are no results in plugin version 1.8 (attached to initial post).
Title: Re: More meta albums for cpg1.5.x
Post by: lurkalot on November 23, 2011, 10:50:11 am
Thanks Andre.  ;) 

Can  I assume I'm using the file with  "and improved code if there are no results"  already, or should I replace the whole plugin for the new one? 

Rgrds

Mick
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on November 23, 2011, 11:12:02 am
Background information: lurkalot asked for the feature introduced by the 'toprateda' meta album in the dev only board. I decided to add it to this plugin and lurkalot reported a bug in the dev only board, where the fix has also been developed.

BTT: you're using a version where only the fix for the bug has been applied. I also added that code to all other meta albums, which will save you 1 database query per meta album, if no matching files where found. Upgrading to the latest official version is recommended ;)
Title: Re: More meta albums for cpg1.5.x
Post by: deadele on March 04, 2012, 01:05:53 am
thank you so much for this Andre. Been looking for a random album feature :D
will it be possible to have the title of the album shown under the thumbnail like lastalb?
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on March 05, 2012, 02:28:17 pm
Version 1.9 (attached to initial post) will display the album titles at the randalb* meta albums.
Title: Re: More meta albums for cpg1.5.x
Post by: KyleClarkeNZ on April 23, 2012, 04:27:07 am
Hi,

is there anyway to add to this, or modify this to include a keywords meta album? I'd like to display all the images with a specific keyword tag on the main page along with the lastup and random etc.

So imagine I had a keyword "For Sale" I'd like to be able to see all of these images on the header "For Sale" amongst the "Latest Files" and "Random Files" on the main page.
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on April 23, 2012, 08:43:55 am
Open codebase.php, find
Code: [Select]
        default:
            return $meta;
and above, add
Code: [Select]
        case 'forsale':
            $album_name = "For sale";
            if ($CURRENT_CAT_NAME) {
                $album_name .= " - $CURRENT_CAT_NAME";
            }

            $query = "SELECT pid FROM {$CONFIG['TABLE_PICTURES']} AS p
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS r ON r.aid = p.aid
                $RESTRICTEDWHERE
                AND approved = 'YES'
                AND keywords like '%test%'";
            $result = cpg_db_query($query);
            $count = mysql_num_rows($result);
            mysql_free_result($result);
            if (!$count) {
                $rowset = array();
                break;
            }

            $query = "SELECT p.* FROM {$CONFIG['TABLE_PICTURES']} AS p
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS r ON r.aid = p.aid
                $RESTRICTEDWHERE
                AND approved = 'YES'
                AND keywords like '%test%'
                ORDER BY pid ASC
                {$meta['limit']}";
            $result = cpg_db_query($query);
            $rowset = cpg_db_fetch_rowset($result);
            mysql_free_result($result);

            build_caption($rowset);
            break;
Title: Re: More meta albums for cpg1.5.x
Post by: KyleClarkeNZ on April 23, 2012, 10:56:55 am
wow, fantastically quick response. Thank you :)

seems pretty straight forward. But I can't get the darn thing to work. the only thing that strikes me as odd is there's a reference to %test% twice in your code. Is this correct, or is it from tests that you were running? I have already tried uninstalling and reinstalling after making the changes in your instructions.

codebase.php after i made your changes.

Code: [Select]
<?php
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
===
more stuff up herehad to truncate to get bellow 25000 character===
        case 
'randalb'// Random albums
        
case 'randuseralb'// Random albums in user categories
        
case 'randpublicalb'// Random albums in public categories
            
$condition = array(
                
'randalb' => '',
                
'randuseralb' => 'AND r.category > '.FIRST_USER_CAT,
                
'randpublicalb' => 'AND r.category < '.FIRST_USER_CAT
            
);

            
$album_name cpg_fetch_icon('alb_mgr'2)." ".$lang_plugin_more_meta_albums[$meta['album'].'_title'];
            if (
$CURRENT_CAT_NAME) {
                
$album_name .= " - $CURRENT_CAT_NAME";
            }

            
$query "SELECT COUNT(*)
                    FROM 
{$CONFIG['TABLE_PICTURES']} AS p
                    INNER JOIN 
{$CONFIG['TABLE_ALBUMS']} AS r ON r.aid = p.aid
                    
$RESTRICTEDWHERE
                    AND approved = 'YES'
                    
{$condition[$meta['album']]}
                    GROUP BY p.aid
                    HAVING COUNT(p.pid) > 0
                    ORDER BY RAND()"
;
            
$result cpg_db_query($query);
            
$count mysql_num_rows($result);
            
mysql_free_result($result);
            if (!
$count) {
                
$rowset = array();
                break;
            }

            
$query "SELECT p.*, r.title
                    FROM 
{$CONFIG['TABLE_PICTURES']} AS p
                    INNER JOIN 
{$CONFIG['TABLE_ALBUMS']} AS r ON r.aid = p.aid
                    
$RESTRICTEDWHERE
                    AND approved = 'YES'
                    
{$condition[$meta['album']]}
                    GROUP BY p.aid
                    HAVING COUNT(p.pid) > 0
                    ORDER BY RAND()
                    
{$meta['limit']}";
            
$result cpg_db_query($query);
            
$rowset cpg_db_fetch_rowset($result);
            
mysql_free_result($result);

            
build_caption($rowset, array('ctime'));
            break;
            
        case 
'forsale':
            
$album_name "For sale";
            if (
$CURRENT_CAT_NAME) {
                
$album_name .= " - $CURRENT_CAT_NAME";
            }

            
$query "SELECT pid FROM {$CONFIG['TABLE_PICTURES']} AS p 
                INNER JOIN 
{$CONFIG['TABLE_ALBUMS']} AS r ON r.aid = p.aid 
                
$RESTRICTEDWHERE
                AND approved = 'YES'
                AND keywords like '%test%'"
;
            
$result cpg_db_query($query);
            
$count mysql_num_rows($result);
            
mysql_free_result($result);
            if (!
$count) {
                
$rowset = array();
                break;
            }

            
$query "SELECT p.* FROM {$CONFIG['TABLE_PICTURES']} AS p 
                INNER JOIN 
{$CONFIG['TABLE_ALBUMS']} AS r ON r.aid = p.aid
                
$RESTRICTEDWHERE 
                AND approved = 'YES'
                AND keywords like '%test%'
                ORDER BY pid ASC 
                
{$meta['limit']}";
            
$result cpg_db_query($query);
            
$rowset cpg_db_fetch_rowset($result);
            
mysql_free_result($result);

            
build_caption($rowset);
            break;(
$rowset);
            break;

        default:
            return 
$meta;
    }
    
    
$meta['album_name'] = $album_name;
    
$meta['count'] = $count;
    
$meta['rowset'] = $rowset;

    return 
$meta;
}


?>

thanks again :)

Kyle
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on April 23, 2012, 11:31:42 am
Of course
Code: [Select]
%test%has to be
Code: [Select]
%For sale%or however your actual keyword looks like.
Title: Re: More meta albums for cpg1.5.x
Post by: jakkto on September 21, 2012, 01:28:12 pm
How can i change top rated list?
I want 4 lists:
- Top rated last 24 hour
- Top rated week
- Top rated month
- Top rated all time

If there is possible to make, please help me. :)
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on September 27, 2012, 11:54:25 am
jakkto, such meta albums are just possible if detailed voting stats are enabled, as Coppermine doesn't store vote timestamps if that option is disabled. The actual request is to add three new meta albums (toprated_day, toprated_week, toprated_month).

Example:
Which picture should be displayed at position 1?
Title: Re: More meta albums for cpg1.5.x
Post by: Kimber on December 09, 2013, 12:16:31 am
I'm receiving the critical database error and I've narrowed it down to "lastcommented".

http://media.george-clooney.us/
Coppermine 1.5.24


Here is the debug information:

Code: [Select]
While executing query 'SELECT p.*, c1.*, UNIX_TIMESTAMP(c1.msg_date) AS msg_date FROM cpg15x_comments AS c1
                LEFT JOIN cpg15x_comments AS c2 ON (c2.pid = c1.pid AND c2.msg_date > c1.msg_date)
                INNER JOIN cpg15x_pictures AS p ON p.pid = c1.pid
                INNER JOIN cpg15x_albums AS r ON r.aid = p.aid
                INNER JOIN cpg15x_categories AS c2 ON c2.cid = category WHERE (c2.lft BETWEEN 3 AND 10) AND (1 OR keywords like '%Kimber%' OR keywords like '%Kimber%')
                AND approved = 'YES'
                AND c1.approval = 'YES'
                AND c2.pid IS NULL
                ORDER BY msg_date DESC
                 LIMIT 0 ,6' in plugins/more_meta_albums/codebase.php on line 439

mySQL error: Not unique table/alias: 'c2'

This is what I have in my codebase.php

Code: [Select]
case 'lastcommented': // Last commented files
            $query = "SELECT msg_date FROM {$CONFIG['TABLE_COMMENTS']} WHERE pid = $pid ORDER BY msg_id DESC LIMIT 1";
            $result = cpg_db_query($query);
            $msg_date = mysql_result($result, 0);
            mysql_free_result($result);

            $query = "SELECT COUNT(*) FROM {$CONFIG['TABLE_COMMENTS']} AS c1
                LEFT JOIN {$CONFIG['TABLE_COMMENTS']} AS c2 ON (c2.pid = c1.pid AND c2.msg_date > c1.msg_date)
                INNER JOIN {$CONFIG['TABLE_PICTURES']} AS p ON p.pid = c1.pid
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS r ON r.aid = p.aid
                $RESTRICTEDWHERE
                AND approved = 'YES'
                AND c1.approval = 'YES'
                AND c2.pid IS NULL
                AND (c1.msg_date > '$msg_date'
                OR c1.msg_date = '$msg_date' AND c1.pid < $pid)";

                $result = cpg_db_query($query);

                list($pos) = mysql_fetch_row($result);
                mysql_free_result($result);
            return strval($pos);
            break;

Can anyone help me correct this?
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on December 09, 2013, 11:05:48 am
Open codebase.php, find
Code: [Select]
            $query = "SELECT p.*, c1.*, UNIX_TIMESTAMP(c1.msg_date) AS msg_date FROM {$CONFIG['TABLE_COMMENTS']} AS c1
                LEFT JOIN {$CONFIG['TABLE_COMMENTS']} AS c2 ON (c2.pid = c1.pid AND c2.msg_date > c1.msg_date)
                INNER JOIN {$CONFIG['TABLE_PICTURES']} AS p ON p.pid = c1.pid
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS r ON r.aid = p.aid
                $RESTRICTEDWHERE
                AND approved = 'YES'
                AND c1.approval = 'YES'
                AND c2.pid IS NULL
                ORDER BY msg_date DESC
                {$meta['limit']}";
and replace with
Code: [Select]
            $query = "SELECT p.*, com1.*, UNIX_TIMESTAMP(com1.msg_date) AS msg_date FROM {$CONFIG['TABLE_COMMENTS']} AS com1
                LEFT JOIN {$CONFIG['TABLE_COMMENTS']} AS com2 ON (com2.pid = com1.pid AND com2.msg_date > com1.msg_date)
                INNER JOIN {$CONFIG['TABLE_PICTURES']} AS p ON p.pid = com1.pid
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS r ON r.aid = p.aid
                $RESTRICTEDWHERE
                AND approved = 'YES'
                AND com1.approval = 'YES'
                AND com2.pid IS NULL
                ORDER BY msg_date DESC
                {$meta['limit']}";

Make sure to replace the correct query, as you posted the wrong one in your thread.
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on January 22, 2014, 04:07:24 pm
Version 1.10 (attached to initial post) fixes the database error.
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on June 24, 2014, 02:33:55 pm
Recently a user asked for some custom meta albums. This is probably also interesting for others, so I post the request and solution here:

Is there a way to make coppermine sort images by keywords the same way is sorting images by most viewed and top rated?

I want to add a custom menu with: Nature, Animals, Cars etc and one users click on Cars coppermine to show all images that has cars in the keywords from all the albums and categories including User galleries.

I know I can make Cars to link to http://allvip.us/gallery/thumbnails.php?album=search&keywords=on&search=cars but that looks ugly.I want the link to be sort.Something like http://allvip.us/gallery/thumbnails.php?album=cars

Inside the function mma_get_pic_pos, find
Code: [Select]
default:and above, add
Code: [Select]
        case 'cars':
        case 'nature':
        case 'animals':
            $query = "SELECT COUNT(*) FROM {$CONFIG['TABLE_PICTURES']} AS p
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS r ON r.aid = p.aid
                $RESTRICTEDWHERE
                AND approved = 'YES'
                AND CONCAT('{$CONFIG['keyword_separator']}', `keywords`, '{$CONFIG['keyword_separator']}') LIKE '%{$CONFIG['keyword_separator']}$album{$CONFIG['keyword_separator']}%'
                AND pid < $pid";

            $result = cpg_db_query($query);

            list($pos) = mysql_fetch_row($result);
            mysql_free_result($result);
            return strval($pos);
            break;


Inside the function mma_meta_album, find
Code: [Select]
default:and above, add
Code: [Select]
        case 'cars':
        case 'nature':
        case 'animals':
            $album_name = $meta['album'];
            if ($CURRENT_CAT_NAME) {
                $album_name .= " - $CURRENT_CAT_NAME";
            }

            $query = "SELECT pid FROM {$CONFIG['TABLE_PICTURES']} AS p
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS r ON r.aid = p.aid
                $RESTRICTEDWHERE
                AND approved = 'YES'
                AND CONCAT('{$CONFIG['keyword_separator']}', `keywords`, '{$CONFIG['keyword_separator']}') LIKE '%{$CONFIG['keyword_separator']}{$meta['album']}{$CONFIG['keyword_separator']}%'";
            $result = cpg_db_query($query);
            $count = mysql_num_rows($result);
            mysql_free_result($result);
            if (!$count) {
                $rowset = array();
                break;
            }

            $query = "SELECT p.* FROM {$CONFIG['TABLE_PICTURES']} AS p
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS r ON r.aid = p.aid
                $RESTRICTEDWHERE
                AND approved = 'YES'
                AND CONCAT('{$CONFIG['keyword_separator']}', `keywords`, '{$CONFIG['keyword_separator']}') LIKE '%{$CONFIG['keyword_separator']}{$meta['album']}{$CONFIG['keyword_separator']}%'
                ORDER BY pid ASC
                {$meta['limit']}";
            $result = cpg_db_query($query);
            $rowset = cpg_db_fetch_rowset($result);
            mysql_free_result($result);

            build_caption($rowset);
            break;
Title: Re: More meta albums for cpg1.5.x
Post by: plancast on February 24, 2015, 12:36:32 pm
Hi Andre,

Is there a way on the Toprateda to add a line in the $RESTRICTWHERE to only show pictures uploaded within the past 24 hours?  I tried adding a ctime line but it doesn't seem to work:

            $query = "SELECT p.pid, (p.votes * p.pic_rating / $max_rating_points * 10000) AS pic_rating FROM {$CONFIG['TABLE_PICTURES']} AS p
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS r ON r.aid = p.aid
                $RESTRICTEDWHERE
                AND approved = 'YES'
                AND p.votes >= {$CONFIG['min_votes_for_rating']}
                AND ctime > (NOW() - 24*60*60)
                ORDER BY pic_rating DESC, pid ASC";

Thanks,

Chris
Title: Re: More meta albums for cpg1.5.x
Post by: Αndré on June 02, 2015, 09:40:37 pm
plancast, you need to apply that change both to the functions mma_meta_album and mma_get_pic_pos.