Advanced search  

News:

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

Pages: 1 2 [3]   Go Down

Author Topic: More meta albums for cpg1.5.x  (Read 37685 times)

0 Members and 1 Guest are viewing this topic.

altenae

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 231
Re: More meta albums for cpg1.5.x
« Reply #40 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
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15369
Re: More meta albums for cpg1.5.x
« Reply #41 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.
Logged

altenae

  • Coppermine frequent poster
  • ***
  • Offline Offline
  • Posts: 231
Re: More meta albums for cpg1.5.x
« Reply #42 on: November 18, 2011, 09:25:10 pm »

That did the trick.

Thanks.

Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15369
Re: More meta albums for cpg1.5.x
« Reply #43 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).
Logged

lurkalot

  • Administrator
  • Coppermine addict
  • *****
  • Country: gb
  • Offline Offline
  • Gender: Male
  • Posts: 824
  • +Tinyportal Support team.
Re: More meta albums for cpg1.5.x
« Reply #44 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
Logged
Running SMF 2.0.13  / Tinyportal 1.2, bridged with Coppermine 1.5.34, plus cpmfetch 2.0.0

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15369
Re: More meta albums for cpg1.5.x
« Reply #45 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 ;)
Logged

deadele

  • Coppermine newbie
  • Offline Offline
  • Posts: 8
Re: More meta albums for cpg1.5.x
« Reply #46 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?
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15369
Re: More meta albums for cpg1.5.x
« Reply #47 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.
Logged

KyleClarkeNZ

  • Coppermine newbie
  • Country: nz
  • Offline Offline
  • Gender: Male
  • Posts: 17
    • NZ Game Collectors
Re: More meta albums for cpg1.5.x
« Reply #48 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.
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15369
Re: More meta albums for cpg1.5.x
« Reply #49 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;
Logged

KyleClarkeNZ

  • Coppermine newbie
  • Country: nz
  • Offline Offline
  • Gender: Male
  • Posts: 17
    • NZ Game Collectors
Re: More meta albums for cpg1.5.x
« Reply #50 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
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15369
Re: More meta albums for cpg1.5.x
« Reply #51 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.
Logged

jakkto

  • Coppermine newbie
  • Offline Offline
  • Posts: 4
Re: More meta albums for cpg1.5.x
« Reply #52 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. :)
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15369
Re: More meta albums for cpg1.5.x
« Reply #53 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:
  • 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?
Logged

Kimber

  • Coppermine newbie
  • Offline Offline
  • Posts: 3
Re: More meta albums for cpg1.5.x
« Reply #54 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?
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15369
Re: More meta albums for cpg1.5.x
« Reply #55 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.
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15369
Re: More meta albums for cpg1.5.x
« Reply #56 on: January 22, 2014, 04:07:24 pm »

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

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15369
Re: More meta albums for cpg1.5.x
« Reply #57 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;
Logged

plancast

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 25
Re: More meta albums for cpg1.5.x
« Reply #58 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
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15369
Re: More meta albums for cpg1.5.x
« Reply #59 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.
Logged
Pages: 1 2 [3]   Go Up
 

Page created in 0.026 seconds with 20 queries.