Getting database values into objects

Question

The thing is that you have classes and then you have the database data. When you create an object how do you set the objects properties to contain the data in the database ?

I saw something like this and I'm wondering if this is really the best way to do it. I'm sure this is a fairly common issue, but I don't know what are the most accepted solutions on how to handle it.

In this example when the object is created you pass an id as a parameter and then you run a query to the database with the id and you assing the returned values to the object properties. I don't have much PHP experience and haven't seen this used much.

Is this an acceptable way to achieve this purpose ? Is there a better or more accepted way ?

public function __construct($id = null){
    if($id != null){
        $sql = "SELECT *
        FROM users
        WHERE user_id = $id";

        $res = Db::returnRow($sql);

        // $res contains an associative array with database columns and values

        if($res){
            $this->user_id = $res['user_id'];
            $this->user_name = $res['user_name'];
            //and so on...
        }
    }
}

Could somebody provide some sample code or pseudocode to illustrate what is the correct way to do this ?

Answer 1

You could create a function to do this for you automatically, by looping over the associative array's key/value pairs. Or you could look into using an ORM library.

Answer 2

It could be an acceptable way for a homework maybe. But architecturaly it is not.

Your class that is representing your business data (a user in your example) must be loosely coupled with your database access logic. In the end the PHP class acting as a user should not be aware that the data come from a database, a file or any other resource. Following that you will be able to reuse your user php class in other projects without having to change anything to it! If you have your data access logic inside it you are stuck.

Conclusion: I would suggest to read some resources on Design Pattern (in your situation take a look at DAO pattern) ;) Hint: the one from Head First series is extremely accessible and enjoyable.

Answer 3

Yes, you can semi-automate this by having a parent class all objects inherit from. On load, it queries, "SHOW FIELDS FROM [my tablename]" and populates an associative array with the names. If an id has been passed in, it looks for a valid object in that table with that id and assigns the values to the array.

Side note: don't pass your id directly into your query like that. Parametize the sql and wrap a function around any user input to sanitize it.

Answer 4

If it's mysql, you can just do:

$obj = mysql_fetch_object($query);

PDO the ability to use arbitrary classes as the target for a fetch, but beware that they assign the variable data before running the constructor:

$pdo->query($stmt, PDO::FETCH_CLASS, "MyClass", array('foo'=>'bar'));

...where the final parameter contains arguments for your class constructor.