PHP: recognizing results from multiple tables
Moderator: General Moderators
PHP: recognizing results from multiple tables
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?
The list() function is a simple way to do it..
Code: Select all
while(list($var1,$var2,,$var4)=mysql_fetch_array($result)) {
echo $var1;
}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]
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]
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.
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.
Code: Select all
select items.ItemNo as IItemNo, rebates.ItemNo as 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
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.
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.