Best way to turn a MySQL query into an object

I have a开发者_StackOverflow中文版 user table that contains a bunch of columns that I want to access in a mysql query for the logged in user. Ultimately, I want the values in these columns to form a User object. Is there a standard function for doing this, or do I basically need to pass each value of the query into a new User statement so that they are passed to the User class's constructor?

mysql_fetch_object() allows you to specify a class name so instances of that class will be constructed from your results:

$result = mysql_query($sql); // Error handling not included

if (mysql_num_rows($result) == 1) {
    $user = mysql_fetch_object($result, 'User');
}

Unless you specify the properties and their access modifiers that correspond to the columns listed in your SELECT query, they will default to public.

Your object's constructor is called after populating its properties. Any changes or additional tasks you want to perform may be performed in the constructor.

mysql_fetch_object

You should checkout mysqli_result::fetch_object. You could also look into PHP's PDO extension.

Table

First, know your table.

CREATE TABLE `user` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(256) NOT NULL,
    `password` VARCHAR(256) NOT NULL,
     PRIMARY KEY (`id`)
);

Domain Object

Create your object. You do not need to define any of these fields, because they will be added to your object as public by default, but it is clear to anyone who may maintain your code later; to define the fields for clarity. The accessors and mutators are necessary since the fields are private.

class User {
    private $id;
    private $username;
    private $password;

    public function __construct($id, $username, $password) {
        $this->id = $id;
        $this->username = $username;
        $this->password = $password;
    }

    public function getId() { return $this->id; }
    public function setId($id) { $this->id = $id; }

    public function getUsername() { return $this->username; }
    public function setUsername($username) { $this->username = $username; }

    public function getPassword() { return $this->password; }
    public function setPassword($password) { $this->password = $password; }
}

Query

Create a query class. A lot of this is boilerplate code, which can be extracted out into an abstract class.

class UserQuery {
    public function create() {
        return new self();
    }

    public function findById($id) {
        $user = null;
        $conn = mysqli_connect("localhost", "root", "pass", "company_db");

        if ($conn->connect_error)
            die("$conn->connect_errno: $conn->connect_error");

        $query = "SELECT * FROM `user` WHERE `id` = ?";
        $stmt = $conn->stmt_init();

        if (!$stmt->prepare($query)) {
            print "Failed to prepare statement\n";
        } else {
            $stmt->bind_param('i', $id); 
            $stmt->execute();
            $result = $stmt->get_result();
            $obj = $result->fetch_object();

            return new User($obj->id, $obj->username, $obj->password);
        }

        $stmt->close();
        $conn->close();

        return null;
    } 
}

Fetch a User object from the user table.

$user = UserQuery::create()->findById(1);
printf("Found user: %s", $user->getUsername());