loading a PHP class object from a database--best practices, database handle, etc

Question

I think perhaps my questions have been answered in part in other locations on stackoverflow, but I felt I might get a more comprehensive answer by asking it all together. I apologize for anything dumb I might type--I don't work in PHP nearly enough, so when I do, I feel like I'm learning it all over again.

Anyway, let's say I've got a mysql database table called "items" with the following columns: item_id, item_type, item_name. And I've got a PHP class that looks like this:

class Item
{
    public $item_id;
    public $item_type;
    public $item_name;
}

In other words, I really just want a direct map from the database to the class object.

I've decided to include a method in the class called "load" that retrieves the item data when supplied with the item_id. So here are my two questions:

1. What is the best way to connect to the database within the class? Should I be instantiating a new PDO object inside the class, or should I be passing the PDO database handle in as a parameter on the load method? Does it matter?

2. Does this make any sense as a way to assign the class properties, or is there a much smarter way to do this:

   $sth = $dbh->query("SELECT * FROM items WHERE item_id = $item_id");
   $sth->setFetchMode(PDO::FETCH_CLASS, 'Item');
   while ($obj = $sth->fetch()) {
       foreach ($obj as $key => $value) {
           $this->$key = $value;
       }
   }   
   

Edit: I realized after seeing these responses (which make for interesting reading, to be sure), that I probably didn't go into enough detail.

I think using an ORM library might be overkill for what I'm doing (just a personal hobby project--not something for an employer), but actually, I'm not certain that's quite what I need, anyway. Here's why. Because I might have something like this:

class InventoryItem extends Item
{
    public $user_id;
    public $quantity;
}

I haven't really thought this through. Just giving an example. So the query I'd use to get the data might look something like this:

SELECT * 
FROM items, inventories 
WHERE item.item_id = inventories.item_id 
AND user_id = $user_id;

In other words, I'd have to create the SQL statement that would correctly pull out the data for the class, and I really just need to know if I should be passing in my database handle or creating it inside the load method, and if the code above makes sense as a good way to assign the property values.

An additional wrinkle is that I might have to manipulate some of the data (e.g., if maybe one of the columns is storing a json encoded array or something).

Answer 1

I use a database object class that is generic, then I have individual classes (which match the different tables in the database) which each extend that.

The database object class contains the functions to instantiate the rows into objects, and perform any crud, the classes that extend them just contain the attributes of each table object.

This might be better asked over on programmers.stackexchange, as its quite an open ended question, and I imagine there are many different ways to do this and opinions on the matter.

Answer 2

You are looking for an object relational mapper (orm). This is a library let you define objects which map to database tables.

Doctrine seems to be the preferred ORM for PHP. There are others as well.