CPG 1.4.2 and SMF 1.1 RC1 SELECT count(*) error

[CapriSkye]

i have CPG 1.4.2 and SMF 1.1 RC1 bridged, it's able to synchronize the member groups, but when i go click on members in CPG it gives me this error:

Code Select Expand


While executing query "SELECT count(*) FROM `smf`.smf_members WHERE 1" on 0

mySQL error:

It doesn't say what mysql error is, and the file it gives me is D:\website\capriskye\gallery\include\functions.inc.php at line 251.
but the problem doesn't seem to be that file.

when comment out this line in udb_base.inc.php it doesn't give me that error but something about member table is empty.

Code Select Expand


if (!$user_count) {
            $result = cpg_db_query("SELECT count(*) FROM {$this->usertable} WHERE 1");
            $nbEnr = mysql_fetch_array($result);
            $user_count = $nbEnr[0];
            mysql_free_result($result);
        }


i believe that's where the sql fails.
when running that SELECT count(*) in phpmyadmin, it's able to tell me the member count, which is only 1, me.

i've changed my member group name from Administrator to something different in smf, which is in Chinese, but when i change it back to Administrator and bridge it again still makes no difference.

i've also bridge the same cpg and smf version on my local machine, it's able to bridge with no problem, so i know it works.
no sure what the problem is, maybe somebody has an idea? thanks

[Nibbler]

Change

Code Select Expand

$result = cpg_db_query("SELECT count(*) FROM {$this->usertable} WHERE 1");

to

Code Select Expand

$result = cpg_db_query("SELECT count(*) FROM {$this->usertable} WHERE 1", $this->link_id);

[CapriSkye]

now i'm getting this

Code Select Expand


While executing query "SELECT ID_MEMBER as user_id, memberName as user_name, emailAddress as user_email, dateRegistered as user_regdate, UNIX_TIMESTAMP(lastLogin) as user_lastvisit, is_activated as user_active, COUNT(pid) as pic_count, ROUND(SUM(total_filesize)/1024) as disk_usage, group_name, group_quota FROM `smf`.smf_members AS u INNER JOIN cpg_usergroups AS g ON u.ID_POST_GROUP+100 = g.group_id LEFT JOIN cpg_pictures AS p ON p.owner_id = u.ID_MEMBER GROUP BY user_id ORDER BY user_regdate DESC LIMIT 0, 25;" on 0

mySQL error:


[Nibbler]

Same fix there.

[CapriSkye]

still doesn't fix it though.

i think this is the query:

Code Select Expand


        $sql = "SELECT {$f['user_id']} as user_id, {$f['username']} as user_name, {$f['email']} as user_email, {$f['regdate']} as user_regdate, {$f['lastvisit']} as user_lastvisit, {$f['active']} as user_active, ".
               "COUNT(pid) as pic_count, ROUND(SUM(total_filesize)/1024) as disk_usage, group_name, group_quota ".
               "FROM {$this->usertable} AS u ".
               "INNER JOIN {$C['TABLE_USERGROUPS']} AS g ON u.{$f['usertbl_group_id']} = g.group_id ".
               "LEFT JOIN {$C['TABLE_PICTURES']} AS p ON p.owner_id = u.{$f['user_id']} ".
               $options['search'].
               "GROUP BY user_id " . "ORDER BY " . $sort_codes[$options['sort']] . " ".
               "LIMIT {$options['lower_limit']}, {$options['users_per_page']};";


i added $this->link_id in cpg_db_query, the one right after the SELECT above and now mysql error says no database selected.

[Nibbler]

The usermanager won't work with cpg and smf in different dbs then, I'll need to add in the alternative implementation.

[CapriSkye]

different db? i thought that's how most people set up cpg and smf.
how come it works for other installation?
thanks for the help.

[kegobeer]

No, most people install everything into the same database.  You usually find separate databases when using an autoinstaller like Fantastico.  Just another reason why I can't stand Fantastico!

[Nibbler]

If you have smf and cpg in the same database, or diff dbs but have the same db user for both smf and cpg then we can join the smf members table with coppermine tables. To use a join you need to have a user with access to both smf and cpg tables. If you don't then the bridge has to do all smf stuff on 1 db connection, the cpg stuff on another connection and then combine the data in the code instead of in mysql.

[CapriSkye]

that's my problem then, i have different users for cpg and smf, thought it was better for security.
now they all use same user and password, still in different database though, but now it works.
which way is recommended? or does it matter?
thanks!

[Nibbler]

Having 2 different accounts is better for reasons of security and if you have a set mysql connection limit per user (it spreads the connections over 2 accounts this way). Having the same account is better for reasons of performance and convenience. The aim is for bridging to work either way.