More meta albums for cpg1.5.x

[altenae]

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

[Αndré]

You have to change

Code Select Expand

AND pid < $pid";
to

Code Select Expand

AND pid > $pid";
for that meta album in the mma_get_pic_pos function.

[altenae]

That did the trick.

Thanks.

[Αndré]

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

[lurkalot]

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

[Αndré]

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

[deadele]

thank you so much for this Andre. Been looking for a random album feature
will it be possible to have the title of the album shown under the thumbnail like lastalb?

[Αndré]

Version 1.9 (attached to initial post) will display the album titles at the randalb* meta albums.

[KyleClarkeNZ]

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.

[Αndré]

Open codebase.php, find

Code Select Expand

        default:
            return $meta;
and above, add

Code Select Expand

        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;

[KyleClarkeNZ]

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 Expand

<?php
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
===more stuff up here, had 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

[Αndré]

Of course

Code Select Expand

%test%
has to be

Code Select Expand

%For sale%
or however your actual keyword looks like.

[jakkto]

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.

[Αndré]

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:

• Picture 1 has a overall rate of 3/5 and received a vote "4" in the last 24 hours.
• Picture 2 has a overall rate of 4/5 and received a vote "3" in the last 24 hours.

Which picture should be displayed at position 1?

[Kimber]

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 Expand

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 Expand

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?

[Αndré]

Open codebase.php, find

Code Select Expand

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

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

[Αndré]

Version 1.10 (attached to initial post) fixes the database error.

[Αndré]

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 Expand

default:
and above, add

Code Select Expand

        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 Expand

default:
and above, add

Code Select Expand

        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;

[plancast]

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

[Αndré]

plancast, you need to apply that change both to the functions mma_meta_album and mma_get_pic_pos.