Getting result from multiple table selection
Posted: Sat Apr 21, 2007 12:21 pm
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:
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?
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]"-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?