Use one pdo connection across multiple modules?

Question

I have multiple modules on the same page that are individually connecting to the same external database through pdo connections. The website is getting slower since I added more modules. My guess is that connecting multiple times to the same external db is slowing it down.

Now I want to connect one time to the external db and use this connection in all modules. How do I do this? I also want the website to be able to update without removing my code.

I like to use normal php query instead of the joomla query stuff, that is why I did not open a new connection to the external database with JFactory.

Answer 1

There are a bunch of ways to achieve what you're after - the following is merely one idea. Instead of writing your own singleton you can attached an object with your db connection (and perhaps a bunch of your db code) to one of the available singletons that you already have access to via the factory. The following code would exist in your module's entry file and the additional class would be in a file in the same directory.

// Grab a copy of the application object
$app = JFactory::getApplication();
// Check if we've already added our extra object
if(!isset($app->extraDB)){
    require_once 'ExtraDB.php'; // include the file with a class with all our stuff in it
    $app->extraDB = new ExtraDB; // Piggyback our new db object onto the application
}
// the same object is now available to all modules containing this code
echo $app->extraDB->example();

Answer 2

One answer to this question is

Creating a single library which all modules can access.

Create you own php classes. Add them in you own custom library folder inside /libraries of your joomla installation.

Classes in a folder that follow a naming convention can be registered collectively with JLoader::discover() method. The discover method looks at the file names in a folder and registers classes based on those names.

Then you will need a loader to load your library class files. This can be done via

1. Discovering your library

// Register all files in the /libraries/mylib folder as classes with a name like: MyLib JLoader::discover('Mylib', JPATH_LIBRARIES . '/mylib');

2. Prefixing your class names to make them discoverable easily.

// Tell the auto-loader to look for classes starting with "Mylib" in a specific folder. JLoader::registerPrefix('Mylib', JPATH_LIBRARIES . '/mylib');

For single database object on all modules, your database class should follow singleton pattern.

Singletons are designed to ensure there is a single (hence the name singleton) class instance and that is global point of access for it, along with this single instance we have global access and lazy initialization.

A example of DB class using singleton pattern here:

<?php
/**
* PDO Singleton Class v.1.0
*/
class DB {
    protected static $instance;
    protected function __construct() {}
    public static function getInstance() {
        if(empty(self::$instance)) {
            $db_info = array(
                "db_host" => "localhost",
                "db_port" => "3306",
                "db_user" => "user",
                "db_pass" => "pass",
                "db_name" => "ftonato",
                "db_charset" => "UTF-8");
            try {
                self::$instance = new PDO("mysql:host=".$db_info['db_host'].';port='.$db_info['db_port'].';dbname='.$db_info['db_name'], $db_info['db_user'], $db_info['db_pass']);
                self::$instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);  
                self::$instance->query('SET NAMES utf8');
                self::$instance->query('SET CHARACTER SET utf8');
            } catch(PDOException $error) {
                echo $error->getMessage();
            }
        }
        return self::$instance;
    }
    public static function setCharsetEncoding() {
        if (self::$instance == null) {
            self::connect();
        }
        self::$instance->exec(
            "SET NAMES 'utf8';
            SET character_set_connection=utf8;
            SET character_set_client=utf8;
            SET character_set_results=utf8");
    }
}
?>

It can now be used as follows:

<?php
require_once ('database.class.php');
try {
    $db = DB::getInstance();
    DB::setCharsetEncoding();
    $sqlExample = 'SELECT * FROM users WHERE _id = 1';
    $stm = $db->prepare($sqlExample);
    $stm->execute();
    return $stm->fetchAll(PDO::FETCH_ASSOC);

} catch (Exception $e) {
    print $e->getMessage();

}