Data-mapping in general refers to mapping data from one data structure to another data structure. In this particular case it refers to mapping data to and from a table row to an object. At least in the simplest case. You may also have to deal with mapping one row to an object, and rows related to the first one by a foreign key into an array of objects residing in the first object etc. That is, you will either have to map inheritance or composition to and from the database.
If you have a one to one relation between row and object, you could go with something like this (some comments below)
class row_object
{
public function __construct($id = null, $id_field = 'id')
{
# make sure table name only inlcudes lower case letters a-z
# and underscore _
$reg = '#[^a-z_]#';
$table = get_called_class();
if ($id && !preg_match($reg, $table))
{
$fields = array();
foreach ($this as $k => $v) {
# make certain field name only includes
# lower case letters a-z and underscore _
if (!preg_match($reg, $k))
{
$fields[] = $k;
}
}
$query = sprintf("SELECT %s FROM %s WHERE %s=:id",
implode(', ', $fields),
$table,
$id_field
);
# Which means you may user any other character than a-z and _
# to prevent something from being included when talking to the db.
$this->Db = registry::get('dbname');
echo $query . PHP_EOL;
$stmt = $this->Db->prepare($query);
if ($stmt->execute(array('id' => $id)))
{
$row = $stmt->fetch();
foreach ($fields as $v)
{
$this->$v = $row[$v];
}
}
}
}
}
class member extends row_object
{
protected $username;
protected $action_date;
protected $action;
protected $Not_From_Db;
public function __construct($id = null, $id_field = 'username')
{
$this->Not_From_Db = 'Due to use of upper case characters';
parent::__construct($id, $id_field);
}
public function __toString()
{
return sprintf('%s chose ā%sā on %s%s%s',
$this->member_id,
$this->action,
$this->login_date,
PHP_EOL,
$this->Not_From_Db
);
}
}
$m = new member('john_doe');
echo $m;
class row_object acts as a base class for any class that has a one to one relation with a table row. Class and table names must match. Member properties and table columns must match. Furthermore, to handle member properties which are NOT found in the table as columns, I only include those properties which consists only of letters a-z and underscore _. Thus, I can keep member properties with for example upper case letters and thereby exclude them from queries against the table. Do note that this requires a coding standard with a strict naming scheme for your member properties to make sure every programmer handle this. Coding standard isn't a bad idea to begin with anyway though.
But I do not necessarily advocate going down that road, and there are of course other ways of achieving this. You could for example keep an array for what goes in the table, using member property names as keys and table field names as values. This on the other hand means you need to take every member property which is stored in a db table and put that into an extra array in the class, which means more typing (although it's quickly done). Choose whatever approach feels best for you. This would go along the lines of
class stuff extends row_object
{
$memberProp1;
$memberProp2;
$not_in_db;
static $db_fields = array(
'memberProp1' => 'member_prop1',
'memberProp2' => 'member_prop2',
);
}
class row_object
{
public function __construct()
{
# ... as before
$query = sprintf("SELECT %s FROM %s WHERE %s=:id",
implode(', ', $db_fields),
$table,
$id_field
);
}
public function store($id, $id_field='id')
{
# Should do similar checks here as in the constructor.
$table = get_called_class();
$params = array();
$query_params = array();
foreach (static::$db_fields as $k => $v)
{
$query_params[] = "$v=:$k";
$params[$k] = $this->$k;
}
# You should check that you have an id. Else you need to do an insert
# instead
$query = sprintf("UPDATE %s SET %s WHERE %s=:id"),
$table,
implode(', ', $query_params),
$id_field
);
$stmt = $this->Db->prepare($query)
$stmt->execute($params);
}
}
You might also wish to make the store function smarter, so you do not unnecessarily execute update queries. If you create a wrapper function for setting member properties, which deal both with actually assigning the value and keeping track of what's been updated, you can make sure only updated things are actually sent in an update query.
class row_object
{
# all specific setter functions use the wrapper function
public function setUsername($value)
{
$this->setVar('username', $value);
}
# May want to keep this protected so the outside can not set
# whatever variable they want
protected function setVar($name, $value)
{
# check if the value has changed
# and if the property is stored in the table
if ($this->$name != $value &&
isset(static::$db_fields[$name]))
{
# Keep track of changed db variables.
$this->updated_vars[] = $name
}
$this->$name = $value;
}
public function store()
{
# no changes. nothing to update
if (count($this->updated_vars) == 0)
{
return;
}
# Use this approach to only include variables which has changed
# in your update query
foreach ($this->updated_vars as $prop)
{
}
}
}
But, when you need to deal with objects/tables such as an "order", which inlcudes several ordered "items", you will need to deal with mapping several rows into something else. One way would be to use the above approach for the order. The ordered items will be stored in another table, but you may still wish to include them in the Order object.
So how to handle the item rows? One way would be to create factory functions. The first replaces the current constructor (which issues a call to the db), while the second takes row data fetched into an array (with $stmt->fetch()). Each factory function then calls the constructor (which is made protected) which simply creates the actual instance. You may even simplify this by having the factory function which retrieves data from the db call the second factory function with the retrieved row data, thus removing code duplication.
Using this approach, the order class could issue one query to retrieve all the ordered items from the db at once, iterate over all those retrieved rows (with while $row = $stmt->fetch()) and then use the factory function to create each item object.
However, the topic isn't entirely uncomplicated. So you may wish to google "ORM", "ORM mapping", "object relational mapping". There should be some information on the topic to give an introduction. After that you may wish to read about mapping inheritance structures.
But one thing to remember in my opinion is that you shoul not compromise database schema design for the sake of achieving easy mapping. Proper schema design (database normalization) affects database efficiency, and also keeps queries for retrieving, inserting/updating and deleting easy to handle. If a part of a schema doesn't fit your current mapping techniques, either create a new class base to handle the new mapping needed, or deal with this specific circumstance directly where its needed. Then, if you later realize that the same situation occurs someplace else, go ahead and see if you can create a generic mapping technique to deal with it. At that point, you have allready handled it as a specific case once, so you allready know how to do that, and can generalize from there.
