Query another table inside chronoconnectivity

Hello,

I am trying to get a field from another table based on a field from the table that is currently connected.
The code that i am using in the body section is

<td bgcolor="#999999" width=160 ><?php
$db =& JFactory::getDBO();
$query = "SELECT B_Name FROM esas_beneficiary WHERE B_id='{esas_projbenef}'";
$db->setQuery($query);
$i = $db->loadResult();
echo $i; ?></td>

where esas_projbenef is a field from the currently connected table and B_Name is the field that i am trying to get from the other table. The result is empty.

I have replace $db with $db2 ..still nothing.
If i echo '{esas_projbenef}' it functions (each row with its own respective value).
If i echo $query all is ok, i have checked the syntax in phpmyadmin.
If i test with a static value e.g. B_id='xxx', it functions also.
For sure i messing things up a bit here. Can it be that there is a smoother way to do this?


Thank you,

Hi lorelainerv,

Because of the work-flow order in ChronoConnectivity you can't use the {column_name} syntax inside the <?php . . . ?> tags. Instead use the $MyRow Object.

<?php
$db =& JFactory::getDBO();
$query = "SELECT `B_Name` FROM `#__beneficiary` WHERE `B_id` = '".$MyRow->esas_projbenef."'";
$db->setQuery($query);
$i = $db->loadResult();
echo $i; ?

Bob

Thank you very much.
I started working on mysql view method, got it running to some point, and again got stuck on refreshing (replacing) the view when the chronoconnectivity table was accessed (CREATE OR REPLACE syntax).
I need to get my act together :?

Again, thank you very much!