Table.fieldname syntax doesn't work?

[Margaret MacDonald]

It appears that the 'table.field' dot notation is not preserved by
php's mysql library.

I can create some join 'SELECT a.id, b.id FROM table1 AS a,
table2 AS b' but when I try to refer to the fields as 'a.id' and
'b.id' the reference fails because the table aliases have been
discarded from the dataset as managed by php. 'a.id' has become
merely 'id', and 'b.id' no longer has a fieldname -- the only way to
refer to it is by the numeric index.

Or am I doing something wrong (I hope so!)? I can't find any mention
of this problem in any docs, so it's totally possible that I'm goofing
something up without realising it. If it's a real problem, it's an
annoying one because it means we must (a) name all fields uniquely
even across tables, (b) avoid doing joins, or (c) get used to using
the numeric indexes to the fields instead of their names.

My test program:
$link = mysql_connect( $_SERVER['HTTP_HOST'] ) ;
if ( empty( $link ) ) die( 'Cannot connect to the server' ) ;
$attached = mysql_select_db ( 'dbase' ) ;
if ( ! $attached ) die ( 'Cannot connect to the database' ) ;

$q = 'select i.id, s.id from table_issues as i, table_scopes as s
where i.id=3 and i.scope=s.id' ;
$dset = mysql_query( $q, $link ) ;
if ( $dset )
{
$rec = mysql_fetch_arr ay( $dset ) ;
foreach ( $rec as $k => $v )
echo $k . ' is ' . $v . '<br>' ;
}

yields this result:
0 is 3
id is 129
1 is 129
Thanks in advance for any insights.
Margaret
--
(To mail me, please change .not.invalid to .net, first.
Apologies for the inconvenience.)

[Pedro Graca]

Margaret MacDonald wrote:

It appears that the 'table.field' dot notation is not preserved by
php's mysql library.

I can create some join 'SELECT a.id, b.id FROM table1 AS a,
table2 AS b'

(snip)
The correct syntax is

select col1 AS col_alias1,
col2 AS col_alias2,
...
from table1 table_alias1,
table2 table_alias2,
...

so your example would be
SELECT a.id, b.id FROM table1 a, table2 b

or, if you'd like to rename the columns
SELECT a.id as table1_id, b.id as table2_id from table1 a, table2 b
HTH
--
USENET would be a better place if everybody read: : mail address :
http://www.catb.org/~esr/faqs/smart-questions.html : is valid for :
http://www.netmeister.org/news/learn2quote2.html : "text/plain" :
http://www.expita.com/nomime.html : to 10K bytes :

[Andy Hassall]

On Mon, 05 Apr 2004 22:30:57 GMT, Margaret MacDonald
<sc**********@a tt.not.invalid> wrote:

It appears that the 'table.field' dot notation is not preserved by
php's mysql library.

I can create some join 'SELECT a.id, b.id FROM table1 AS a,
table2 AS b' but when I try to refer to the fields as 'a.id' and
'b.id' the reference fails because the table aliases have been
discarded from the dataset as managed by php. 'a.id' has become
merely 'id', and 'b.id' no longer has a fieldname -- the only way to
refer to it is by the numeric index.

This is how it works in every database interface I have used; table
names/aliases are discarded, leaving only the column name. If you have multiple
columns having the same name, you need to alias them.

SELECT a.id AS a_id, b.id AS b_id
FROM table1 AS a, table2 AS b

This is documented, see:
http://uk.php.net/manual/en/function...etch-array.php

"
If two or more columns of the result have the same field names, the last column
will take precedence. To access the other column(s) of the same name, you must
use the numeric index of the column or make an alias for the column. For
aliased columns, you cannot access the contents with the original column name
(by using 'field' in this example).

Example 1. Query with aliased duplicate field names

SELECT table1.field AS foo, table2.field AS bar FROM table1, table2
"

One school of thought for column naming (that I agree with) is that two
identically named columns in different tables generally implies that there's a
foreign key relationship between the two, and so you wouldn't have two columns
named 'id' in two tables unless you could join on them. If you're joining them
together, then it doesn't matter which of the two you get, as they'd be equal.

So you might have table widget and table thingy:

thingy
------
thingy_id (pk)

widget
------
widget_id (pk)
thingy_id (fk to thingy)

select widget_id, thingy_id
from widget
join thingy using (thingy_id)

There's thingy.thingy_i d and widget.thingy_i d, but no conflict as to which one
to use they're the same value.

--
Andy Hassall <an**@andyh.co. uk> / Space: disk usage analysis tool
http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space

[Pedro Graca]

Pedro Graca wrote:

Margaret MacDonald wrote:

It appears that the 'table.field' dot notation is not preserved by
php's mysql library.

I can create some join 'SELECT a.id, b.id FROM table1 AS a,
table2 AS b'

(snip)
The correct syntax is

(snip bunch of BS)
Oops ... sorry everyone
The "AS" may be used between the tablename and the alias

I just never used it and didn't bother to check the manual
http://www.mysql.com/doc/en/JOIN.html

I'll read your post again Margaret.
Sorry again
--
USENET would be a better place if everybody read: : mail address :
http://www.catb.org/~esr/faqs/smart-questions.html : is valid for :
http://www.netmeister.org/news/learn2quote2.html : "text/plain" :
http://www.expita.com/nomime.html : to 10K bytes :

[Pedro Graca]

Margaret MacDonald wrote:
(snip)

$q = 'select i.id, s.id from table_issues as i, table_scopes as s
where i.id=3 and i.scope=s.id' ;
$dset = mysql_query( $q, $link ) ;
if ( $dset )
{
$rec = mysql_fetch_arr ay( $dset ) ;
foreach ( $rec as $k => $v )
echo $k . ' is ' . $v . '<br>' ;
}

yields this result:
0 is 3
id is 129
1 is 129

Andy is right.
There is no way to get the value using the string index if the columns
have the same name.

$q = 'select i.id as i_id, s.id as s_id from ...';

would yield:
0 is 3
i_id is 3
1 is 129
s_id is 129

--
USENET would be a better place if everybody read: : mail address :
http://www.catb.org/~esr/faqs/smart-questions.html : is valid for :
http://www.netmeister.org/news/learn2quote2.html : "text/plain" :
http://www.expita.com/nomime.html : to 10K bytes :

[Margaret MacDonald]

Thanks, both.

I didn't interpret the passage Andy quoted as being applicable
because, to me, the fully-qualified identifiers t1.xx and t2.xx
*aren't* the same--they're completely different to one another (which
of course is the whole point of the qualification).

I still think it's a bug or at least lacuna in the library, though,
since the qualifiers are present in the dataset when it's returned by
mysql.

So it's back to using unique fieldnames across all tables. Bummers.

Margaret
--
(To mail me, please change .not.invalid to .net, first.
Apologies for the inconvenience.)

[Virgil Green]

"Margaret MacDonald" <sc**********@a tt.not.invalid> wrote in message
news:5j******** *************** *********@4ax.c om...

Thanks, both.

I didn't interpret the passage Andy quoted as being applicable
because, to me, the fully-qualified identifiers t1.xx and t2.xx
*aren't* the same--they're completely different to one another (which
of course is the whole point of the qualification).

I still think it's a bug or at least lacuna in the library, though,
since the qualifiers are present in the dataset when it's returned by
mysql.

So it's back to using unique fieldnames across all tables. Bummers.

You don't need to use unique field names. You merely need to alias them
(making them unique in the result set) in the queries when you happen to
join two tables having fields with the same name.

select t1.name as t1_name, t2.name as t2_name from company t1, employee t2
where t1.compid = t2.compid

or so...

- Virgil