5
\$\begingroup\$

I'm an absolute beginner in PHP OOP in search of the "Holy Grail" of connecting to MySQL database once and reusing this connection for the whole site.

classes/db.php

<?php
define('SERVER', 'localhost');
define('USERNAME', 'root');
define('PASSWORD', 'password');
define('DATABASE', 'cms');

class DB {
    function __construct(){
        $connection = @mysql_connect(SERVER, USERNAME, PASSWORD) or die('Connection error -> ' . mysql_error());
        mysql_select_db(DATABASE, $connection) or die('Database error -> ' . mysql_error());
    }
}
?>

classes/users.php

<?php
class Users {
    function __construct(){
        $db = new DB();
    }

    public function read(){
        $query = mysql_query("select use_id, use_name, use_email FROM users");
        while ($row = mysql_fetch_array($query)){
            $data[] = $row;
        }
        return $data;
    }
}
?>

users.php

<?php require_once('classes/db.php'); ?>
<?php require_once('classes/users.php'); ?>
<?php
$Users = new Users();
$users = $Users->read();
?>
<?php
if ($users) {
    echo '<ul>';
    foreach($users as $user){
        echo '<li><a href="mailto:' . $user['use_email'] . '">' . $user['use_name'] . '</a></li>';
    }
    echo '</ul>';   
}
?>

My doubts are mostly on the Users class part:

function __construct(){
    $db = new DB();
}

It seems to be an easy way to have the connection available, but I read somewhere that instantiate the db connection in the constructor is a bad idea. Can you explain why, and if there's a better way to have the db connection easily available in every class that needs it?

I read a similar question here, but I can't understand the abstraction/holding reference/singleton suggestion from the accepted answer, and the lack of a full practical example doesn't help me.

\$\endgroup\$
1
  • 1
    \$\begingroup\$ If possible, you should be using the PDO or mysqli extensions to avoid possible SQL injections. \$\endgroup\$
    – Bobby
    Nov 1 '11 at 12:57
3
\$\begingroup\$

A better practice is passing a created DB instance to the Users class. Imagine that you have a Products class, an Orders class etc. Will all of their constructors create a separate MySQL connection? It doesn't look a good idea since all of them could use the same database connection.

class Users {
    private $db;

    public function __construct($db) {
        $this->db = $db;
    }

    ....
}

class Products {
    private $db;

    public function __construct($db) {
        $this->db = $db;
    }

    ....
}

$db = new DB();
$users = new Users($db);
$products = new Products($db);
\$\endgroup\$
2
  • \$\begingroup\$ Thanks a lot, this solution seems easy to understand and implement! The last part of your post is not much clear to me ("If the type of the constructor parameter is an interface..."), please can you provide an example? \$\endgroup\$
    – Ivan
    Nov 2 '11 at 16:02
  • \$\begingroup\$ Good point :-). I've deleted this paragraph, it looks inappropriate in your case since the Users class use a lot of mysql functions. Here you can find a simple mocking example: java.dzone.com/news/a-simple-manual-mock-example \$\endgroup\$
    – palacsint
    Nov 2 '11 at 20:44
1
\$\begingroup\$

The thing is that it'd be ideal not to have to make a connection to the DB everytime you want to query something, instead you make the connection only once through a singleton class "connexion", and by doing that you have only one connection, because there'd exist only one instnace of the class connection

But having only one connection is not good either because sharing the same connection by multiple users can slow things down. In my opinion it'd be ok to have a number of available connections and use only those, not making a new connection everytime you want to query something, I guess this is called "connection pooling". Maybe you can implement a singleton class with an array of a fixed number of connections to the database available, say 20 so to speak.

But it depends on the configuration of the server running the php script it can be multithreaded or multiprocess. Here it's explained: Apache: multi-threaded vs multi-process (pre-forked) If your server is configured as multiprocess, it's the same to make the connection in the constructor of your class or in a different class.

if your server is configured as multithreaded it's better to have a singleton class. But I also read that php is not multithreaded safe.

\$\endgroup\$
1
  • \$\begingroup\$ Thanks, now I understand! At the moment I'm on IIS 7.5, so it's PHP thread safe \$\endgroup\$
    – Ivan
    Nov 2 '11 at 16:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.