two field with the same name

rushenas

Hi
I need to retrieve results from 3 tables. 2 of them has a field, named category (I mean products.category and category.category). When I want to echo the query result, PHP shows me the second one value. But I need to show users the first value. Since the number of fields are huge, it would be a hard job if I list all of field names. So it would be so better to use select * ....
How can I specify that I wanna show the second one field value?

bradgrafelman

How are you retrieving the data? Via a _fetch_assoc function? You should just be able to do $result['products.category'] unless I'm mistaken.

Either way - have you tried doing a [man]print_r/man on the array to see what indeces it has?

rushenas

I tried to use print_r(), and I noticed that it only contains category.category, not products.category. And using $result[products.category] doesn't work.

bogu

Use alias for thoes fields...

Piranha

First up it is $result['products.category'] and not $result[products.category]. Don't forget the ' signs.

Second, as bogu says, use alias. And avoid SELECT *, if the database changes in the future it may cause trouble. Or what if another row is added with the same name, then you would probably get this same problem all over again. But if you really want to use it, try this (I don't know how it works, haven't tried it myself).

SELECT *, products.category as pcat FROM...

Roger_Ramjet

If you are joining on that field and have USING(category) as the join condition then you will only get that column listed once in the PK table. If you used the ON(c.category=p.category) syntax you will get it listed twice in your results.

If you are not joining on that column then it should be listed twice.

Why does it mater which table it is listed in: it will be the same value anyway?

[note] This is one area where mysql differs from most other DBs which list results verbosely, ie table.column, and you have to address them as such in your code. I suppose the developers were trying to save keystrokes, web developers being notoriously lazy in that respect. The downside is what you are experiencing here, that columns do not come with their table identity attached.