return result object from PHP db query

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

@outis, I'm not entirely sure that answers my question, but would you mind giving an example? My SQl's not the greatest — iangraham
– iangraham, Commented Nov 16, 2009 at 9:00

outis · Accepted Answer · 2009-11-16 09:24:52Z

4

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

edited Nov 16, 2009 at 9:24

answered Nov 16, 2009 at 9:09

outis

77.7k23 gold badges155 silver badges228 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

iangraham Over a year ago

so does this query select everything from the address table? I wasn't aware you could do that. thanks!

outis Over a year ago

It selects every address with the user_id you specify, which is the same as your query.

Steve · Accepted Answer · 2009-11-16 08:49:51Z

0

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.

answered Nov 16, 2009 at 8:49

Steve

3714 silver badges13 bronze badges

Collectives™ on Stack Overflow

return result object from PHP db query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related