Fabrik Cross database joins

Question

I have two separate databases with the same credentials: in db1.table1 there is a column containing filepaths that I need to update with a filename that is stored in db2.table2 and that I can retrieve using an ID from a a Joomla Fabrik form input. The form on submission store an ID to db1.table1 and also run a form php plugin with the following code

$galleryId = '{id_element_name_raw}';

if (!empty($galleryId)) {
   $myDb = JFactory::getDbo();
   $myQuery = "
      UPDATE db1.table1
      SET db1.table1.path =
        CONCAT('/filepath/', db2.tabele2.imgfilename)
      WHERE db2.table2.id = '$galleryId'
";
   $myDb->setQuery($myQuery);
   $myDb->execute();
}

where id_element_name_raw is the ID full element name, galleryID is the image ref number in db2 that user is setting in db1 to make the join. The form is stored in db2 (joomla system db).

This code should set on db1.table1.filepath the filepath build from filename contanined in db1.table1 and retrieved with ID. When I submit the form there isn't any change in filepath column in db1.table1 as I'm expecting.

Any tips to make it work?

Answer 1

I had no chance yet to test it, but it can work for you.

You can link and make another DataBase as "Slave".

Step 1: First setup your connection options:

$option['driver'] = 'MySQLi';
$option['host'] = 'localhost';
$option['user'] = 'root';
$option['password'] = '';
$option['database'] = 'joomla_slave';
$option['prefix'] = 'xxxx_';

Step 2: Now make instance of JDatabase with connection option:

$db_slave = & JDatabase::getInstance($option);

Step 3: Setup error handling:

if ( JError::isError($db_slave) ) {
            jexit('Database Error: ' . $db_slave->toString() );
}
if ($db_slave->getErrorNum() > 0) {
            JError::raiseError(500, 'JDatabase::getInstance: Could not connect to database <br />');
}

Step 4: Use the object and combine queries from your Master & Slave DataBase:

$db_master = & JFactory::getDbo();
$query = 'SELECT * FROM `#__users`';
$db_master->setQuery( $query );
$rows = $db_master->loadObjectList();             
echo '<pre>';
print_r($rows);
echo '</pre>';
echo '------------------------------';
$query = 'SELECT * FROM `#__users`';
$db_slave->setQuery( $query );
$rows = $db_slave->loadObjectList();                
echo '<pre>';
print_r($rows);
echo '</pre>';

Replace "echo" with "return" if use in fabrik Forms/Elements.