Page 1 of 1
PHP: recognizing results from multiple tables
Posted: Tue Aug 26, 2003 3:57 pm
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?
Posted: Tue Aug 26, 2003 4:06 pm
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;
}
Posted: Tue Aug 26, 2003 5:44 pm
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]
Posted: Tue Aug 26, 2003 10:03 pm
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.
Posted: Tue Aug 26, 2003 10:46 pm
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
Posted: Tue Aug 26, 2003 11:15 pm
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...
Posted: Tue Aug 26, 2003 11:50 pm
by JAM
Hehe
Not MySQL...
Its an SQL standard, that works pretty similiar on all platforms/db's.
Posted: Wed Aug 27, 2003 12:13 am
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.
Posted: Wed Aug 27, 2003 11:01 am
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'...
Posted: Wed Aug 27, 2003 12:28 pm
by Unipus
Got it now, thanks.