Ambiguous columns with JOIN query

[4string]

I'm trying to join two tables in mySQL and both tables have a commonly named column. It works just as I want in PHPmyAdmin, but when I use it from a php script I get 'undefined' for widget.widget_id.

SELECT widget_name, widget_description, widget_category, widget.widget_id
FROM user_widgets
LEFT JOIN widget ON widget.widget_id
WHERE user_id =$user_id AND user_widgets.widget_id = widget.widget_id

I'm new to DB's and this is my first ever join query. What am I doing wrong?

TIA

[s1dev]

A couple of things:
When joining tables it is always a good idea to explictly name your columns like tablename.columnname.
Anyway, I think your problem lies in your LEFT JOIN clause.
I think it should be:

LEFT JOIN widget ON user_widgets.widget_id=widget.widget_id

You can remove this comparison from your WHERE clause also.

[4string]

A million thanks! It worked!

I think I get it now, too. (I hope.)

[Lord Majestic]

When joining tables it is always a good idea to explictly name your columns like tablename.columnname.

I'd say its better to use aliases that you can set in most databases by specifying an alias after table name in the query ie: select Alias.* from table_a Alias

Aliases are better since you won't have to change lots of things in the query if you change table name.

[s1dev]

Yes aliases are a possible use here too. I was emphasizing the explicit reference to the table in the column.
Aliases are good to use when you are joining a table to itself, or using a subquery with the same table, or to save typing in big queries with long table names. Using an alias is NOT going to save much time if the table name changes. Why? Using the search and replace feature of your editor will change 1 or 100 table names in your code.

[Lord Majestic]

> Why? Using the search and replace feature of your
> editor will change 1 or 100 table names in your code.

Yeah you could do that -- I did not mention the main benefit of aliases -- readability. If you start using table names everywhere code will become less readable (especially if you deal with lots of columns in joining).