Calling $this->load->database() multiple times

[eluser]TheBenno[/eluser]
I have read the user-guide thoroughly, but I did not see an answer to my question: Is it acceptable to call $this->load->database() multiple times in a single page load? I called it twice in a row like so:

Code:

$a = $this->load->database('default', TRUE);
$b = $this->load->database('default', TRUE);

and $a == $b but $a !== $b. It would be extremely bad if calling it twice in a row creates 2 connections to the database, as you will very quickly get "Too Many Connection" errors from your DB unnecessarily.


The reason I ask is: not all of my pages use all of my databases. I would like to connect only the the databases I need for each specific page load. I feel the best way to do this would be to connect to the a database within the model-methods which requires that database. That way i don't need to list out the databases at the top of each controller method, instead i just call the model methods i need, and they connect to the appropriate databases lazily (like MDB2 is capable of doing with its singleton() method). My worry is that if i do it this way I will create many more than 1 connection to the same database per page load, when really the connection could be cached. I could always build an MDB2 library into codeigniter, but I would prefer to use their database libraries.


Can anyone help?


Thanks!

[eluser]danmontgomery[/eluser]
You can echo the resource_id to see that it is creating separate connections:

Code:

$a = $this->load->database('default', TRUE);
$b = $this->load->database('default', TRUE);
echo $a->conn_id.'<br/>'.$b->conn_id;

The more important question is, why would you want to make separate connections to the same database?

[eluser]WanWizard[/eluser]
This example isn't very useful, because that would indeed create two database connections to the same database.

It's better to create a separate config for each of your databases, only load your default database 'by default', and load the others as needed.
See Database configuration and Database configuration in the manual.

[eluser]TheBenno[/eluser]
Thanks for the response, but I don't think you guys are understanding my problem. I tried to make my example super simple but I guess I will need to use a more complex one to illustrate:

I have a view which gets a user and gets the user's friends. Lets say that the user table is on db "USER" and the friends table is on db "FRIENDS" (on separate machines in the interest of this example). My view goes like this:

Code:

profile($uid) {
    $user = $this->User_model->get_user_by_uid($uid);
    $friends = $this->User_model->get_friends_for_uid($uid);
    //Display view with that data
}
//User Model:
get_user_by_uid($uid) {
    $user = $this->get_cached_user($uid);
    if ($user === False) {
        $sql = 'SELECT * FROM user WHERE uid = ?';
        $udb = $this->load->database('user', TRUE);
        $user = $udb->query($sql, array($uid))->row_array();
    }
    return $user;
}

get_friends_for_uid($uid) {
    $friends = $this->get_cached_friends($uid);
    if ($friends === False) {
        $sql = 'SELECT * FROM friends WHERE uid = ?';
        $fdb = $this->load->database('friends', TRUE);
        $friends = $fdb->query($sql, array($uid))->result_array();
    }
    return $friends;
}

So basically I DON'T want to connect to any databases unnecessarily (see MDB2 and it's singleton() method for a great example of how this can be done) and more often then not i will NOT need to connect to any databases. I want to connect to my databases lazily. Is there some way that I wrap "load->database()" to check to see if an existing connection exists so that every call to load->database returns the singleton instance? I could build this functionality into my models, but potentially I could connect to the same database in multiple models, so I really need the connection cacheing/singleton statically throughout the runtime of the script.

Thanks

[eluser]WanWizard[/eluser]
I understand perfectly what you mean, but for me it doesn't make sense to use database connections like this.

I do you want this, I suggest extending the Model class, and use class variables. Then do:

Code:

//User Model:
get_user_by_uid($uid) {
    $user = $this->get_cached_user($uid);
    if ($user === False) {
        $sql = 'SELECT * FROM user WHERE uid = ?';
        if ( ! $this->udb )
        {
            $this->udb = $this->load->database('user', TRUE);
        }
        $user = $this->udb->query($sql, array($uid))->row_array();
    }
    return $user;
}

[eluser]TheBenno[/eluser]
I understand that solution, and it works on a per model basis, but does not work if more than one model need to connect to that database. Is there some way I could store a global array of already connected databases, so that I could test:

Code:

$udb = $this->connected_databases['user'];
if(!$udb)
    $udb = $this->load->database('user', TRUE);

I am surprised that you don't see this as a major limitation. In my experience scaling up websites, the first thing to die when a huge traffic spike hits is MySQL "Too Many Connections". Memcached can help alleviate a lot of that headache, but it can also help to NOT make unnecessary connections to your database. Many of a site's ajax calls may hit dbA and dbB but not dbC, and if memcache gets all hits, it could not connect to any database at all! Huge win. If no solution exists I will have to build my own 'library' class which simply wraps MDB2, a sqldb-api which supports this feature.

[eluser]WanWizard[/eluser]
I don't have my data distributed over lots of databases, so I don't have this issue.

If you can come up with a standard for naming your database objects, you could manually assign them to the CI superobject:

Code:

//User Model:
get_user_by_uid($uid) {
    $user = $this->get_cached_user($uid);
    if ($user === False) {
        $sql = 'SELECT * FROM user WHERE uid = ?';
        if ( ! isset($this->CI->udb) )
        {
            $this->CI->udb = $this->load->database('user', TRUE);
        }
        $user = $this->CI->udb->query($sql, array($uid))->row_array();
    }
    return $user;
}

You can extend the Model library, and have the constructor of MY_Model create the $this->CI variable.

Alternatively you can extend the Loader class, and create your version of the database() method.

You have to figure out a way to determine if a config is already loaded. One way of doing that is to use the config name in the CI variable. Currently, $this->db is created hardcoded. You could change this so that this is the behaviour for 'default', while p.e. loading the database with the 'user' config creates $this->db_user. Then in the database() method you could check if the object already exists, and only intantiate it if it doesn't.

[eluser]nil_php[/eluser]
The solution is-

There is need to write $this->load->database('default', TRUE); sentence in every function you have to just write it in constructor.

Make sure to set your db pconnect to FALSE if you are going to connect to multiple databases.

$db[‘master’][‘pconnect’] = FALSE;

$db[‘slave’][‘pconnect’] = FALSE;


Please check the link
http://www.adroitcoders.com/blog/2010/06...ing-mysql/

[eluser]mddd[/eluser]
You could make a simple library to deal with your database connections. That would be a "place to go" for your application if it needs a certain connection and to check if it already exists. This will save you from checking in all your methods separately.

This is really the same idea of WanWizard's point (extending the loader class) but instead of extending that, we add a layer in between that holds the databases you already connected to.

Code:

// in a model:
get_user_by_uid($uid) {
    $user = $this->get_cached_user($uid);
    if ($user === False) {
        $sql = 'SELECT * FROM user WHERE uid = ?';
        // get a connection to the user db, whether it already exists or not
        $udb = $this->db_manager->get_connection('user');
        $user = $udb->query($sql, array($uid))->row_array();
    }
    return $user;
}

// in the 'db_manager' library:
class Db_manager
{
    var $connections = array();
    var $CI;

    function __construct()
    {
        $this->CI =& get_instance();
    }

    function get_connection($db_name)
    {
        // connection exists? return it
        if (isset($this->connections[$db_name]))
        {
            return $this->connections[$db_name];
       }
       else
       {
            // create connection. return it.
            $this->connections[$db_name] = $this->CI->load->database($db_name, true);
            return $this->connections[$db_name];
        }
    }
}

[eluser]TheBenno[/eluser]
Great answer mddd. Your solution is in fact basically the exact solution that I am currently using quite happily. It may be interesting to note that I am actually using the MDB2 framework with its singleton() method. It may be totally unnecessary given this Db_manager solution, however I ended up doing this because I simply could not get CI's database to return the unicode characters stored in my database. I have utf8_unicode_ci as the collation at every level in my DB, and in my CI database config file i have utf8_unicode_ci set as the collation as well. No idea what i was doing wrong, but since MDB2 just worked out of the box, it was easy to have my Db_manager class use MDB2 connections instead of CI-Database connections.

Regardless of that tangent i just went off on: mddd's solution is the one I built (couple weeks ago actually, but mddd's example is very elegant) and solves this problem quite well. nil_php's solution looks like it could work, but i'm not exactly sure what 'pconnect' does. Does it lazily connect to the database? The documentation simply states: "Whether to use a persistent connection", but I DO want a persistent connection! If i do need to hit the database a few times in a page load, that better not open 3 connections to mysql!!

Anyway, thanks for the help guys! Now I'm painfully trying to tackle nested views. I am opening a new thread for my inquiry.