Profiler and Multiple DB's

[eluser]dbrown75[/eluser]
Not sure what I am doing incorrect, but I have multiple databases that I connect to and only the default is showing in the Profiler output.

I have a model with multiple functions, each making connections to my second database.

I connect to my second database like :

$second_database = $this->load->database('database_name', true);

then I run 2 queries per function, but they do not show in the Profiler output.

I have played around with the syntax :

$CI =& get_instance();
$CI->$second_database = $this->load->database('database_name', true);

and can get the second database to show, but it then only shows the last functions queries and not all queries that were ran?

I'm still a bit green with CI and php, so suggestions welcome.

[eluser]Thorpe Obazee[/eluser]
It is not the database name that you should use as the first parameter.

Code:

$DB1 = $this->load->database('group_one', TRUE);
$DB2 = $this->load->database('group_two', TRUE);

[eluser]dbrown75[/eluser]
Sorry I am using the group name.

I do have two databases defined in my database.php:

$db['default']['database'] = 'first_database';

$db['second_database']['database'] = 'second_database';

and am calling as :

$second_database = $this->load->database(‘second_database’, true);

[eluser]TheFuzzy0ne[/eluser]
Please paste the contents of ./system/application/config/database.php after you've removed any sensitive information from it, such as your usernames and passwords.

[eluser]dbrown75[/eluser]
<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
/*
| -------------------------------------------------------------------
| DATABASE CONNECTIVITY SETTINGS
| -------------------------------------------------------------------
| This file will contain the settings needed to access your database.
|
| For complete instructions please consult the "Database Connection"
| page of the User Guide.
|
| -------------------------------------------------------------------
| EXPLANATION OF VARIABLES
| -------------------------------------------------------------------
|
| ['hostname'] The hostname of your database server.
| ['username'] The username used to connect to the database
| ['password'] The password used to connect to the database
| ['database'] The name of the database you want to connect to
| ['dbdriver'] The database type. ie: mysql. Currently supported:
mysql, mysqli, postgre, odbc, mssql
| ['dbprefix'] You can add an optional prefix, which will be added
| to the table name when using the Active Record class
| ['pconnect'] TRUE/FALSE - Whether to use a persistent connection
| ['db_debug'] TRUE/FALSE - Whether database errors should be displayed.
| ['cache_on'] TRUE/FALSE - Enables/disables query caching
| ['cachedir'] The path to the folder where cache files should be stored
| ['char_set'] The character set used in communicating with the database
| ['dbcollat'] The character collation used in communicating with the database
|
| The $active_group variable lets you choose which connection group to
| make active. By default there is only one group (the "default" group).
|
| The $active_record variables lets you determine whether or not to load
| the active record class
*/

$active_group = 'default';
$active_record = TRUE;

require_once('db-routing.php');

// Default database connection
$db['default']['hostname'] = MYSQL_HOST;
$db['default']['username'] = 'user';
$db['default']['password'] = 'password';
$db['default']['database'] = 'main_database';
$db['default']['dbdriver'] = 'mysql';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';

// The second_database connection
$db['second_database']['hostname'] = MYSQL_HOST;
$db['second_database']['username'] = 'user';
$db['second_database']['password'] = 'password';
$db['second_database']['database'] = 'second_database';
$db['second_database']['dbdriver'] = 'mysql';
$db['second_database']['dbprefix'] = '';
$db['second_database']['pconnect'] = TRUE;
$db['second_database']['db_debug'] = TRUE;
$db['second_database']['cache_on'] = FALSE;
$db['second_database']['cachedir'] = '';
$db['second_database']['char_set'] = 'utf8';
$db['second_database']['dbcollat'] = 'utf8_general_ci';

?>

[eluser]TheFuzzy0ne[/eluser]
OK, and the code you're using to instantiate both of these?

[eluser]dbrown75[/eluser]
I'm just staring out on building the code, so not accessing the default database yet.

I have a model that has multiple functions like :

public function get_data()
{

$second_database = $this->load->database('second_database', true);
$second_database->query('SET NAMES latin1');
$second_database->select("wp_posts.post_date 'date', wp_posts.post_title 'title', wp_posts.post_excerpt 'content', wp_posts.post_name 'slug', wp_users.display_name 'author'");
$second_database->join('wp_users', 'wp_users.ID = wp_posts.post_author', 'left');
$second_database->join('wp_post2cat', 'wp_post2cat.post_id = wp_posts.id', 'left');
$second_database->where('wp_post2cat.category_id = 71');
$second_database->where('wp_posts.gt_status = 1');
$second_database->where("wp_posts.post_status = 'publish'");
$second_database->where("wp_posts.post_date <= NOW()");
$second_database->orderby('wp_posts.post_date', 'DESC');
$query = $second_database->get('wp_posts', 1);

if ($results = $query->result())
{
$post = $results[0];

$post->permalink = 'http://www.mywebsite.com/'
. date('Y', strtotime($post->date)) . '/'
. date('n', strtotime($post->date)) . '/'
. date('j', strtotime($post->date)) . '/'
. $post->slug . '/';

$post->content = strip_tags(trim($post->content), '<img>');
$post->content = nl2br($post->content);
$post->content = strip_tags(trim($post->content), '<img>');

return $post;
}
else
{
return false;
}
}

using the above code, only the default shows up with zero queries which is right as I'm not hitting the default yet, but shows nothing for the second_database.

If I add $CI =& get_instance(); and switch $second_database over to $CI->$second_database, I can get the last functions queries I call to show up but not all of them. That is I have 3 functions each that call the second_database, all using the above syntax, so I'd expect to see the query count at 6, not 2. The queries between the function a differ, in that one looks for category_id = 71, the other looks for category_id = 69 and the third looks for the most recent and doesn't filter by category_id.

[eluser]TheFuzzy0ne[/eluser]
Looks like the profiler will only work on database objects that are instantiated within the global scope of the CodeIgniter super object.

You might want to try something like this:

Code:

$this->db2 =& $this->load->database('db2');

Any models that need to use it can do so, you'd just need to assign the database variable by reference.

Code:

$this->load->model('my_model');
$this->my_model->db =& $this->db2;

# Now the model will use db2 as it's default database.

although in most cases, this will be done for you automatically.

[eluser]dbrown75[/eluser]
Thanks. This gave me enough to get it working. Greatly appreciated.