PHP OOP: how to use a generic MySQL class in other classes

Question

I've just started using OOP PHP and ran into a question. I've set up a generic mysql class that allows me to connect to a database and has some functions to obtain records from a table:

class mysql{
    //some lines to connect, followed by:
    public function get_record($sql)
    {
      $result = mysql_result(mysql_query($sql));
      return $result;
      //obiously it's a bit more advanced, but you get the picture.

    }
}

Next, I have a class to obtain user details:

class user{
    __construct($id)
    {  
        $this->id = $id
    }
    public function get_username($id)
    {
         $username = get_record("SELECT name FROM users WHERE id = '".$this->id."'");
         return $username;
    }
}

I tried this, but got the error that the function get_record was unkown. I solved this by adding $mysql = new mysql(); to the user class.

However, it feels quite inefficient to have to instantiate the mysql object for every class that uses my database methods (that's pretty much all of them).

Is there a way to make the mysql class and its methods accessible to all other classes, without having to call the mysql class in every method?

Answer 1

For one, you don't need to use singleton in this case - or actually, you almost never do. See this article, for example.

Second, I think your OO designs are a bit off. The main point of object-oriented programming and design is to isolate responsibility into separate classes. Right now, you're giving your User class two main responsibilities - store / carry one user's relevant data, and query the data service (in this case, a simple MySQL / database abstraction layer).

You should first move that functionality into a separate object. Usually, this is called a Service - so in this case, it's a UserService. A UserService has one responsibility: provide access to User objects. So it'd sorta look like this:

class UserService {
    public function __construct($mysql); // uses the mysql object to access the db. 
    public function get($id) {
        $result = $this->mysql->get_record("select x from y");
        $user = new User($result['id'], $result['name']); // assuming user has a constructor that takes an id and a name
        return $user;
    }
    public function save($user);
    public function delete($user);
}

You tie it all together at the start of your request (or where you need to access users):

$mysql = new MySQL($credentials);
$service = new UserService($mysql);
$user = $service->find(1337);

It's not perfect, but it's a much neater design. Your MySQL object does what it needs to do (build a connection, execute queries), your user object is plain dumb, and your service does only one thing, i.e. provide a layer between the actual storage layer and the thing calling it.