Writing a function that converts SQL result rows into an object

Suppose I have a database schema like this:

http://img98.imageshack.us/img98/786/samplequ.png

I use the following SQL to select all rows from the customer table, and also all related rows:

SELECT c.*,
cty.id AS country__id, cty.name AS country__name,
o.id AS order__id, o.date AS order__date, o.total AS order__total,
ol.id AS order__orderline__id, ol.quantity AS order__orderline__quantity, ol.sub_total AS order__orderline__sub_total,
p.id AS order__orderline__product__id, p.name AS order__orderline__product__name, p.price AS order__orderline__product__price,
s.id AS order__shop__id, s.name AS order__shop__name
FROM customer c
JOIN country cty ON cty.id=c.country_id
JOIN order o ON o.customer_id=c.id
JOIN shop s ON s.id=o.shop_id
JOIN orderline ol ON ol.order_id=o.id
JOIN product p ON ol.product_id=p.id
ORDER BY
c.last_name ASC,
c.first_name ASC,
o.date DESC,
ol.id ASC

You can see that despite the columns from the customer table, all other columns use an alias that reflects its relationship that can be used to construct the object. The double underscores in the alias are used to identify the relationship hierarchy.

I would like to write a PHP function that takes the result rows and returns an array of Customer objects. These Customer objects should be loaded with other related objects:

$customers=convert_result_rows_to_objects($result_rows,'Customer');
foreach($customers as $cust){开发者_StackOverflow
 echo $cust->id;
 echo $cust->get_full_name();
 echo $cust->country->name;
 foreach($cust->orders as $o){
  echo $o->id;
  echo $o->date;
  echo $o->shop->name;
  foreach($o->orderlines as $ol){
   echo $ol->product->name
   echo $ol->quantity;
   echo $ol->sub_total;
  }
  echo $o->total;
 }
}

I have already written the models for each database table, for example:

class Customer{
 public function get_full_name(){...}
}

For simplicity, I ignore the getters and setters for all other fields.

But how do I go about writing the convert_result_rows_to_objects function?

I don't want to use an ORM yet.

What I have got in my brain now is, the function should look like this,

function convert_result_rows_to_objects($result_rows, $main_class_name){

    $main_objs = array(); //containing the main objects to be returned, customer objects in my example
    $main_obj = NULL;
    $previous_row = NULL;
    $next_row = NULL;

    for($i = 0; $i<count($result_rows); $i++){

        $previous_row = ($i > 0 ? ($result_rows[$i - 1]) : NULL);
        $this_row = $result_rows[$i];
        $next_row = $i === ( count($result_rows) - 1) ? NULL : ($result_rows[$i + 1]);

        if ($previous_row === NULL || $previous_row->id !== $this_row->id) {
            $main_obj = new $main_class_name(); //create the main object  

        //what should be done next?

    }
}

EDIT: One thing I didn't point out in the original post is that I use a view to bring all the relevant columns together. The example code doesn't highlight it so much, but if you create a view that brings together everything you want, then you can create a model based off that and the query, as well as the model, will be very easy. /EDIT

It's not too difficult. You need to make sure that your model class looks like your database row, otherwise you'll have to do manual mapping. I have a base class for my database model that looks like the following:

class Ashurex_Model
{
    public function __construct($args = null)
    {
        if(is_array($args))
        {
            $this->setOptions($args);
        }
    }

    // Magic setter changes user_id to setUserId
    public function __set($name, $value)
    {
        $method = 'set' . Ashurex_Utilities::underscoreToCamelCase($name);
        if (method_exists($this, $method))
        {
            $this->$method($value);
        }
    }

    // Magic getter changes user_id to getUserId
    public function __get($name)
    {
        $method = 'get' . Ashurex_Utilities::underscoreToCamelCase($name);
        if (method_exists($this, $method))
        {
            return $this->$method();
        }
    }

    public function __call($name, $args)
    {
        if (method_exists($this, $name))
        {
            return call_user_func_array(array($this, $name), $args);
        }
    }

    // Used for initializing an object off the database row
    // transforms all the row names (like user_id to UserId)
    // from underscores to camel case
    protected function setOptions(array $options)
    {
        foreach($options as $key => $value)
        {
            $this->__set($key,$value);
        }
        return $this;
    }
}

An example user class would look like this:

class Ashurex_Model_User extends Ashurex_Model
{
    protected $_id;
    protected $_username;
    protected $_userpass;
    protected $_firstName;

    { ... }

    public function getId(){ return $this->_id; }
    public function getUsername(){ return $this->_username; }
    public function getUserpass(){ return $this->_userpass; }
    public function getFirstName(){ return $this->_firstName; }

    { ... }

    public function setId($id){ $this->_id = $id; }
    public function setUsername($username){ $this->_username = $username; }
    public function setUserpass($password){ $this->_userpass = $password; }
    public function setFirstName($firstName){ $this->_firstName = $firstName; }

    { ... }

    // This function will help when automatically saving the object back to the database
    // The array keys must be named exactly what the database columns are called
    public function toArray()
    {
        $data = array(
            'id'                    => $this->getId(),
            'username'              => $this->getUsername(),
            'userpass'              => $this->getUserpass(),
            'first_name'            => $this->getFirstName(),
            { ... }
        );

        return $data;
    }
}

The database table looks like this:

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL,
  `userpass` varchar(160) NOT NULL,
  `first_name` varchar(64) NOT NULL,
  `last_name` varchar(64) NOT NULL,
  `email` varchar(64) NOT NULL,
  `role_id` tinyint(4) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `force_password_change` tinyint(1) NOT NULL DEFAULT '0',
  `creation_datetime` datetime NOT NULL,
  `updated_datetime` datetime NOT NULL,
  `user_salt` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `role_id` (`role_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

As you can see, the most important aspect is that you name your columns in an appropriate manner that your base model class can 'automagically' map the column name to a method.

Finally, this is what the save code would look like...

public function save(Ashurex_Model $obj)
    {
        try
        {
            $data = $obj->toArray();
                    // If no id is set, we are inserting a new row
            if(null === ($id = $data['id']))
            {
                unset($data['id']);
                $this->getDbTable()->insert($data);
                return $this->getDb()->lastInsertId();
            }
            else
            {
                            // We have an Id, do an update
                $where = $this->getDbTable()->getAdapter()->quoteInto('id = ?',array($id));
                $this->getDbTable()->update($data,$where);
                return $id;
            }
        }
        catch(Exception $e)
        {
            self::logException(__METHOD__,$e);
        }
        return false;
    }

Example find code looks like this, as you can see it initializes the new object right from the database result row based on column names:

public function find($id)
    {
        try
        {
            $table = $this->getDbView();
            $stmt = $table->select()
                        ->where('id = ?')
                        ->bind(array($id));

            $row = $table->fetchRow($stmt);
            if(!is_null($row))
            {
                $r = $row->toArray();
                $obj = new Ashurex_Model_User($r);
                return $obj;
            }
            else
            {
                return null;
            }
        }
        catch(Exception $ex)
        {
            self::logException(__METHOD__,$ex);
            return null;
        }
    }

EDIT:

Sorry, I didn't pay attention when you said you already had the model. First, to populate the objects you need to identify the fields (of course). Since you have a prefix you could use strstr or strpos to search for the object identification (each one of them) and them get the name of the field using substr. Then you are ready to populate the objects. If you want it to be really generic you would need to break the fields by the double underscores and create the objects dynamically.

It would be nice if you could show an example of the returned data.

Pretty sure the information you want can be found here: http://php.net/manual/en/book.reflection.php

I reckon that as you work through all the edge cases, bits of weirdness in mapping database columns to getter/setters, avoiding loading your entire database at start up etc. you will end up writing an ORM.