Getting result from multiple table selection

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
ario
Forum Newbie
Posts: 13
Joined: Wed Apr 26, 2006 3:56 pm

Getting result from multiple table selection

Post by ario »

I have a MySQL database and I want to do something like this:

I have a table of objects (I'll call it "obj") and I want to search for objects matching a particular where clause and which are linked to users, and then get those objects and users, so:

Code: Select all

"SELECT users.*,obj.* FROM users, obj WHERE obj.userid=users.id AND [other conditions]"
Now, this query works fine--when I execute it from the command line I get the results I need. And when I execute it in PHP and look at the results using mysql_fetch_row, it also works fine. My question is, what's the best way to fetch data from this result and turn it into the two sets of User and Obj objects that I need? There are a few difficulties:

-I can't use mysql_fetch_assoc because field names may overlap, especially the field id, which is guaranteed to overlap for any two tables.
-I don't want to hard-code the number of fields that each table has, because this may change and that seems like a hacked way to separate the returned values.

It looks like my answer is to use mysql_field_table to determine the table for each field, but my instincts with my past attempts to build something like this in PHP is that I'm overlooking or don't know about something simpler I can do. This seems like a fairly common application, so, is there an easy way to do this, or am I just going to have to build it myself?
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post by bdlang »

Don't use '*' to retrieve all columns from each table, select each column by name, and use an ALIAS on those columns with ambiguity. Then you can easily filter out what you want done with each piece of data from each table. I'd still recommend using mysql_fetch_assoc().
User avatar
ario
Forum Newbie
Posts: 13
Joined: Wed Apr 26, 2006 3:56 pm

Post by ario »

That still gives the problem of having to name each column explicitly, which means I have to keep track of all queries like this linking to these tables and change them whenever the fields in the table change. My feeling about that is that I shouldn't have to add on another layer of complexity to the query in order to make up for a lack of functionality in PHP... I'll look into aliasing, but does anyone else know of a PHP-side way to deal with this?
Post Reply