User Gallery Alphabetic Tabbing and bridging

[jonket]

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

Code Select Expand

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

[Jeff Bailey]

Try this update:
http://coppermine.svn.sourceforge.net/viewvc/coppermine?view=revision&revision=8391

[jonket]

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

[Jeff Bailey]

Oh I miss read this, I thought this was a briding problem. Let me look at the plugin.

[Jeff Bailey]

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 Expand


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


        // 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);


[jonket]

This did not help.  Want a user and password?

[Jeff Bailey]

PM sent

[Jeff Bailey]

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

[jonket]

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

[Jeff Bailey]

I noticed you hadn't undone the code change from

replace with:

Code Select Expand


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

[jonket]

I have reverted to original

[Jeff Bailey]

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 Expand


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


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


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

[jonket]

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

[Jeff Bailey]

Maybe something like this will work.

In codebase.php
Find

Code Select Expand


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


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

[jonket]

This works now without errors. 

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

[Jeff Bailey]

Did it work with those characters before?

[jonket]

Yes. it did

[Jeff Bailey]

probably needs html_entity_decode() somewhere, I'll look around some tomorrow to see if thats the issue.

[Αndré]

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

1.

Code Select Expand

$user_count = 1;
probably has to be

Code Select Expand

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

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?