Page 1 of 1
[SOLVED] Connect to a remote database in a component
Posted: Wed Jan 02, 2008 8:31 pm
by arfinator
Hello,
This is my first post on the forums and my second day using joomla and i am in love. My question refers to connecting to another database (not the joomla database) and fetching data from there. I tried searching the forums but to no avail. Hopefully someone might be able to help. I just read the Hello World Component tutorials and I think that I kind of understand them.
So, here is the scenario:
I have information about songs currently broadcasting over the internet from an on-line radio station that I code for (title, artist, duration, etc). This information is stored in a MySQL database on the individual computers for each DJ and not on the webserver. My question is that is there a way to connect to a remote database within a component, fetch data or add new data, disconnect from that remote database, and then output what was fetched?
Does anyone have any ideas on how this might be accomplished?
Thank you in advance.
Hess Smith
Re: Connect to a remote database in a component
Posted: Wed Jan 02, 2008 9:48 pm
by arfinator
Okay. I found a way to initiate a remote instance of the database and am utilizing that in this script. The only problem is that I am getting weird errors that I cannot seem to explain:
These are the errors that I get when I try to load my component (com_playing).
Warning: array_key_exists() [function.array-key-exists]: The second argument should be either an array or an object in C:\Program Files\Apache Group\Apache2\htdocs\libraries\joomla\database\database.php on line 206
Warning: array_key_exists() [function.array-key-exists]: The second argument should be either an array or an object in C:\Program Files\Apache Group\Apache2\htdocs\libraries\joomla\database\database.php on line 207
Warning: array_key_exists() [function.array-key-exists]: The second argument should be either an array or an object in C:\Program Files\Apache Group\Apache2\htdocs\libraries\joomla\database\database.php on line 208
Warning: array_key_exists() [function.array-key-exists]: The second argument should be either an array or an object in C:\Program Files\Apache Group\Apache2\htdocs\libraries\joomla\database\database\mysql.php on line 58
Warning: array_key_exists() [function.array-key-exists]: The second argument should be either an array or an object in C:\Program Files\Apache Group\Apache2\htdocs\libraries\joomla\database\database\mysql.php on line 59
Warning: array_key_exists() [function.array-key-exists]: The second argument should be either an array or an object in C:\Program Files\Apache Group\Apache2\htdocs\libraries\joomla\database\database\mysql.php on line 60
Warning: array_key_exists() [function.array-key-exists]: The second argument should be either an array or an object in C:\Program Files\Apache Group\Apache2\htdocs\libraries\joomla\database\database\mysql.php on line 61
Warning: array_key_exists() [function.array-key-exists]: The second argument should be either an array or an object in C:\Program Files\Apache Group\Apache2\htdocs\libraries\joomla\database\database\mysql.php on line 62
Warning: array_key_exists() [function.array-key-exists]: The second argument should be either an array or an object in C:\Program Files\Apache Group\Apache2\htdocs\libraries\joomla\database\database\mysql.php on line 63
Fatal error: Call to undefined method JException::setQuery() in C:\Program Files\Apache Group\Apache2\htdocs\components\com_playing\playing.php on line 46
Here is the code for the file playing.php:
Code: Select all
<?php
###########################
## PREVENT DIRECT ACCESS ##
###########################
defined( '_JEXEC' ) or die( 'Direct access to this page is prohibited.' );
#################################
## REQUIRE THE HTML VIEW CLASS ##
#################################
jimport( 'joomla.application.helper' );
require_once( JApplicationHelper::getPath( 'front_html', 'com_playing' ) );
#########################
## COMPONENT STRUCTURE ##
#########################
//Initiate Joomla Database
$db = JFactory::getDBO();
//Search for currently active DJ from Joomla Database
$query = 'SELECT dj_id FROM #__radio_show WHERE active = 1 ORDER BY `id` DESC LIMIT 1';
$db->setQuery( $query );
$currentID = $db->loadResult();
//Using ID from last query, find all relevant information about currently playing DJ so we can connect to that DB
$query = 'SELECT id, db_driver, db_host, db_user, db_password, db_database, db_port FROM #__radio_djs WHERE id = ' . $currentID;
$db->setQuery( $query );
$ConInfo = NULL;
//Load all information into Object ConInfo
$ConInfo = $db->loadObject();
$option['driver'] = $ConInfo->db_driver; // Database driver name
$option['host'] = $ConInfo->db_host.':'.$ConInfo->db_port; // Database host name
$option['user'] = $ConInfo->db_user; // User for database authentication
$option['password'] = $ConInfo->db_password; // Password for database authentication
$option['database'] = $ConInfo->db_database; // Database name
$option['prefix'] = ''; // Database prefix (may be empty)
//Connect to remote database
$database = & JDatabase::getInstance( $option );
$songtest = 'SELECT title FROM songlist LIMIT 1';
$database->setQuery( $songtest );
$song = $db->loadResult();
playing_HTML::output($song);
?>
Does anyone know what could be going on?
Hess Smith
Re: Connect to a remote database in a component
Posted: Wed Jan 02, 2008 10:39 pm
by arfinator
Somehow I got past those errors by making the array $options[...] instead of $option[...].
Now, I am still getting an error:
Fatal error: Call to undefined method JException::setQuery() in C:\Program Files\Apache Group\Apache2\htdocs\components\com_playing\playing.php on line 46
What would this error be caused by?
Here is playing.php again:
Code: Select all
<?php
###########################
## PREVENT DIRECT ACCESS ##
###########################
defined( '_JEXEC' ) or die( 'Direct access to this page is prohibited.' );
#################################
## REQUIRE THE HTML VIEW CLASS ##
#################################
jimport( 'joomla.application.helper' );
require_once( JApplicationHelper::getPath( 'front_html', 'com_playing' ) );
#########################
## COMPONENT STRUCTURE ##
#########################
//Initiate Joomla Database
$db = JFactory::getDBO();
//Search for currently active DJ from Joomla Database
$query = 'SELECT dj_id FROM #__radio_show WHERE active = 1 ORDER BY `id` DESC LIMIT 1';
$db->setQuery( $query );
$currentID = $db->loadResult();
//Using ID from last query, find all relevant information about currently playing DJ so we can connect to that DB
$query = 'SELECT id, db_driver, db_host, db_user, db_password, db_database, db_port FROM #__radio_djs WHERE id = ' . $currentID;
$db->setQuery( $query );
$ConInfo = NULL;
//Load all information into Object ConInfo
$ConInfo = $db->loadObject();
$options['driver'] = $ConInfo->db_driver; // Database driver name
$options['host'] = $ConInfo->db_host.':'.$ConInfo->db_port; // Database host name
$options['user'] = $ConInfo->db_user; // User for database authentication
$options['password'] = $ConInfo->db_password; // Password for database authentication
$options['database'] = $ConInfo->db_database; // Database name
$options['prefix'] = ''; // Database prefix (may be empty)
//Connect to remote database
$database = & JDatabase::getInstance( $options );
$query = 'SELECT title FROM songlist LIMIT 1';
$database->setQuery( $query );
//$song = $db->loadResult();
//playing_HTML::output($song);
playing_HTML::output('DEBUG VARIABLES--');
playing_HTML::output('Driver:'.$ConInfo->db_driver);
playing_HTML::output('Host:'.$ConInfo->db_host);
playing_HTML::output('Port:'.$ConInfo->db_port);
playing_HTML::output('User:'.$ConInfo->db_user);
playing_HTML::output('Password:'.$ConInfo->db_password);
playing_HTML::output('Database:'.$ConInfo->db_database);
?>