mysql_fetch_array and table aliases

[Paul E Collins]

I'm using mysql_fetch_arr ay 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_arr ay($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]

Re: mysql_fetch_arr ay and table aliases

Paul E Collins wrote:[color=blue]
> SELECT A.name, B.name FROM A, B WHERE A.id = B.id
>
> I tried this code:
>
> while ($row = mysql_fetch_arr ay($result, MYSQL_ASSOC))
> {
> echo $row['A.name'];
> }
>
> ... but the echo statement produces "Notice: Undefined index: A.name".[/color]

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]

Re: mysql_fetch_arr ay and table aliases

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]

Re: mysql_fetch_arr ay and table aliases

alvonsius wrote:[color=blue]
> PS: why don't you use LEFT JOIN ??[/color]

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

--
E. Dronkert

[Paul E Collins]

Re: mysql_fetch_arr ay and table aliases

"alvonsius" <alvonsius.albe rt@gmail.com> wrote:
[color=blue]
> SELECT
> A.name AS name_A,
> B.name AS name_B
> FROM A, B WHERE A.id = B.id[/color]

Yep, thanks. The aliases make the query that little bit longer, but
it's not a big problem.
[color=blue]
> PS: why don't you use LEFT JOIN ??[/color]

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.