从PHP db查询返回结果对象

Question

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 similarly named fields.

Any help would be great thanks.

---

EDIT: (in response to Steve)

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

Answer 1

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());