Joining joomla table with external table

[Fran M.]

Hi,
I have an external table in an external databse with the categories id
from the joomla categories table.
I want to show the external data with the name of the category. Is
there any way o Joomla utility to do this external join?
Thanks

[Prasit Gebsaap]

Hi,

 

For me, I have done this using external database connection object to query data from external database. Then I use category_id to find for category name. I use this method because there are many external databases that I have to query them.

 

The otherway that may be possible is to create view for database in joomla so you can query 2 database for the same time. This is possible if you have only small number of external database.

--
You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.
To post to this group, send an email to joomla-de...@googlegroups.com.
To unsubscribe from this group, send email to joomla-dev-gene...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/joomla-dev-general?hl=en-GB.

--

Prasit Gebsaap

 

Nonthaburi, Thailand

http://www.joomlant.com, http://www.joomlant.org

http://ktdms.blogspot.com, http://lowcost-it.blogspot,com

 

[Fran M.]

I was thinking in another 2 ideas:
1. Create an array object with the categories id and category title,
so, when displaying the external data, search in the array
2. Another, but I am not sure, is posible to changea result query
object and add a new column with the category names? I am not sure
about this one

On 27 ene, 09:19, Prasit Gebsaap <prasit.gebs...@gmail.com> wrote:
> Hi,
>
> For me, I have done this using external database connection object to query
> data from external database. Then I use category_id to find for category
> name. I use this method because there are many external databases that I
> have to query them.
>
> The otherway that may be possible is to create view for database in joomla
> so you can query 2 database for the same time. This is possible if you have
> only small number of external database.
>

[Prasit Gebsaap]

That are the solution I used. You can add property to result set like this.

/**

* Override JModelList 's getItems to insert unit name

*

@see JModelList::getItems()

*/

function getItems() {

          if ($result = parent::getItems()) {

                 $db = JFactory::getDbo();

                 $query =

"SELECT id, title FROM #__asterman_units";

                  if ($units = $db->setQuery($query)->loadAssocList('id')) {

                          foreach ($result as $item) {

                                if (empty($item->unit_id)) {

                                      $item->

unit_name = 'NA';

                                  }

else{

                                        $item->

unit_name = $units[$item->unit_id]['title'];

                                  }

                          }

                  }

                  return $result;

         }

}