Using different mysql users within same class while maintaining same connection?

Question

I'm in a situation where I need to use MYSQL's LAST_INSERT_ID(), which is obviously only possible when maintaining the same connection. But as I was thinking about the way I establish my PDO connections, it occurred to me that I am probably going to run into problems.

I've got a database class with various types of connections. Inside this class, I've created different PDO connection methods based on user permissions which I call inside the PDO query methods like so...

class dbFunctions {

    private $userSelect = "userSelect";
    private $passSelect = "XXXXX";
    private $userDelete = "userDelete";
    private $passDelete = "XXXXX";

    protected function connectSelect() {
        $dsn = $this->dsn();
        $this->pdo = new PDO($dsn, $this->userSelect, $this->passSelect, $this->options);

        return $this->pdo;
    }

    protected function connectDelete() {
        $dsn = $this->dsn();
        $this->pdo = new PDO($dsn, $this->userDelete, $this->passDelete, $this->options);

        return $this->pdo;
    }

    public function selectCount($query, $values = []) {

        $result = $this->connectSelect()->prepare($query);
        $result->execute($values);
        $exec = $result->fetch();

        $count = (int)$exec['total'];

        return $count;
    }
}

My questions are...

• Am I better off creating one global connection object with the same account/password for all queries? I did it this way for security purposes but I believe it may have drawbacks.
• Even though these are different PDO connection objects, is PHP able to determine they are part of the same session within the same script? In which case the separate methods will be ok.
• How can I keep separate mysql users with my current class?

Obviously I can test this on my own, but I'm unsure what the results will be when I get multiple users accessing the site.

Answer 1

The best is to have only one DB connection per page. Maybe by using a singleton.

First for performance, because a connection have a cost.

In my opinion, there isn't any security issue.

Answer 2

• Am I better off creating one global connection object with the same account/password for all queries? I did it this way for security purposes but I believe it may have drawbacks.

I'm no security expert. I think that if I can manage mysql users and permissions in a way that only some human accounts can delete stuff, that's some kind of security. I also think this has many complex ramifications. On the other hand, people can update to a blank string if need be...

• Even though these are different PDO connection objects, is PHP able to determine they are part of the same session within the same script? In which case the separate methods will be ok.

PHP objects do not seem to be persistent. We can serialize them and use __sleep() to manage their storage.

It [sleep] can clean up the object and is supposed to return an array with the names of all variables of that object that should be serialized.

this part might interest you:

The intended use of __wakeup() is to reestablish any database connections that may have been lost during serialization and perform other reinitialization tasks.

• How can I keep separate mysql users with my current class?

With the same pattern, but make sure not to create pdo instances for each select or delete query. I tweaked it a bit to reuse the same function for both connections. Adding more is as easy as... naming new class members.

class dbFunctions {

    // other members

    // add these
    private $instanceSelect = null; // they default to null anyway
    private $instanceDelete = null;
    private $instanceFoosball = null;

    protected function connect($what) {
        if ($this->{"instance".$what} === null) { // true the first time
            $this->{"instance".$what} = new PDO($this->dsn(), $this->{"user".$what}, $this->{"pass".$what}, $this->options);
        }
        return $this->{"instance".$what}; // always reused
    }

    public function selectCount($query, $values = []) {}
}

$result = $this->connect('Select')->prepare($query);
$result = $this->connect('Foosball')->prepare($query);