0

I am trying to write some code with multiple classes which will have a lot of DB connections/queries later.

My question is does PDO connect to database as soon as the following line is executed and it will stay connected until it is closed?

$pdo = new PDO($dsn, $user, $pass, $options);

Or it waits until we do the following to connect, for example:

$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?');
$stmt->execute([$email]);
$user = $stmt->fetch();

// Closing connection
$stmt = null;

If it isn't connected immediately when we do new PDO() then I can easily make the $pdo variable global and use it in every class, otherwise I have to connect in each class separately with __construct() method and then close connection on __destruct() method. (I'm still not sure if this is the best way to go for better performance)

P.S. Do we also need to set $pdo = null to close the connection? or $stmt = null is enough?

J. Doe
  • 722
  • 6
  • 24
  • 2
    `Upon successful connection to the database, an instance of the PDO class is returned to your script. The connection remains active for the lifetime of that PDO object. To close the connection, you need to destroy the object by ensuring that all remaining references to it are deleted—you do this by assigning NULL to the variable that holds the object. If you don't do this explicitly, PHP will automatically close the connection when your script ends.`https://www.php.net/manual/en/pdo.connections.php – user3783243 Apr 08 '19 at 00:21
  • 1
    _"I'm still not sure if this is the best way to go for better performance"_ it is not. Pass the connection to your class constructors as a dependency – Phil Apr 08 '19 at 00:43
  • @Phil Could you kindly provide an example please? – J. Doe Apr 08 '19 at 01:24
  • 2
    https://phpdelusions.net/pdo/pdo_wrapper#dependency_injection – Phil Apr 08 '19 at 01:35

1 Answers1

1

When you instantiate new PDO() it will try to persist a connection to the database.

Connections are established by creating instances of the PDO base class.

Source

I would suggest to avoid using global variables unless your codebase is relatively small. You'll be sharing your database instance where it shouldn't be shared!

Perhaps we can produce a singleton class to get your database connection. This is so that we can get a single instance of the connection and do all the work needed before closing the connection, once your database work is complete.

It would be nice to close the database connection early. However, PHP does it for you after the script has finished executing.

If you don't do this explicitly, PHP will automatically close the connection when your script ends.

Source

I hope this helps!

Johnny
  • 156
  • 3
  • 6
  • 2
    There are some grave problems with your singleton example, namely unacceptable error reporting and wrong charset handling. I would suggest to find another one. Let alone singleton is a questionable solution itself, having same problems as global variables – Your Common Sense Apr 08 '19 at 03:38
  • Ahh, I'll happily remove it for now, thanks for the info! What would be your suggestion? – Johnny Apr 08 '19 at 04:09
  • @YourCommonSense What is the best approach in your opinion for this matter? (connecting to database in multiple classes) , I was thinking of connecting to database with PDO in `__construct()` method of every class and then closing connection with `__destruct()` method. do you think that is acceptable and a good practice? _Because passing the connection to classes will keep the connection active until the execution is finished (which I think is bad performance-wise)_ – J. Doe Apr 08 '19 at 12:41
  • 1
    @J.Doe Premature optimization is the root of all evil and your assumptions is a perfect example of this rule. In an attempt to avoid an imaginable(!) performance issue, you are about to ruin the performance in reality. Because it's connecting in the every class will make sure your database server is dead. So, text time try to improve the performance *only* if you have a real issue, not an imaginary one. – Your Common Sense Apr 08 '19 at 12:59
  • @YourCommonSense Thank you very much for the clarification, I learned a lot! Could you kindly approve if using the following method is okay with performance in mind: `Static solution for the Object Oriented Code` https://phpdelusions.net/pdo/pdo_wrapper#static_instance (website will have 500/600 concurrent users with ~1000 queries per second) – J. Doe Apr 08 '19 at 13:20
  • 1
    @J.Doe Being, incidentally, the author of the page you linked, I can only repeat what it said there - it's a tradeoff that could be used if your code is poorly organized. Nevertheless performance wise it's the same as Dependency injection. As long as you keep a single connection, all methods are the same. – Your Common Sense Apr 08 '19 at 13:27
  • @YourCommonSense You are awesome, thank you for all the useful notes and amazing website! – J. Doe Apr 08 '19 at 14:11
  • 2
    @J.Doe to elaborate on what I said before. There are many drawbacks in connecting every class. You cannot use transactions or other connection-bound features between classes. Connection is relatively slow operation, so the more you have the poorer the performance is. And of course the infamous too many connections error. a typical app could have hundreds or thousands objects and it means hundreds or thousands connections opened – Your Common Sense Apr 08 '19 at 14:28