mysql_fetch_array and table aliases

[Paul E Collins]

I'm using mysql_fetch_array to run a query along these lines:

SELECT A.name, B.name FROM A, B WHERE A.id = B.id

I tried this code:

while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo $row['A.name'];
}

.... but the echo statement produces "Notice: Undefined index: A.name".
Of course, using just 'name' would be ambiguous because the query also
retrieves B.name (and my actual query will involve more tables and
probably select '*' [i.e. all columns], so it isn't trivial to use
'AS' to provide individual column aliases).

How can I access a column's value based on table *and* column name?

P.

[Ewoud Dronkert]

Paul E Collins wrote:

SELECT A.name, B.name FROM A, B WHERE A.id = B.id

I tried this code:

while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo $row['A.name'];
}

... but the echo statement produces "Notice: Undefined index: A.name".

1. (very bad) use $row[0] and $row['name']
2. (bad) use mysql_fetch_row(), $row[0], $row[1]. Doesn't work with
"select *".
3. (best) alias all duplicate column names using "as".

--
E. Dronkert

[alvonsius]

Of course when you use SELECT A.name, B.name blah blah you would get
the same column "name" as result (you can try this in console too), so
the best idea is using alias that representing the column like this :

SELECT
A.name AS name_A,
B.name AS name_B
FROM A, B WHERE A.id = B.id

and to access it just use $row['name_A'] ... the variable name still
gave me the information I need

PS: why don't you use LEFT JOIN ??

[Ewoud Dronkert]

alvonsius wrote:

PS: why don't you use LEFT JOIN ??

Probably because it's different than his implicit INNER JOIN.

--
E. Dronkert

[Paul E Collins]

"alvonsius" <al**************@gmail.com> wrote:

SELECT
A.name AS name_A,
B.name AS name_B
FROM A, B WHERE A.id = B.id
Yep, thanks. The aliases make the query that little bit longer, but
it's not a big problem.
PS: why don't you use LEFT JOIN ??

Well, in my real query it's an inner join. This was just a rather
meaningless example. If you're asking why I use the comma notation, I
just find it a bit more readable than "JOIN... JOIN... JOIN" when
several tables are involved.

P.