My web project has a class called DB_CONNECTOR where all the functions are bundled that interact with the mysql database. These would be functions like get_user(), add_user(), change_user_attribute() and many more. In each of these functions a sql query is executed and as is good practice, I use prepared statements (with named parameters).
Currently the connection to the database is established in the constructor of the class and also all the statements are prepared there. I thought this would be a good idea, so the statements are all immediatly ready for execution.
Now I realized that my use case is often to create a db_connector object, execute one or maybe two functions and then the objects lifecycle ends and at a later step a new one might be constructed (or not). So, I'm not so sure anymore if it smart to put the prepared statements in the constructor as it is forseeable that I will end up with at least 20 or likely more prepared statements.
So my question is:
This answer is based in both my experience and humble opinion, but I'll try to elaborate my arguments so it isn't just some random guy's opinion.
I don't think the database connection must be the core object in your application, let alone the only one. It'd expect to see an entirely different class for the user, so you can later have further classes for everything else. Otherwise, your application will eventually consist of a single class in a 5000 line file and your class will not be suitable to track entity data at instance level and you'll need to pass variables around in method calls. That's pretty much procedural code in OOP dress.
Also, I don't that making your User class inherits from Database (something pretty frequent nonetheless) is practical at all. Interlacing the database connection and the business logic objects doesn't really simplify application design and actually makes some parts harder.
The design of the database layer itself is pretty much standardised:
This is exactly how PDO works.
Given that, it's easier to make the database classes just one more dependency of your entities rather than their grandparent. The injection of this dependency can be done by different means:
Make it a class property:
public function __construct(\PDO $connection)
{
$this->connection = $connection;
}
Pass it to the methods they actually needed (if not many of them):
public function getOrders(\PDO $connection)
{
$stmt = $connection->prepare('SELECT ...');
}
... or use one of those fancy dependency injection containers you can find at Packagist.
Be aware that there're also object-relational mapping (ORM), active record pattern... Those are entirely different families of solutions which may suit your needs or not depending on your use case, but not what I'm describing here.
Said that, it becomes obvious that you prepare the statements at the exact point where you need them. This design doesn't even allow otherwise ;-)