Avatar Prob on CPG with ModPack bridged to Joomla

[DesMas]

Hi, maybe this had been already discussed, but my search didn't bring up good results till now:

Situation:
- I installed a cpg 1.4.12 and joomla 1.0.12 on my local "dev system"
- then i installed the latest modpack for the cpg
- after all running fine i bridged the cpg with the "mehdi plugin" (http://www.mehdiplugins.com/misc/) to joomla

from there on I get an error when i click on a thumbnail to view the image (and then put it into the basket):

Code Select Expand

While executing query "

SELECT c.msg_id, c.msg_author, c.msg_body, UNIX_TIMESTAMP(msg_date) AS msg_date, c.author_id, c.author_md5_id, c.msg_raw_ip, c.msg_hdr_ip, c.pid, u. AS avatar_url
FROM ssg_cpg_comments AS c
LEFT JOIN `SSG`.ssg_jos_users AS u ON c.author_id=u.id
WHERE pid='6'
ORDER BY c.msg_id ASC

" on Resource id #28

mySQL error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to
use near 'AS avatar_url FROM ssg_cpg_comments AS c LEFT JOIN `SSG`.ssg_jos_users AS u ON c' at line 1

I followed the code back to the file udb_base.inc.php at line 884:

Code Select Expand

$sql = "SELECT c.msg_id, c.msg_author, c.msg_body, UNIX_TIMESTAMP(msg_date) AS msg_date, c.author_id, c.author_md5_id, c.msg_raw_ip, c.msg_hdr_ip, c.pid, u.{$this->field['avatar_url']} AS avatar_url FROM {$CONFIG['TABLE_COMMENTS']} AS c LEFT JOIN {$this->usertable} AS u ON c.author_id=u.{$this->field['user_id']} WHERE pid='$pid' ORDER BY c.msg_id $comment_sort_order";

complete snipped:

Code Select Expand

    function get_comments($pid, $comment_sort_order)
    {
        global $CONFIG;
        if ($this->can_join_tables) {
            $sql =  "SELECT c.msg_id, c.msg_author, c.msg_body, UNIX_TIMESTAMP(msg_date) AS msg_date, c.author_id, c.author_md5_id, c.msg_raw_ip, c.msg_hdr_ip, c.pid, u.{$this->field['avatar_url']} AS avatar_url FROM {$CONFIG['TABLE_COMMENTS']} AS c LEFT JOIN {$this->usertable} AS u ON c.author_id=u.{$this->field['user_id']} WHERE pid='$pid' ORDER BY c.msg_id $comment_sort_order";

            $result = cpg_db_query($sql, $this->link_id);
            return $result;
        } else { //no join -> we do not get the avatar URL and if needed we need to get it later on in another query
            $sql =  "SELECT c.msg_id, c.msg_author, c.msg_body, UNIX_TIMESTAMP(msg_date) AS msg_date, c.author_id, c.author_md5_id, c.msg_raw_ip, c.msg_hdr_ip, c.pid FROM {$CONFIG['TABLE_COMMENTS']} AS c WHERE pid='$pid' ORDER BY c.msg_id $comment_sort_order";

            $result = cpg_db_query($sql);
            return $result;
        }
    }

it seems the field "$this->field['avatar_url']" does not exist...

Well the code itself runs to this point, because the "$this->can_join_tables" is on true (all tables in same database).
but by the fact that the needet entry doeas not exist in the joomla tables, the code runs itself dead.

How could this be resolved?
would it help if I change

Code Select Expand

if ($this->can_join_tables) { to

Code Select Expand

if ($this->can_join_tables && isset($this->field['avatar_url']) { or will be there more errors upcoming later.

As note: the "use avatar" setting in the config is set to "no"
2. note: foind this thread (http://forum.coppermine-gallery.net/index.php?topic=44127.0) but seems not to run the same way

[DesMas]

another note (cant edit my post)

the bridge was installed after the modpack by including the codes into the existing files (no overwrite)

changes:
bridgemgr.php

Code Select Expand

// status: bridge ok, manager ok
$default_bridge_data['mambo'] = array(
  'full_name' => 'Mambo server',
  'short_name' => 'mambo',
  'support_url' => 'http://www.mamboserver.com/',
  'full_forum_url_default' => 'http://www.yoursite.com/board',
  'full_forum_url_used' => 'mandatory,not_empty,no_trailing_slash',
  'relative_path_to_config_file_default' => '../board/',
  'relative_path_to_config_file_used' => 'lookfor,configuration.php',
  'use_post_based_groups_default' => '0',
  'use_post_based_groups_used' => 'radio,1,0',
);
//-----------------------------------------------------------------------------   
//Mehdi Plugin hack
// status: bridge ok, manager ok
$default_bridge_data['joomla'] = array(
  'full_name' => 'Joomla!',
  'short_name' => 'joomla',
  'support_url' => 'http://www.joomla.org/',
  'full_forum_url_default' => 'http://localhost',
  'full_forum_url_used' => 'mandatory,not_empty,no_trailing_slash',
  'relative_path_to_config_file_default' => '../',
  'relative_path_to_config_file_used' => 'lookfor,configuration.php',
  'use_post_based_groups_default' => '0',
  'use_post_based_groups_used' => 'radio,1,0',
);
//-----------------------------------------------------------------------------

functions.inc.php

Code Select Expand

//-----------------------------------------------------------------------------   
        //Mehdi Plugin hack
        //deactivated original line
        //$template = fread(fopen($template_file, 'r'), filesize($template_file));
       
        //new
        global $BRIDGE;
       
        $integrateTest= strcasecmp($BRIDGE['short_name'],"joomla") == 0;
        $integrateTest= $integrateTest && file_exists("bridge/joomla.integ.php");
        $integrateTest= $integrateTest && isset($BRIDGE['relative_path_to_config_file']);
        $integrateTest= $integrateTest && isset($BRIDGE['full_forum_url']);
             
        if($integrateTest) include ("bridge/joomla.integ.php"); //integrate.php will affect $template var
      else {
      $template = fread($fileres = fopen($template_file, 'r'), filesize($template_file));
      fclose($fileres);
      }
//---------------------------------------------------------------------------

[Stramm]

you need to edit joomla.inc.php... the part

Code Select Expand

$this->field = array(
'username' => 'username', // name of 'username' field in users table
'user_id' => 'id', // name of 'id' field in users table
'password' => 'password', // name of 'password' field in users table
'email' => 'email', // name of 'email' field in users table
'regdate' => 'UNIX_TIMESTAMP(registerDate)', // name of 'registered' field in users table
'lastvisit' => 'UNIX_TIMESTAMP(lastvisitDate)', // last time user logged in
'active' => '1-block', // is user account active?
'location' => "''", // name of 'location' field in users table
'website' => "''", // name of 'website' field in users table
'usertbl_group_id' => 'gid', // name of 'group id' field in users table
'grouptbl_group_id' => 'group_id', // name of 'group id' field in groups table
'grouptbl_group_name' => 'name' // name of 'group name' field in groups table
);

What to edit you know when eg. comparing the modpack phpbb2018.inc.php with the original one.

--> adding

Code Select Expand

'notify' => 'user_notify_pm',
'auto_subscribe_post' => 'user_notify',
'auto_subscribe_comment' => 'user_notify',
            'avatar_url' => 'user_avatar'

however that won't work out of the box. You'll have to find the field names joomla uses and replace the above ones (phpbb). Another problem may be the avatars. Depending on how joomla handles them. And you either need to modify the bridgemanager or you have to manually add the path to the avatars to coppermines config table (if bridged the modpack doesn't use its own avatar system anymore but the forums).

[DesMas]

Thx for the answer.
Ill focus on that this weekend.....

Well by the fact, that joomla in original has no avatars, and the thing that u can turn of the use of the "avatar use" in the coppermine config, wouldn't it make sense the the query which uses the $this->field['avatar_url'] would only be taken, if the "avatar use" in the coppermine config is enabled?

[Stramm]

no, then again there's the need for an additional if clause and another query... of course possible.
also you could create a field in the joomla users table (myavatars or something like that)... that's always empty. So it won't return a value but the query for it won't spit out an error too.

[DesMas]

Stramm, little question .. hope i get is described....

If i want to add some more fields for the emails send to user and admin but these fields are part of an table named user_extended in the database, how could i easyly reach this data?

example
photo_shop_checkout.php -> photoshop_email_the_user()

Code Select Expand

$template_vars = array(
'{ORDER_ID}' => $order_id,
'{SITE_NAME}' => $CONFIG['gallery_name'],
'{PRICE}' => number_format(($cd_price[0]+$cd_price[1]+$shipping_price-$discount),2),
'{USER_NAME}' => (USER_NAME),
'{ADMIN}' => $CONFIG['gallery_name'],
'{LINK}' => $CONFIG['ecards_more_pic_target'],
//add
'{STREET}' => $user_info['street'],
// .. and more
);

I would think I had to manupulate the function udb_base.inc.php->get_user_infos() is that correct?
Or is there an easy way hidden in the system to reach this table mayby by using the bridge files?
Or is it the best to add a manual query into the get_user_infos() function and merge the data?

Any hint for a newb?

[DesMas]

well i managed it now by a mod in the
udb_base.inc.php->get_user_infos() by adding the following lines before the return statement:

Code Select Expand

                /**
                 * START manual add for user extended data
                 */
                //query the data we need
                $sql = "SELECT u.id, u.name AS fullname, e.* " .
                        "FROM jos_users u " .
                            "LEFT JOIN jos_user_extended e ON ( u.id = e.id ) " .
                        "WHERE u.id = '$uid' " .
                        "LIMIT 1";
                $result = cpg_db_query($sql, $this->link_id);
                //fetch the data
                if (!mysql_num_rows($result)) cpg_die(ERROR, $lang_register_php['err_unk_user'], __FILE__, __LINE__);
                $ext_user_data = mysql_fetch_array($result);
                // check the cols for null values
                if (!isset($ext_user_data['fullname'])) $ext_user_data['fullname'] = '';
                if (!isset($ext_user_data['user1'])) $ext_user_data['user1'] = '';
                if (!isset($ext_user_data['user2'])) $ext_user_data['user2'] = '';
                if (!isset($ext_user_data['user3'])) $ext_user_data['user3'] = '';
                if (!isset($ext_user_data['user4'])) $ext_user_data['user4'] = '';
                if (!isset($ext_user_data['user5'])) $ext_user_data['user5'] = '';
                if (!isset($ext_user_data['user6'])) $ext_user_data['user6'] = '';
                if (!isset($ext_user_data['user7'])) $ext_user_data['user7'] = '';
                if (!isset($ext_user_data['user8'])) $ext_user_data['user8'] = '';
                if (!isset($ext_user_data['user9'])) $ext_user_data['user9'] = '';
                if (!isset($ext_user_data['user10'])) $ext_user_data['user10'] = '';
                if (!isset($ext_user_data['user11'])) $ext_user_data['user11'] = '';
                if (!isset($ext_user_data['user12'])) $ext_user_data['user12'] = '';
                if (!isset($ext_user_data['user13'])) $ext_user_data['user13'] = '';
                if (!isset($ext_user_data['user14'])) $ext_user_data['user14'] = '';
                if (!isset($ext_user_data['user15'])) $ext_user_data['user15'] = '';
                //merge the arrays
                $user_data = array_merge($user_data, $ext_user_data);
                mysql_free_result($result);
                /**
                 * END manual add for user extended data
                 */               

and added them to the template_vars array in the photo_shop_checkout.php->photoshop_email_the_user()

But I dont love this query with its hardcoded table statements..... would like it more unbound....

[Stramm]

That's not related to the modpack and also not to your initial avatar question.

To avoid changing to much core code I'd avoid editing udb_base.inc.php and query the extended user data winthin the function photoshop_email_the_user(). Here I wouldn't worry to much if the table name is hardcoded.

[DesMas]

yeah Iknow its not really related to the thing, the relation I had in mind was the thing that u may due the modpack coding itself had a little hint in background how to include external tables into the code for easy access...
well I'll move the code into a new function for the user info in the photo_shop_checkout.php file.
Thx anyway.... if u want u can close this thread.