0

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?

  • There is no point in your code as it was no joins. And you can write that kind a like join with Joomla DB Driver (not sure if you can do it with pure MySql). So what you should do is create a function that would connect to DB2 (accomplishable) fetch data and past those into DB1. – Alexandr Oct 26 '17 at 16:15
  • Thank you, I have update the query that now is working in myphpadmin: UPDATE db1.table1 SET filepath= CONCAT("/path/",(SELECT imgfilename FROM db2.table2 where id=value)) WHERE id_gallery = value But after setting the php function in fabrik form I receive a 500 server error on form submission – Pier67 Oct 26 '17 at 16:45
  • I think that I'm not updating the right database – Pier67 Oct 26 '17 at 16:53
  • I'd suggest to try any dummy select query from multiple DB with Joomla first. and if it works then try to update. – Alexandr Oct 26 '17 at 17:36
  • Did you meet any positive results with that? – Alexandr Oct 27 '17 at 14:29
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.

  • This answer looks like the correct implementation of this advice: docs.joomla.org/Connecting_to_an_external_database (I'm not sure I'd write the error handling in the same fashion, but that is beside the point.) @Pier67 If this answer resolved your issue, please mark it with the green tick. If this answer did not resolve the issue, please update your question so that other volunteers can help to move this page to a system-recognized resolution. – mickmackusa Mar 12 at 1:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.