Accessing an external database from a joomla module

[Techbot]

On a joomla 1.14 site


the code is fairly straightforward,

Code: Select all

<?php

defined( '_VALID_MOS' ) or
    die( 'Direct Access to this location is not allowed.' );

$conn = mysql_connect(localhost,JoeBlogs,secret) ;

mysql_select_db(external_database) ; 

$result = mysql_query("SELECT artist, title from historylist ORDER BY historylist.date_played DESC");

list($title,$artist)= mysql_fetch_row($result);
 	 
	 echo $title ;   echo "<br>" ;
	  echo $artist ; echo "<br>" ;
		 
 ?>

It works but breaks all following connections with the jos_database (as far as I can tell) ie any modules after this one stop working.

Is it because I need to make the external call, some kind of instance?.

Any help would be appreciated, this is my first module.

[Rogue4ngel]

Greetings. I do know there has been some discussion on connecting to external databases. Here's a tip on connecting to an external db:
http://dev.joomla.org/component/option, ... _database/

Not sure if it will help, but it can't hurt.

[Techbot]

Thanks Rogue.

This is my version of the above code translated to joomeese. Unlike the above which works but breaks joomla database connectivity, this fails to work at all. No doubt a typo or some syntax. I've done some decent searching and beyond the link you provided, there is actually nothing else in the forums.

I could move the external table into joomla, but I won't have learned anything as a result and it makes my next venture impossible.

thanks

Code: Select all

<?php

defined( '_VALID_MOS' ) or
    die( 'Direct Access to this location is not allowed.' );
	
$option['driver']   = 'mysql';            // Database driver name
$option['host']     = 'localhost';    // Database host name
$option['user']     = 'joebloggs';       // User for database authentication
$option['password'] = 'secret';  // Password for database authentication
$option['database'] = 'external database';      // Database name
$option['prefix']   = '';             // Database prefix (may be empty)

$database = & JDatabase::getInstance( $option );


$sql= "SELECT artist, title from historylist ORDER BY date_played DESC" ;

$database->setQuery( $sql );

$category = $database->loadRow();

 
echo $category ;
	
		  echo "<br>" ;
		  
 ?>
	

[Rogue4ngel]

Something I may do some research on myself. Seems to be something that a lot of people would use. I am sure someone out there has done it (a few bridges out there that work). I'll see what I can dig up.

[Techbot]

I'm guessing this issue is only relevant to modules not components. I'm using one of the custom code components to access the same database and it works fine.

Is it possible getinstance is a 1.5 only function?
****************************
getInstance (line 194)

Returns a reference to the global Database object, only creating it if it doesn't already exist.

The 'driver' entry in the parameters array specifies the database driver to be used (defaults to 'mysql' if omitted). All other parameters are database driver dependent.

* return: A database object
* since: 1.5

JDatabase &getInstance ([array $options = array()])

* array $options: Parameters to be passed to the database driver

http://api.joomla.org/Joomla-Framework/ ... etInstance
****************************





Does my joomlise sql look correct? ie most of the code should be the same as if, in the joomla database. I'm guessing the only different between an internal and external is the line

$database = & JDatabase::getInstance( $option );

unfortunately a search for external database leads to this post.

I'm gonna check the fabrik module code to see if I can find any hints there. I'll post anything I find.

Thanks again rogue.

[Rogue4ngel]

What you have looks ok to me. I always get messed up when I start going back and forth trying to figure out what's passing by reference. Probably why I didn't become a career coder .

Sounds like you've got a lead to follow. Glad to hear about what you come up with, if anything. I haven't found anything yet, but there is a way around this. Perhaps someone may shed some light...