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: User Gallery Alphabetic Tabbing and bridging  (Read 11661 times)

0 Members and 1 Guest are viewing this topic.

jonket

  • Translator
  • Coppermine regular visitor
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 64
User Gallery Alphabetic Tabbing and bridging
« on: November 09, 2012, 07:12:32 pm »

I am getting this error message.  Do not find it anywhere else in the forum.  Please help:

Code: [Select]
While executing query 'SELECT NULL FROM cpg15x_albums AS p  INNER JOIN cpg15x_pictures AS pics ON pics.aid = p.aid  INNER JOIN `stjarnai_phpbb`.phpbb_users AS u ON p.owner = u.user_id WHERE ( category > 10000 ) AND UPPER(username) LIKE 'B%' GROUP BY category;' in plugins/usergal_alphatabs/codebase.php(364) : eval()'d code on line 31

mySQL error: SELECT command denied to user 'stjar_cpg'@'localhost' for table 'phpbb_users'

It should not be looking for phpbb-users in stjar_cpg. It should be stjarnai_phpbb

Coppermine install: http://stjarna.is/joomla/myndasafn
Coppermine version: cpg1.5.20
Bridging app version: Phpbb3 in CPG 1.5.20


BridgeManager settings:
Bridge app URL:  http://stjarna.is/joomla/spjall     
Relative path to your bridge app's config file:  ../../joomla/spjall/
Cookie name or prefix:  phpbb3_stjarna
« Last Edit: November 09, 2012, 10:31:48 pm by Jeff Bailey »
Logged

Jeff Bailey

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 1322
  • Fishing relaxes me.
    • Bailey Family Co.
Logged
Thinking is the hardest work there is, which is probably the reason why so few engage in it. - Henry Ford

jonket

  • Translator
  • Coppermine regular visitor
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 64
Re: User Gallery Alphabetic Tabbing and bridging
« Reply #2 on: November 09, 2012, 10:29:52 pm »

Thanks, but this did not work.  Just wonder why this  plugin is looking in CPG database, not phpbb3 database.
Logged

Jeff Bailey

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 1322
  • Fishing relaxes me.
    • Bailey Family Co.
Re: User Gallery Alphabetic Tabbing and bridging
« Reply #3 on: November 09, 2012, 10:31:33 pm »

Oh I miss read this, I thought this was a briding problem. Let me look at the plugin.
Logged
Thinking is the hardest work there is, which is probably the reason why so few engage in it. - Henry Ford

Jeff Bailey

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 1322
  • Fishing relaxes me.
    • Bailey Family Co.
Re: User Gallery Alphabetic Tabbing and bridging
« Reply #4 on: November 09, 2012, 10:59:17 pm »

It's looking in the correct database `stjarnai_phpbb`.phpbb_users the problem is MySQL user permissions as far as I can tell.

I have no way to test this or even if I have the right idea of what the problem is but

In codebase.php
Find:
Code: [Select]
        // Get the total number of users with albums
        $sql  = "SELECT NULL ";
        $sql .= "FROM {$CONFIG[\'TABLE_ALBUMS\']} AS p ";
        $sql .= " INNER JOIN {$CONFIG[\'TABLE_PICTURES\']} AS pics ON pics.aid = p.aid ";
        $sql .= " INNER JOIN ".$this->usertable." AS u ON p.owner = u.{$f[\'user_id\']} ";
        $sql .= "WHERE ( category > " . FIRST_USER_CAT . " $forbidden) ";
        if ($l = $getLetter) $sql .= "AND UPPER({$f[\'username\']}) LIKE \'$l%\' ";
        $sql .= "GROUP BY category;";
        $result = cpg_db_query($sql);
        $user_count = mysql_num_rows($result);

replace with:
Code: [Select]
        // Get the total number of users with albums
        $sql  = "SELECT NULL ";
        $sql .= "FROM {$CONFIG[\'TABLE_ALBUMS\']} AS p ";
        $sql .= " INNER JOIN {$CONFIG[\'TABLE_PICTURES\']} AS pics ON pics.aid = p.aid ";
        $sql .= " INNER JOIN ".$this->usertable." AS u ON p.owner = u.{$this->field[\'user_id\']} ";
        $sql .= "WHERE ( category > " . FIRST_USER_CAT . " $forbidden) ";
        if ($l = $getLetter) $sql .= "AND UPPER({$this->field[\'user_id\']}) LIKE \'$l%\' ";
        $sql .= "GROUP BY category;";
        $result = cpg_db_query($sql);
        $user_count = mysql_num_rows($result);
Logged
Thinking is the hardest work there is, which is probably the reason why so few engage in it. - Henry Ford

jonket

  • Translator
  • Coppermine regular visitor
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 64
Re: User Gallery Alphabetic Tabbing and bridging
« Reply #5 on: November 09, 2012, 11:10:13 pm »

This did not help.  Want a user and password?
Logged

Jeff Bailey

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 1322
  • Fishing relaxes me.
    • Bailey Family Co.
Re: User Gallery Alphabetic Tabbing and bridging
« Reply #6 on: November 09, 2012, 11:15:40 pm »

PM sent
Logged
Thinking is the hardest work there is, which is probably the reason why so few engage in it. - Henry Ford

Jeff Bailey

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 1322
  • Fishing relaxes me.
    • Bailey Family Co.
Re: User Gallery Alphabetic Tabbing and bridging
« Reply #7 on: November 10, 2012, 12:07:12 am »

I replied to your email.

Could you run a version check and give me the revision numbers for:
  • udb_base.inc.php
  • functions.inc.php
  • search.inc.php
  • search.php
  • usermgr.php
Logged
Thinking is the hardest work there is, which is probably the reason why so few engage in it. - Henry Ford

jonket

  • Translator
  • Coppermine regular visitor
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 64
Re: User Gallery Alphabetic Tabbing and bridging
« Reply #8 on: November 10, 2012, 12:20:55 am »

I downloaded the files directed to.

They do not have revision numbers in version check.  But they are said to be newer and for cpg 1.5.21
Logged

Jeff Bailey

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 1322
  • Fishing relaxes me.
    • Bailey Family Co.
Re: User Gallery Alphabetic Tabbing and bridging
« Reply #9 on: November 10, 2012, 12:23:59 am »

I noticed you hadn't undone the code change from
replace with:
Code: [Select]
        // Get the total number of users with albums
        $sql  = "SELECT NULL ";
        $sql .= "FROM {$CONFIG[\'TABLE_ALBUMS\']} AS p ";
        $sql .= " INNER JOIN {$CONFIG[\'TABLE_PICTURES\']} AS pics ON pics.aid = p.aid ";
        $sql .= " INNER JOIN ".$this->usertable." AS u ON p.owner = u.{$this->field[\'user_id\']} ";
        $sql .= "WHERE ( category > " . FIRST_USER_CAT . " $forbidden) ";
        if ($l = $getLetter) $sql .= "AND UPPER({$this->field[\'user_id\']}) LIKE \'$l%\' ";
        $sql .= "GROUP BY category;";
        $result = cpg_db_query($sql);
        $user_count = mysql_num_rows($result);
you can revert back to the original.
Logged
Thinking is the hardest work there is, which is probably the reason why so few engage in it. - Henry Ford

jonket

  • Translator
  • Coppermine regular visitor
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 64
Re: User Gallery Alphabetic Tabbing and bridging
« Reply #10 on: November 10, 2012, 12:51:16 am »

I have reverted to original
Logged

Jeff Bailey

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 1322
  • Fishing relaxes me.
    • Bailey Family Co.
Re: User Gallery Alphabetic Tabbing and bridging
« Reply #11 on: November 10, 2012, 04:46:46 am »

Well the problem is you have a different mysql user for each table.

$this->can_join_tables becomes false because your db user names are not the same and your cpg db user does not have select permissions on your phpBB table.

I tried writing a workaround for this but I don't know enough about phpBB to create test accounts or enough about how this plugin works. For now I just commented out:
Code: [Select]
// Get the total number of users with albums
        /*$sql  = "SELECT NULL ";
        $sql .= "FROM {$CONFIG[\'TABLE_ALBUMS\']} AS p ";
        $sql .= " INNER JOIN {$CONFIG[\'TABLE_PICTURES\']} AS pics ON pics.aid = p.aid ";
        $sql .= " INNER JOIN ".$this->usertable." AS u ON p.owner = u.{$f[\'user_id\']} ";
        $sql .= "WHERE ( category > " . FIRST_USER_CAT . " $forbidden) ";
        if ($l = $getLetter) $sql .= "AND UPPER({$f[\'username\']}) LIKE \'$l%\' ";
        $sql .= "GROUP BY category;";
        $result = cpg_db_query($sql);
        $user_count = mysql_num_rows($result);*/
Code: [Select]
        $users_per_page = $CONFIG[\'thumbcols\'] * $CONFIG[\'thumbrows\'];
        //$totalPages = ceil($user_count / $users_per_page);
        //if ($PAGE > $totalPages) $PAGE = 1;
        $lower_limit = ($PAGE-1) * $users_per_page;
Code: [Select]
            // This query determines which users we need to collect usernames of - ie just those which have albums with pics
            // and are on the page we are looking at
            $sql  = "SELECT category - 10000 as user_id ";
            $sql .= "FROM {$CONFIG[\'TABLE_ALBUMS\']} AS a ";
            $sql .= "INNER JOIN {$CONFIG[\'TABLE_PICTURES\']} AS p ON p.aid = a.aid ";
            $sql .= "WHERE ((isnull(approved) or approved=\"YES\") ";
            $sql .= "AND category > " . FIRST_USER_CAT . ") $forbidden_with_icon GROUP BY category ";
            //$sql .= "LIMIT $lower_limit, $users_per_page ";
It works for now because you only have one user with a user gallery.

Easiest solution for you would be to grant permissions to your cpg db user for your phpBB tables, then uncomment the above code.
Logged
Thinking is the hardest work there is, which is probably the reason why so few engage in it. - Henry Ford

jonket

  • Translator
  • Coppermine regular visitor
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 64
Re: User Gallery Alphabetic Tabbing and bridging
« Reply #12 on: November 10, 2012, 01:03:59 pm »

Well, some hosting providers do not allow same user to different databases.

I just moved the cpg tables to the phpbb3 database.  But i would like to have it in diffferent databases.

I have reverted to the original codebase.php
Logged

Jeff Bailey

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 1322
  • Fishing relaxes me.
    • Bailey Family Co.
Re: User Gallery Alphabetic Tabbing and bridging
« Reply #13 on: November 13, 2012, 12:59:52 am »

Maybe something like this will work.

In codebase.php
Find
Code: [Select]
        // Get the total number of users with albums
        $sql  = "SELECT NULL ";
        $sql .= "FROM {$CONFIG[\'TABLE_ALBUMS\']} AS p ";
        $sql .= " INNER JOIN {$CONFIG[\'TABLE_PICTURES\']} AS pics ON pics.aid = p.aid ";
        $sql .= " INNER JOIN ".$this->usertable." AS u ON p.owner = u.{$f[\'user_id\']} ";
        $sql .= "WHERE ( category > " . FIRST_USER_CAT . " $forbidden) ";
        if ($l = $getLetter) $sql .= "AND UPPER({$f[\'username\']}) LIKE \'$l%\' ";
        $sql .= "GROUP BY category;";
        $result = cpg_db_query($sql);
        $user_count = mysql_num_rows($result);
Replace with
Code: [Select]
if ($this->can_join_tables){
// Get the total number of users with albums
$sql  = "SELECT NULL ";
$sql .= "FROM {$CONFIG[\'TABLE_ALBUMS\']} AS p ";
$sql .= " INNER JOIN {$CONFIG[\'TABLE_PICTURES\']} AS pics ON pics.aid = p.aid ";
$sql .= " INNER JOIN ".$this->usertable." AS u ON p.owner = u.{$f[\'user_id\']} ";
$sql .= "WHERE ( category > " . FIRST_USER_CAT . " $forbidden) ";
if ($l = $getLetter) $sql .= "AND UPPER({$f[\'username\']}) LIKE \'$l%\' ";
$sql .= "GROUP BY category;";
$result = cpg_db_query($sql);
$user_count = mysql_num_rows($result);
} else {
$sql = "SELECT DISTINCT owner FROM {$CONFIG[\'TABLE_ALBUMS\']}";
$result = cpg_db_query($sql);
$albumowners = mysql_fetch_array($result);
mysql_free_result($result);

$sql = "SELECT user_id FROM {$this->usertable}";
if ($l = $getLetter) $sql .= " WHERE UPPER({$f[\'username\']}) LIKE \'$l%\' ";
$result = cpg_db_query($sql, $this->link_id);
$users = mysql_fetch_array($result);
mysql_free_result($result);

$user_count = 1;
foreach ($users as $user) {
if (in_array($user, $albumowners)) {
$user_count ++;
}
}
}

Untested
Logged
Thinking is the hardest work there is, which is probably the reason why so few engage in it. - Henry Ford

jonket

  • Translator
  • Coppermine regular visitor
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 64
Re: User Gallery Alphabetic Tabbing and bridging
« Reply #14 on: November 15, 2012, 08:26:11 am »

This works now without errors. 

But now I am in trouble with users albums if the users names start with a special Icelandic character.
Logged

Jeff Bailey

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 1322
  • Fishing relaxes me.
    • Bailey Family Co.
Re: User Gallery Alphabetic Tabbing and bridging
« Reply #15 on: November 16, 2012, 02:43:59 am »

Did it work with those characters before?
Logged
Thinking is the hardest work there is, which is probably the reason why so few engage in it. - Henry Ford

jonket

  • Translator
  • Coppermine regular visitor
  • **
  • Offline Offline
  • Gender: Male
  • Posts: 64
Re: User Gallery Alphabetic Tabbing and bridging
« Reply #16 on: November 16, 2012, 08:54:47 am »

Yes. it did
Logged

Jeff Bailey

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: us
  • Offline Offline
  • Gender: Male
  • Posts: 1322
  • Fishing relaxes me.
    • Bailey Family Co.
Re: User Gallery Alphabetic Tabbing and bridging
« Reply #17 on: November 17, 2012, 05:31:54 am »

probably needs html_entity_decode() somewhere, I'll look around some tomorrow to see if thats the issue.
Logged
Thinking is the hardest work there is, which is probably the reason why so few engage in it. - Henry Ford

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: User Gallery Alphabetic Tabbing and bridging
« Reply #18 on: November 20, 2012, 10:00:51 am »

I just had a short look at Jeff's latest mod and your gallery. What I noticed:

1.
Code: [Select]
$user_count = 1;probably has to be
Code: [Select]
$user_count = 0;
2. Browse through the different letters. For every letter the first two albums are dupes, and they appear for several letters.

3. The user name, album and file count seems to be always the same for each user, e.g.
Quote
5
5 album(s)
5 file(s)


I haven't looked at the vanilla plugin yet, but I assume issue 2 and 3 doesn't occur there, right?
Logged
Pages: [1]   Go Up
 

Page created in 0.028 seconds with 21 queries.