What's a good way to make a PHP website approach the database object oriented?
mysql, oop, php
Solution
It's a common thing to have a separate class to handle SQL queries and to keep the fetched data. In fact, it is the real application of the Single Responsibility Principle.
What I usually do is keep a class with all the information concerning the data, in your case the User class, with all the user information as fields.
Then comes the business layer, for instance UserDataManager (though the use of "Manager" as a suffix is not recommended and you'd better find a more suitable name in each scenario) which takes the pdo object in its constructor to avoid use of global variables and has all the SQL methods. You'd thus have methods registerNewUser, findUserById, unsuscribeUser and so on (the use of "User" in the method can be implied by the class name and be omitted).
Hope it helps.
Problem
Please note I'm not looking for 'use a framework' answers. I'm trying to structurally improve the way I code websites and approach databases from PHP. I'm building a web service from scratch, without any frameworks. I'm using a LAMP stack and am trying to learn a bit of PHP's OO functionality while I'm at it. I've previously only used OO to make mobile apps. I've been at it for months now (as planned, no worries). Along the way I've bumped into a couple of structural problems, making me wonder what the best way would be to make the code object oriented. Pretty much all of the problems involve the database in some way. Say we have a class `DB` and a class `User`. In most cases I only need to fetch a single user's information from the database. I thought a good way to handle it was to have a global `$_db` variable and have the `User` object query the database like so (oversimplified): ``` class User { function __construct($id) { global $_db; $q = $_db->query("SELECT name, mail FROM user WHERE id = ?", $id); $this->loadProperties($q); } } ``` Now say we have a page that shows a list of users. I still want to make `User` objects for each of them, but I don't want to query the database for each separate user. So, I extend the `User` class to take an object as an argument: ``` class User { function __construct($id) { if(is_object($id)) $q = $id; else { global $_db; $q = $_db->query("SELECT name, mail FROM user WHERE id = ?", $id); } $this->loadProperties($q); } } ``` Now I can create a list of, for example, the 100 most recently created and active accounts: ``` $user_list = []; $q = $_db->query("SELECT name, mail FROM user WHERE banned = 0 ORDER BY date_created DESC LIMIT 100"); while($a = $_db->fetch($q)) $user_list[] = new User($a); ``` This all works great, except for one big downside: the database queries for table `user` are no longer in one place, which is kind of making spaghetti code. This is where I'm starting to wonder whether this can be done more efficiently. So maybe I need to extend my `DB` object instead of my `User` object, for example: ``` class DB { public function getUsers($where) { $q = $this->query("SELECT name, mail FROM user WHERE ".$where); $users = []; while($a = $this->fetch($q)) $users[] = new User($a); } } ``` Now I would create the user list as follows: ``` $user_list = $_db->getUsers("banned = 0 ORDER BY date_created DESC LIMIT 100"); ``` But now I'm calling the `getUsers()` method in various places using various SQL queries, solving nothing. I also don't want to load the same properties each time, so my `getUsers()` method will have to take entire SQL queries as an argument. Anyway, you get the point. Speaking of loading different properties, there's another thing that has been bugging me writing OO in PHP. Let's assume our PHP object has at least every property our database row has. Say I have a method `User::getName()`: ``` class User { public function getName() { return $this->name; } } ``` This function will assume the appropriate field has been loaded from the database. However it would be inefficient to preload all of the user's properties each time I make an object. Sometimes I'll only need the user's name. On the other hand it would also be inefficient to go into the database at this point to load this one property. I have to make sure that for each method I use, the appropriate properties have already been loaded. This makes complete sense from a performance perspective, but from an OO perspective, it means you have to know beforehand which methods you're gonna use which makes it a lot less dynamic and, again, allows for spaghetti code. The last thing I bumped into (for now at least), is how to separate actual new users from `new User`. I figured I'd use a separate class called `Registration` (again, oversimplified): ``` class Registration { function createUser() { $form = $this->getSubmittedForm(); global $_db; $_db->query("INSERT INTO user (name, mail) VALUES (?, ?)", $form->name, $form->mail); if($_db->hasError) return FALSE; return $_db->insertedID; } } ``` But this means I have to create two separate classes for each database table and again I have different classes accessing the same table. Not to mention there's a third class handling login sessions that's also accessing the user table. In summary, I feel like all of the above can be done way more efficiently. Most importantly I want pretty code. I feel like I'm missing a way to approach the database from an OO perspective. But how can I do so without losing the dynamics and power of SQL queries? I'm looking forward to reading your experiences and ideas in this field. Update Seems most of you condemn my use of `global $_db`. Though you've convinced me this isn't the best approach, for the scope of this question it's irrelevant whether I'm supplying the database through an argument, a global or a singleton. It's still a separate class `DB` that handles any interaction with the database.