PHP: recognizing results from multiple tables

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
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

PHP: recognizing results from multiple tables

Post by Unipus »

In SQL I can do a query in the format 'select * from sample, example' and then access the columns using the sample.column or example.column notation. In PHP, though, how do I access those variables specific to a given table from a multi-table query?
User avatar
xisle
Forum Contributor
Posts: 249
Joined: Wed Jun 25, 2003 1:53 pm

Post by xisle »

The list() function is a simple way to do it..

Code: Select all

while(list($var1,$var2,,$var4)=mysql_fetch_array($result)) { 
    echo $var1;
}
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

Post by Unipus »

Unless I'm missing something, that doesn't really apply to what I'm doing.

Here's the current situation: I've got two tables, named 'items' and 'rebates'. Both have a field named 'ItemNo'. I'm doing a left join on these tables so that I get all matching results from both and can see where they intersect. There is a one-to-many relationship, where there are necessarily more items than rebates. That works fine. But here's the problem: PHP doesn't seem able to recognize the distinction of variables from different tables - for instance, items.ItemNo means nothing to PHP, apparently.

So I've got two fields with the same name coming in, and PHP is naturally just assigning the value according to the last result it finds, which is always the rebate table. As a result, ItemNo often returns NULL because for most of the items, there is no corresponding rebate.

What I want is to have ItemNo be assigned the value from the items table.[/i]
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Hmm, OK, I'm not a mysql guy (and I don't know if that's what your using), but normally if you do a LEFT JOIN there is only one couple in the result set from the two joined columns, and it wouldn't be qualified with a tablename -- so 'ItemNo' should work fine as is.

If MySQL does funny stuff here, then try using an "AS" clause to rename the table.field to some other simple name that then behave better across them PHP<->MySQL interface.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Code: Select all

select items.ItemNo as IItemNo, rebates.ItemNo as RItemNo ....
...and then you can refer to the different fields using IItemNo resp. RItemNo.

Hope that gives you some more ideas. LEFT/RIGHT/CROSS joines doesnt work as its more a "how to link tables" function rather than selecting the data.0
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

JAM wrote: Hope that gives you some more ideas. LEFT/RIGHT/CROSS joines doesnt work as its more a "how to link tables" function rather than selecting the data.
Really?? MySQL is even more screwed up than I thought...
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Hehe

Not MySQL...
Its an SQL standard, that works pretty similiar on all platforms/db's.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

That's not comletely true. Most SQL DBMS's offer the [LEFT|RIGHT|FULL] JOIN with a USING clause, which causes the two tables to be joined on the shared column named in the USING yielding that column unqualified in the result set (similar to how a NATURAL JOIN operates on ALL shared columns.

If the tables are being joined on the shared column I would expect a USING to be used. Otherwise, I would expect a regular ON clause, but the columns to be explicitly listed and renamed in the SELECT list. In either case I wouldn't expect to have to qualify names on the PHP side.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Of course, and Oracle can use much, much more join's than any database, some db's can use subqueries within joins, etc. etc...

Hence the 'pretty similiar'...
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

Post by Unipus »

Got it now, thanks.
Post Reply