SQL query efficiency when using PHP classes

Question

I've been programming in PHP for many years, however only recently started programming with classes. I have the following - basic - user class as follows:

<?php
/* The class for constructing any user's information
 */

    class User {

        protected $userId, $email, $userGroup;

        protected function getEmail() {
            return $this->email;
        }

        protected function getUserId() {
            return $this->userId;
        }

        protected function getUserGroup() {
            return $this->userId;
        }


        public function __construct($userId='') {
            if($userId) {
                $select = mysql_query("SELECT userId, email, user_group FROM user WHERE userId = '$userId'");
                while($user==mysql_fetch_array($select)) {
                $this->email = $user[email];
                    $this->userId = $userId;
                    $this->userGroup = $user[user_group];
                }
            }
        }

}?>

So I understand I can do the following

<?php
$user = new User($userId);
echo $user->getEmail();
?>

To display the user's email address for a given userId. What I'd like to know is, what would be the best way - using OOP - to display, say, 40 user's emails. Obviously creating 40 user objects would be silly as that's 40 SQL queries. Would you simply make a "users" class that was used for returning an array of multiple users, after doing an SQL given various parameters?

Ie

<?php
$getUsers = new Users('Tom');
// create 'Users' object where name is 'Tom'
print_r($users);
// prints the returned array of users?
?>

Thanks for your help. Hopefully this is clear.

Answer 1

I'd do it something like this (using another class):

class UserRepository {
    public function getByName($name) {
        $result = mysql_query("SELECT userId, email, user_group FROM user WHERE name = '$name'");

        $users = [];

        while ($row = mysql_fetch_assoc($result)) {
            $user = new User;
            $user->email     = $row['email'];
            $user->userId    = $row['userId'];
            $user->userGroup = $row['user_group'];

            $users[] = $user;
        }

        return $users;
    }
}

Addition: The following example gives a good idea on how you can make the classes more testable and easy to modify in the future should they need to be:

UserRepositoryInterface

interface UserRepositoryInterface {
    public function getByName($name);
    public function getByUserId($id);
}

MySqliUserRepository

class MySqliUserRepository implements UserRepositoryInterface {
    public function getByName($name) {
        // Get by name using mysqli methods here
    }

    public function getByUserId($id) {
        // Get by user id using mysqli methods here
    }
}

PDOUserRepository

class PDOUserRepository implements UserRepositoryInterface {
    public function getByName($name) {
        // Get by name using PDO methods here
    }

    public function getByUserId($id) {
        // Get by user id using PDO methods here
    }
}

Usage

class Foo {

    protected $userRepository;

    public function __construct(UserRepositoryInterface $userRepository) {
        $this->userRepository = $userRepository;
    }

    public function bar() {
        $user = $this->userRepository->getByUserId(10);
    }
}

Regarding use of mysql_

It may not be exactly how you do it but it'll give you an idea. Also mysql_ is depreciated so its best to use mysqli_ or PDO (my personal recommendation). PDO is also much more OOP friendly.

PDO: http://php.net/manual/en/book.pdo.php

mysqli_: http://php.net/manual/en/book.mysqli.php

Update:

Your individual user class would simply contain information relating to the user. The user class shouldn't contain any way to retrieve a user, that is the job of the repository. So if you want to retrieve 1 user, instead of doing in the User __construct as you currently do, add a method to the UserRepository that looks something like this:

public function getByUserId($id) {
    // Select user from db, check only 1 exists, make user object, return.
}

Answer 2

I try to separate my data objects from the DB stuff. In your case, I'd make the following arrangements:

• An instance of the User class represents an individual user, either in DB or not. The constructor does not retrieve anything from DB, it just populates class properties.

• For users not in DB (e.g., a newly created user) the userId property is NULL (not '').

• Methods that do DB stuff expect a database interface (or at least an object) as argument:

  public function save(PDO $pdo){
  }
  

• There're static methods to fetch stuff from DB where a class instance does not make sense yet; they return either a User instance or a User collection:

  public static function fetchById(PDO $pdo, $id){
  }
  
  public static function fetchAll(PDO $pdo){
  }
  

• When it makes sense, I write a private method to share common code:

  private static function fetch(PDO $pdo, array $filter=array()){
      $sql = 'SELECT id, email, group
           FROM user' . PHP_EOL;
      $params = array();
  
     if( isset($filter['id']) ){
         $sql .= 'WHERE id=:id';
         $params['id'] = $filter['id'];
     }
  
     //...
  }
  
  public static function fetchById(PDO $pdo, $id){
      $data = self::fetch($pdo, array('id' => $id));
      if( empty($data) ){
          return NULL;
      }else{
          reset($data);
          return curren($data);
      }
  }
  
  public static function fetchAll(PDO $pdo){
      return self::fetch($pdo);
  }
  

• The User collection I've mentioned can as simple as an array of User instances, or as elaborate as your own generics implementation.

This way, a typical script looks like this:

// Fetch my details
$me = User::fetchById(1);

// Create a new user
$you = new User(NULL, 'Joe', 'Guests');
$you->save($pdo);
$message = sprintf('User created with ID=%d', $you->userId);

Answer 3

Instead of using the construct to retrieve a user, you could use methods to insert/retrieve users instead.

You can create methods to insert new users, update a particular user, retrieve one particular user or retrieve all (with conditions) in an array of users' objects, etc