Connected external Database extract values into Fabrik Form

cheyennemtnman

cheyennemtnman

Member
I would like to build a url link with values from a connected and accessible database residing in the same domain.

I just need to extract two fields from the database tables to construct a url that I want to display in a form and that url should be clickable for the user to open a new window for that url. I would match the email address for between the two database tables to extract the proper information.

I was told the calc element might be the way to do it. Has anyone attempted to do something like this? if so can you share your solution?

Thank you for your input in advance!
 
See http://fabrikar.com/wiki/index.php/Common_Joomla_PHP_tasks
In your calc element fetch the information you need from your 2nd database, compose your link string, something like
Code: 
[COLOR=#000088]$db[/COLOR] [COLOR=#339933]=[/COLOR] FabrikWorker[COLOR=#339933]::[/COLOR][COLOR=#004000]getDbo[/COLOR][COLOR=#009900]([/COLOR][COLOR=#000000][B]false[/B][/COLOR][COLOR=#339933],[/COLOR] [COLOR=#cc66cc]2[/COLOR][COLOR=#009900])[/COLOR][COLOR=#339933];
...[/COLOR]
$a='<a target="_blank" http=.......';
return $a;

 
So can this be done with php in the calc field using connected db

Joomla User table - email address to match another table from connected database with a table
with an email address field this connected table has multiple instances of the same email address
from 2 to 20 times.
I want to find all matching email addresses in the connected database table and build
a URL from concantenating two fields in the connected table with a string of
"http://mywebsite.com/".field1.field2 and display a status field from the same table for that record.

What is displayed in form....
http://mywebsite.com/".field1.field2 STATUS VALUE from connected table
http://mywebsite.com/".field1.field2 STATUS VALUE from connected table
http://mywebsite.com/".field1.field2 STATUS VALUE from connected table
http://mywebsite.com/".field1.field2 STATUS VALUE from connected table
http://mywebsite.com/".field1.field2 STATUS VALUE from connected table
http://mywebsite.com/".field1.field2 STATUS VALUE from connected table

Will the built links be clickable in form?
...
I am thinking an array of values have to be returned and displayed
 
Hoping It would be something like this...

$email = $user->get('email');

$result = mysql_query("SELECT * FROM table
WHERE email='$email'");

while($row = mysql_fetch_array($result))
{
echo 'http://mywebsite.com/...' . row['field2'] . $row['field3'] ." ". $row['field4'] ;
echo "<br />";
}
 
troester said:
You should use the Joomla API and the calc element must return the string you want to display (not echo).
http://fabrikar.com/wiki/index.php/Common_Joomla_PHP_tasks
Click to expand...

Is this what you mean?

$email = $user->get('email');
$list = array();
$list = mysql_query("SELECT * FROM table
WHERE email='$email'");
foreach ($rows as $row) {
$list[] = '<li>'$row->"http://mywebsite.com/" . $row->field2 .$row->field3 .' '. $row->field 4'</li>';
}$
return '<ol>'.implode($list).'</ol>';
 
That code is broken, try this:

PHP: 
$user = JFactory::getUser();
$email = $user->get('email');
$db = JFactory::getDbo();
$db->setQuery("SELECT * FROM table WHERE email=" . $db->Quote($email));
$rows = $db->loadObjectList();
$list = array();
foreach ($rows as $row) {
    $list[] = '<li>http://mywebsite.com/' . $row->field2 . $row->field3 . ' ' . $row->field4 . '</li>';
}
return '<ol>' . implode($list) . '</ol>';

-- hugh
 
You mentioned on Skype you actually have 29 tables to find the email in, and were going to build a combined CSV export of those, and import it into a single table to do this with.

Any luck with that?

-- hugh
 
Yes I exported the twenty nine tables into one table and plan to import that into a fabrik table. There I can find the indentifying tables that share the same email address value but I still have to access the set of 29 tables to get the status of that individual from the tables he is listed in. Here is the trick... the only way to get to the correct table is by its name. THe identifying factor for the table is only in the name of the table. The last segment of the table name is the identifier. Table_type_999999 (number is the identifier) The front part is the same for all tables in question. So I guess I have to find the table number from the newly imported list(which I added to the table before importing) then build the table variable in the code you provide above for each table the user email matches. Then display the status for each table in the users private form.

Is there a way to join the tables that the logged in user email address is listed in? This could be one table or 15 tables to be joined.

Would there be a calc element per table that the user email is located in to display it?
 
Set up the Table mentioned above... Set the form for -1 with ID in the row parameters...
I get this error...

Error: 500
You may not be able to visit this page because of:

an out-of-date bookmark/favourite
a search engine that has an out-of-date listing for this site
a mistyped address
you have no access to this page
The requested resource was not found.
An error has occurred while processing your request.

$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>$row->tr_survey_link . ' ' . $row->tr_survey_table_name . '</li>';
}
return '<ol>' . implode($list) . '</ol>';
Click to expand...

Still have not figured out how to get status from the table while listing the links
 
Thanks Hugh... got most of what I needed...

Here is the code that works... sans 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>';

Now I need to figure out how to find the status of each survey link from the survey table list... I know the tables for each employee but need to list on the same line as the link... how can I list the survey link with the status for that particular survey... can I load an array within the loadobject to create a row object to list? It is a separate but connected database.
 
troester said:
See http://fabrikar.com/wiki/index.php/Common_Joomla_PHP_tasks
In your calc element fetch the information you need from your 2nd database, compose your link string, something like
Code: 
[COLOR=#000088]$db[/COLOR] [COLOR=#339933]=[/COLOR] FabrikWorker[COLOR=#339933]::[/COLOR][COLOR=#004000]getDbo[/COLOR][COLOR=#009900]([/COLOR][COLOR=#000000][B]false[/B][/COLOR][COLOR=#339933],[/COLOR] [COLOR=#cc66cc]2[/COLOR][COLOR=#009900])[/COLOR][COLOR=#339933];
...[/COLOR]
$a='<a target="_blank" http=.......';
return $a;
Click to expand...

$db = FabrikWorker::getDbo(false, 2); what does this do... not much in the wiki about it... "FabrikWorker" is something built into this interface? So after I use this statement... How do I find a value of a column in a row in the table I need to look at? Is there a way iterate through a fabrik table selection to find what tables to look at in the connected database? I have the names of the tables in the connected database as a field in a fabrik table (site database)

Site2ConnectedStatusupdate.jpg
 
Possible solution?

Can this be done in the framework of Fabrik Calc field?

Code: 
declare @tablename varchar(50)
$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) 
{
Set @tablename = $row->tr_survey_table_name
@query='SELECT * FROM '+ @tablename WHERE email=" . $db->Quote($email));
exec(@query)
$surveystatus= status;
}
{
      $list[] = '<li><a href="' . $row->tr_survey_link . '"> ' . $row->Survey_Subject . '</a>  - ' .  $row->Evaluator_Type . '.  $surveystatus . '</li>';
}
return '<ol>' . implode($list) . '</ol>';
 
Tried the code above no result.. .just blank for calc field

Expected result...
Employee________ Type___________Status Complete

1. Employee Name A - Self ______________N
2. Employee Name B - Peer______________Time/Date Stamp
3) Employee Name C - Manager__________Time/Date Stamp
4) Employee Name D - Direct Report______Time/Date Stamp
....
 
At a quick glance
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)  
{ 
$tablename = $row->tr_survey_table_name;
$query="SELECT status FROM ".$tablename." WHERE email=".$db->Quote($email); 
$db->setQuery($query);
$xx= $db->loadResult();
//??$surveystatus= status;
//is status a column in $tablename?
$list[] = '<li><a href="' . $row>tr_survey_link . '"> ' . $row->Survey_Subject . '</a>  - ' .  $row->Evaluator_Type .'___'. $xx->status . '</li>'; 
} 
return '<ol>' . implode($list) . '</ol>';

Edit: code modified after reading your previous posts
 
I shouldn't edit 2 things at the same time;)
Try
Code: 
$db->setQuery($query); 
$surveystatus= $db->loadResult(); 

$list[] = '<li><a href="' . $row>tr_survey_link . '"> ' . $row->Survey_Subject . '</a>  - ' .  $row->Evaluator_Type .'___'. $surveystatus . '</li>';
 
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 89 (members: 2, guests: 87)
    Back
    Top