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.