TheCodingBook
TheCodingBook

Reputation: 21

PHP Class - How to connect to the database only once

I tried to do a simple SQL class. There is just one problem:

function __classDBREAD($table, $where, $value, $back)
{
    $link = mysql_connect('127.0.0.1','XXXX','XXXXXX');
    mysql_select_db('XXXX', $link);
    mysql_set_charset('utf8');

    $sql = "SELECT * FROM $table WHERE $where = '$value' LIMIT 1";
    $result = mysql_query($sql, $link) or die(mysql_error());
    $row = mysql_fetch_assoc($result);
    return $row[$back];
    mysql_close($link);
}

Now, how can I connect to the SQL only once and add functions like "dbUpdate", "dbInsert" and "dbRead" without connecting to the database every time?

The same for the teamspeak connection.

Here an example:

require_once                      ("lib/TeamSpeak3/TeamSpeak3.php");
    $ts3_VirtualServer              = TeamSpeak3::factory("serverquery://XXXXX:[email protected]:10011/?server_port=XXXX");
    $__varTeamspeakClients          = $ts3_VirtualServer->clientList();
    $__intTeamspeakClientsOnline    = $ts3_VirtualServer["virtualserver_clientsonline"] - 1;
    $ts3_VirtualServer->request('clientupdate client_nickname='.$this->__classRndString(8));

The same problem. How to define the connection only once, when I include the Class to the page?

Upvotes: 1

Views: 6201

Answers (3)

Rasclatt
Rasclatt

Reputation: 12505

Since everyone is tossing their OOP into the ring (NOTE: I am going to do PDO because I know it better, but the principle is the same, just substitute the connection):

<?php
class DatabaseConnection
    {
        # Create a singleton to store the connection for reuse
        private static $singleton,
                       $con;
        # save connection to singleton and return itself (the full object)
        public function __construct()
           {
                # If your singleton is not set
                if(!isset(self::$singleton))
                    # assign it this class
                    self::$singleton = $this;
                # return this class
                return self::$singleton;
           }
        # This is a connection method because your __construct
        # is not able to return the $pdo connection
        public function connection($host='hostname',$username='username',$password='password',$database='database')
            {
                # In the connection, you can assign the PDO to a static
                # variable to send it back if it's already set
                if(self::$con instanceof \PDO)
                    return self::$con;
                # If not already a PDO connection, try and make one
                try {
                        # PDO settings you can apply at connection time
                        $opts = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,PDO::ATTR_EMULATE_PREPARES => false);
                        # Assign your PDO Conneciton here.
                        self::$con =  new PDO("mysql:host={$host};dbname={$database}",$username,$password,$opts);
                        # Return the connection
                        return self::$con;
                    }
                catch (PDOException $e){
                        exit('Database error');
                    }   
            }
    }

You can use it anywhere, in and outside of your functions, classes, you name it. It will return the same connection every time. If you use spl_autoload_register() with an autoload function you will be all set and not even have to explicitly use the require_once() every time!

<?php
require_once('class.DatabaseConnection.php');
// Instantiate connection class
$pdo = new DatabaseConnection();
// Assign the connection to $con (or whatever variable you like)
$con = $pdo->connection();

One note of caution, singleton patterns are generally used minimally. Often it is better to use new Objects() without assigning it a singleton. I personally like the singleton for this use however.



SIMPLE DEMONSTRATION - Consider this scenario:

<?php
// Here is an example of the singlton with an echo
// which shows that the class is returning itself
// like a global
class DatabaseConnection
    {
        private static $singleton;
        public function __construct()
           {
                // If your singleton is not set
                if(!isset(self::$singleton)) {
                        echo 'NEW Object'.PHP_EOL;
                        // assign it this class
                        self::$singleton = $this;
                    }
                else
                    echo 'SAME Object'.PHP_EOL;
                // return this class
                return self::$singleton;
           }
    }

function ReturnConnection()
    {
        return new DatabaseConnection();
    }

class TestClass
    {
        public  function __construct()
            {
                new DatabaseConnection();
            }
    }

function query($sql=false)
    {
        return ReturnConnection();
    }

// The connection class wrapped in a function 
$a = ReturnConnection();
// The function nested inside a class
$b = new TestClass();
// The function nested inside another function
$c = query();

The above uses of the singleton will produce:

NEW Object
SAME Object
SAME Object

Upvotes: 4

I wrestled a bear once.
I wrestled a bear once.

Reputation: 23379

First of all, you should not be using the mysql_* functions at all. They're deprecated and dangerous to use. Buth hypthetically, you could use globals.

Define your connection outside of the function (in the global scope) then use global to give your function acces to it. Now all you have to do is include that one "global" line in all your functions that need it.

$link = mysql_connect('127.0.0.1','XXXX','XXXXXX');

function __classDBREAD($table, $where, $value, $back)
{
    global $link;
    mysql_select_db('XXXX', $link);
    mysql_set_charset('utf8');

    $sql = "SELECT * FROM $table WHERE $where = '$value' LIMIT 1";
    $result = mysql_query($sql, $link) or die(mysql_error());
    $row = mysql_fetch_assoc($result);
    return $row[$back];
    mysqli_close($link);
}

EDIT...

I didn't read this too closely. I see you're working on a class, so globals is not the best option. Consider this..

class mydbclassthing {
    private $conn;
    public function __construct(){
        $this->conn = mysql_connect('127.0.0.1','XXXX','XXXXXX');
    }

    function __classDBREAD($table, $where, $value, $back)
    {
        $link = $this->con;
        mysql_select_db('XXXX', $link);
        mysql_set_charset('utf8');

        $sql = "SELECT * FROM $table WHERE $where = '$value' LIMIT 1";
        $result = mysql_query($sql, $link) or die(mysql_error());
        $row = mysql_fetch_assoc($result);
        return $row[$back];
        mysql_close($link);
    }
}

Upvotes: 2

Juan Bonnett
Juan Bonnett

Reputation: 1853

As the PHP documentation says about Persistent Database Connections (http://php.net/manual/en/features.persistent-connections.php):

an instance of the PHP interpreter is created and destroyed for every page request (for a PHP page) to your web server. Because it is destroyed after every request, any resources that it acquires (such as a link to an SQL database server) are closed when it is destroyed. In this case, you do not gain anything from trying to use persistent connections -- they simply don't persist.

If what you want is to re-use code more eficiently, doing it the OOP way is the key.

I might think about a better solution later, but what I'd do is to use class members to store links to SQL, so they don't have to be created everytime:

class MySQL {

    protected $mysql_link;
    protected $server = '127.0.0.1'; //and so on...

    public function __construct() {
       $this->link = mysql_connect($this->server,$this->user,$this->password);
       mysql_select_db($this->selected_db, $this->link);
       mysql_set_charset('utf8');
    }

    public function link() {
        return $this->link;
    }

    public function close() {
        return mysql_close($this->link);
    }

}

Now, using OOP in the rest of your application (something like dependency injection) would be awesome, but if not, anyway, you can always instantiate the class you're creating and even store that object in a $_SESSION variable.

Using the $object->link() will always return that instance's link, instead of creating new ones everytime you need to execute any Query,

Upvotes: 0

Related Questions