Use multiple remote connections in a single calc field

[georgie]

Hello

I use a calc field to display remote data (another database). It's absolutely great, thank you Fabrik!

Now I would like to display data from another database, but in the same field (to minimize the number of fields). Is this possible?

Here my actual code fort just one remote DB (works like a charm):

$prospects_email = '{prospects___email_raw}';

if (!empty($prospects_email)) {

    $myDb = FabrikWorker::getDbo(false, 11);

    $myQuery = $myDb->getQuery(true);

    $myQuery->select("group_concat(ma_concat SEPARATOR ', ')
       from (select concat(date_format(from_unixtime(prime_acymailing_urlclick.date),'%d/%m/%Y')) as ma_concat
      from prime_acymailing_url inner join prime_acymailing_urlclick on prime_acymailing_url.urlid = prime_acymailing_urlclick.urlid inner join prime_acymailing_subscriber on prime_acymailing_urlclick.subid = prime_acymailing_subscriber.subid
         where prime_acymailing_subscriber.email = " . $myDb->quote($prospects_email) . "
         order by prime_acymailing_urlclick.date desc
         limit 0,6) as alias");
   $myDb->setQuery($myQuery);
   $ClicHistory = $myDb->loadResult();

   if (!empty($ClicHistory)) {
      return 'Last clicks from <font style="color:#f38709 ; font-weight: bold">Prime-journal.online</font> on '. $ClicHistory ;
   }
}
// If empty
else {
   return '';
}

I have try to just add this after my first loadResult, mais cela suffit ? faire planter la page:

$myDb2 = FabrikWorker::getDbo(false, 17);

$myQuery2 = $myDb2->getQuery(true);

$myQuery2->select("group_concat(ma_concat SEPARATOR ', ')
   from (select concat(date_format(from_unixtime(vcs_acymailing_urlclick.date),'%d/%m/%Y')) as ma_concat
   from vcs_acymailing_url inner join vcs_acymailing_urlclick on vcs_acymailing_url.urlid = vcs_acymailing_urlclick.urlid inner join vcs_acymailing_subscriber on vcs_acymailing_urlclick.subid = vcs_acymailing_subscriber.subid
   where vcs_acymailing_subscriber.email = " . $myDb2->quote($prospects_email) . "
   order by vcs_acymailing_urlclick.date desc
   limit 0,6) as alias");

$myDb2->setQuery($myQuery2);
$ClicHistory2 = $myDb2->loadResult();

Please have you an idea?

Georges

 

[Sophist]

The new code looks OK. Adding it to the end of the existing code will mean it is only executed if a return statement has not been previously executed.

I have indented your code for you because I think that this will help you position your additional code in relation to the if/then/else and return statements.

 

[georgie]

Ohh thank you !

But really I have an error, I do not undertstand why...

I search

 

[cheesegrits]

Is it actually erroring out? Or just not returning what you expect?

-- hugh

 

[georgie]

Hello

Yes a Fabrik error : Fabrik has generated...

I keep to try, debug...

If you have an idea... I take !

THX

 

[lcollong]

Did you try to add :
echo $myQuery2->dump();
before the loadResult to have a look to your SQL ?
Then you can copy paste this SQL in phpmyadmin and see how it runs...

Why do you have 2 "from" clauses ?

 

[georgie]

Ah yes !

OK it seems to works, I keep to work !

A simple syntax code error...

My 2 "from" are to do a sub-request (goal: display on a Fabrik CRM, clicks from a remote Acymailing, it works like a charm, now I want to display clicks from several remote Acymailing in just one calc field).

Georges

 

[lcollong]

Ah ok. I've missed a parenthesis
Good to know. I did some CRM involving both Fabrik and Acy as well. Nice products to work with !