return result object from PHP db query

I'm working on a query where I pull data from multiple tables using left joins like this:

 $query = "
     SELECT 
         users.name, 
         users.email,
         address.street,
         address.city,
         address.state,
         address.zip
    FROM users    
LEFT JOIN(
        SELECT 
            addresses.street, 
            addresses.city, 
            addresses.state, 
            addresses.zip, 
            `addresses.user_id `
        FROM addresses
)
AS address
ON users.id = `address.user_id`
WHERE users.id = 1";

$mysql = new mysql(HOST, USER, PASS, DBNAME);
$result = $mysql->query($query)->fetch_object();

The results I get now I can access the results like this:

   // get name
   $result->name;
   //get street address
   $result->street;

Since the query will eventually become something a little more complex than that. I would like to be able to access the data like this:

// get user name
$result->user->name;

// get the street address
$result->address->street;

This will help make the data easier to read, since some of the table have similarl开发者_如何学Goy named fields.

Any help would be great thanks.

I am familiar with ORMs, and I'm currently using the Kohana framework. My interest is in cutting down on the actually number of queries run. The ORM in the Kohana framework calls a "SELECT *" for each table/model that you call. I'd prefer not to do that if I dont have to.

Running two separate queries(as shown in the example) is not that big of a deal, but in my real example i'll be pulling data from about 10 separate tables, so I'd rather not run separate queries to get the functionality i was describing

To answer your question in the comment, here's the query I would envision:

SELECT 
     users.name, users.email,
     addresses.street, addresses.city, addresses.state, addresses.zip
  FROM users    
  LEFT JOIN addresses
    ON users.id = addresses.user_id
  WHERE users.id = 1

Since the sub-select is at most a projection of the addresses table, it seems redundant.

As for the main question, I'm having a hard time coming up with anything that's elegant and non-hackish. In fact, the only things that do come to mind are downright ugly. You could, for instance, add prefixes to the column names in the query:

SELECT 
     users.name AS user_name, users.email AS user_email,
     addresses.street AS address_street, ...

You'd have to parse the column names yourself. I suppose it wouldn't be too bad. Something like:

function rowToObject($row) {
    $obj = new StdClass();
    foreach ($row as $key => $val) {
        $keys = explode('_', $key);
        $o = $obj;
        for ($i=0; count($keys) > 1; ++$i) {
          $k = array_shift($keys);              
          if (! isset($o->{$k})) {
            $o->{$k} = new StdClass();
          }
          $o = $o->{$k};
        }
        $o->{$keys[0]} = $val;
    }
    return $obj;
}

...
$result = rowToObject($mysql->query($query)->fetch_assoc());

I think that maybe the only way would be to use some ORM (Object Relational Mapper). Usage of ORM brings other handy stuffs than this, but you have to pay for it whit lower performance.

Good PHP ORMs are e.g. Doctrine or Propel.