Connected external Database extract values into Fabrik Form

[cheyennemtnman]

Thanks for taking a look once again... still getting a blank page
here is the code I had inserted with Hugh and Your help...

$user = JFactory::getUser(); 
$email = $user->get('email'); 
$db = JFactory::getDbo(); 
$db->setQuery("SELECT * FROM tr_survey_list WHERE email=" . $db->Quote($email)); 
$rows = $db->loadObjectList();
$list = array(); 
foreach ($rows as $row)  
{ 
$tablename = $row->tr_survey_table_name;
$query="SELECT status FROM ".$tablename." WHERE email=".$db->Quote($email); 
$db->setQuery($query); 
$surveystatus= $db->loadResult(); 
$list[] = '<li><a href="' . $row>tr_survey_link . '"> ' . $row->Survey_Subject . '</a>  - ' .  $row->Evaluator_Type .'___'. $surveystatus . '</li>';; 
} 
return '<ol>' . implode($list) . '</ol>';

Where does this code know that I want to use connected database... seems to me it is looking for site database...

 

[cheyennemtnman]

This code works and lists without the status code...

$user = JFactory::getUser();
$email = $user->get('email');
$db = JFactory::getDbo();
$db->setQuery("SELECT * FROM tr_survey_list WHERE email=" . $db->Quote($email));
$rows = $db->loadObjectList();
$list = array();
foreach ($rows as $row) {
      $list[] = '<li><a href="' . $row->tr_survey_link . '"> ' . $row->Survey_Subject . '</a>  - ' .  $row->Evaluator_Type . '</li>';
}
return '<ol>' . implode($list) . '</ol>';

 

[rob]

HI

$row>tr_survey_link

should be

$row->tr_survey_link

$user = JFactory::getUser();
$email = $user->get('email');
$db = JFactory::getDbo();
$db->setQuery("SELECT * FROM tr_survey_list WHERE email=" . $db->quote($email));
$rows = $db->loadObjectList();
$list = array();
foreach ($rows as $row)   {
  $tablename = $row->tr_survey_table_name;
  $query="SELECT status FROM ".$tablename." WHERE email=".$db->quote($email); 
  $db->setQuery($query);
  $surveystatus= $db->loadResult();
  $list[] = '<li><a href="' . $row->tr_survey_link . '"> ' . $row->Survey_Subject . '</a>  - ' .  $row->Evaluator_Type .'___'. $surveystatus . '</li>';;
}
return '<ol>' . implode($list) . '</ol>';

 

[cheyennemtnman]

Thanks for catching that... Okay now the form is showing but there are no row values showing and no status values ... How does the code above know this is a connected database other than the site database. Am I missing a statement to tell it to look for table in a secondary connected database?

 

[troester]

Originally Posted by troester
See http://fabrikar.com/wiki/index.php/C...omla_PHP_tasks
In your calc element fetch the information you need from your 2nd database, compose your link string, something like
Code:
$db = FabrikWorker::getDbo(false, 2); ... $a='<a target="_blank" http=.......'; return $a;

This is what this post was about.
So I think (not tested)

$user = JFactory::getUser();
$email = $user->get('email');
$db = JFactory::getDbo();
$dbc2 = FabrikWorker::getDbo(false, 2);

$db->setQuery("SELECT * FROM tr_survey_list WHERE email=" . $db->quote($email));
$rows = $db->loadObjectList();
$list = array();
foreach ($rows as $row)   {
  $tablename = $row->tr_survey_table_name;
  $query="SELECT status FROM ".$tablename." WHERE email=".$db->quote($email); 
  $dbc2->setQuery($query);
  $surveystatus= $dbc2->loadResult();
  $list[] = '<li><a href="' . $row->tr_survey_link . '"> ' . $row->Survey_Subject . '</a>  - ' .  $row->Evaluator_Type .'___'. $surveystatus . '</li>';;
}
return '<ol>' . implode($list) . '</ol>';

 

[cheyennemtnman]

That is it! So you can nest the check into the second database... Thanks all for the help! I will post something to show this in the wiki to make it clearer for the first timers...

 

[cheyennemtnman]

Okay back again... The calc is working great but now I would like to pass one of the data values found in the for each loop to add as a parameter to the form I am sending them to...

Can this be done in the calc element... I would like to have a url built with &Survey_Subject value go to an element on a fabrik form... How would I go about building that URL with the lastest code above

 

[cheesegrits]

Fixed this on Skype session.

Just need to build the URL thusly:

[12:28:45 PM] Hugh Messenger: $row->tr_appraisal_url . "&tr_employee_appraisal_form___tr_employee_name=" . $row->Survey_Subject

-- hugh

 

[cheyennemtnman]

Yes thanks once again... worked nicely... Can see now why it is better to build the link within the calc and not have it loaded totally in an element of a table... more flexibility

$list[] = '<li><a href="' . $row->tr_appraisal_url . "&tr_employee_appraisal_form___tr_employee_name=" . $row->Survey_Subject . '"> ' . $row->tr_appraisal_subject . '</a>  - will return to Manager Console ' . '</li>';;
}
return '<ol>' . implode($list) . '</ol>';

 

[cheesegrits]

Yup. Personally, I'd go as far as just storing the form ID in your metadata table, instead of the whole link, and build the link on the fly in the calc, just using the formid and Survey_Subject from $row. The form ID is the only thing that actually changes in your stored links, and at some point you may change your link format (like you might enable SEF), and it's a darn site easier to change one line in the PHP calc, than modify every URL in your table.

-- hugh