MySQL database connection in the constructor

Question

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.

Answer 1

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);

Answer 2

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.