Welcome Guest, Not a member yet? Register   Sign In
how do I cache multiple databases (dynamic query)

(This post was last modified: 07-05-2018, 07:31 AM by admin0.)


I have 4 databases in the config. 

my model is something like: 

PHP Code:
function get_total($dbkey){
// this $dbkey is the additioanl databases specified in the config 

$thisdb $this->load->database($dbkeyTRUE);
$query $thisdb->query("select count(*) as total from dbname.tablename"); 

and from the controller, I do something like 

PHP Code:

$db3total_users $this->mydb->get_total("db3"):
$db4total_users $this->mydb->get_total("db4"):
$db5total_users $this->mydb->get_total("db5"): 

This works fine . with this I am able to connect to 4 additional database and pull all info @ once. 

The issue here is that the $thisdb->cache_on();  on the model is not working. The connection takes the same amount of time even if that line was not there.. and then, I do not see any pages being created on application/cache 

another thing to note, the profiler only shows the main $db query .. for the additional ones, it does not.

so how would I do db query cache here ? 



This statement does not look right:
CURRENT: $query = $thisdb->query("select count(*) as total from dbname.tablename");
SHOULD BE: $query = $this->db->query("select count(*) as total from dbname.tablename");

(07-05-2018, 08:34 AM)php_rocs Wrote: @admin0,

This statement does not look right:
CURRENT: $query = $thisdb->query("select count(*) as total from dbname.tablename");
SHOULD BE: $query = $this->db->query("select count(*) as total from dbname.tablename");

$this->db . -- is for the default db 
i use the default db for only login and sessions. 

$thisdb is from the model where I pass the db as argument and thus a single model/function allows me to query multiple identical databases in the fly. 

the query/model is working fine .. just caching is not .. unless caching is only for the default db and not for extra dbs


Caching should work fine unless there is something not declared correctly.
How do you declare the databases in the config file?


Looking at the setup steps - is the folder writable?

You should provide the second database information in `application/config/database.php´

Normally, you would set the default database group, like so:

$db['default']['hostname'] = "localhost";
$db['default']['username'] = "root";
$db['default']['password'] = "";
$db['default']['database'] = "database_name";
$db['default']['dbdriver'] = "mysql";
$db['default']['dbprefix'] = "";
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = FALSE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = "";
$db['default']['char_set'] = "utf8";
$db['default']['dbcollat'] = "utf8_general_ci";
$db['default']['swap_pre'] = "";
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;

1) Create a writable directory on your server where the cache files can be stored.
2) Set the path to your cache folder in your application/config/database.php file. Set "cachedir" parameter in db config
3) Enable the caching feature, either globally by setting the preference in your application/config/database.php file, or manually as described below.

$db['db2'] = array(
'dsn' => '',
'hostname' => 'localhost',
'username' => 'root',
'password' => '',
'database' => 'tickpay',
'dbdriver' => 'mysqli',
'dbprefix' => '',
'pconnect' => FALSE,
'db_debug' => (ENVIRONMENT !== 'production'),
'cache_on' => TRUE,
'cachedir' => 'application/cache',
'char_set' => 'utf8',
'dbcollat' => 'utf8_general_ci',
'swap_pre' => '',
'encrypt' => FALSE,
'compress' => FALSE,
'stricton' => FALSE,
'failover' => array(),
'save_queries' => TRUE

Suggestion: don't use caching if you don't fully understand how it works.

CI's cache system works by creating a folder for the controller+method and is a very strange implementation imo. Especially if you have multiple models used across different controllers. This isn't so much of an issue if your data doesn't change that often - but if it does change through CRUD functions you'll have to make some house keeping functions to delete the cache files associated with all the controller+methods that query a specific table to which the CRUD operations were performed on; or delete all the cache files (inefficient).

Why the cache system doesn't work by making folders per database table is beyond me.

Just thought I'd bring your attention to these factors.

Theme © iAndrew 2016 - Forum software by © MyBB