problem with ambiguous field names (mysql 4.1)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
jolinar
Forum Commoner
Posts: 61
Joined: Tue May 24, 2005 4:24 pm
Location: in front of computer

problem with ambiguous field names (mysql 4.1)

Post by jolinar »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Does anyone know how to get around the "ambiguous field" bug in MySQL 4.1?

The nature of the beast is as follows, when executing the following query:

[syntax="sql"]SELECT comment_id,comment,date,user_name,title FROM ((blog_comment INNER JOIN users USING (user_id)) INNER JOIN blog_entry USING(blog_id)) ORDER BY date DESC;
The following error is returned:
Column 'date' in field list is ambiguous
Does anyone know any workarounds for this problem? (It certainly is an annoying one, though I'm probably overlooking something simple)

If needed, the structure of the tables concerned is as follows: (lifted from mysqladmin backup)

Code: Select all

CREATE TABLE  `sobek`.`blog_comment` (
  `comment_ID` int(10) unsigned NOT NULL auto_increment,
  `blog_ID` int(10) unsigned NOT NULL,
  `user_ID` int(10) unsigned NOT NULL,
  `comment` text NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY  (`comment_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE  `sobek`.`blog_entry` (
  `blog_ID` int(10) unsigned NOT NULL auto_increment,
  `title` text NOT NULL,
  `content` text NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY  (`blog_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE  `sobek`.`users` (
  `user_name` varchar(45) NOT NULL,
  `pass` varchar(60) NOT NULL,
  `real_name` varchar(45) NOT NULL,
  `user_level` int(10) unsigned NOT NULL,
  `user_email` varchar(45) NOT NULL,
  `user_date_joined` datetime NOT NULL,
  `user_ID` int(10) unsigned NOT NULL auto_increment,
  `last_login` datetime NOT NULL,
  PRIMARY KEY  (`user_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 4096 kB';

feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

It's not ambiguous, it's a reserved word.

You shouldn't use reserved words as field names for starters but if you absolutely MUST use them then they have to be backticked.

Code: Select all

SELECT comment_id, comment, `date`, user_name, title FROM ((blog_comment INNER JOIN users USING (user_id)) INNER JOIN blog_entry USING(blog_id)) ORDER BY `date` DESC;
You can get away without backticking date in the SELECT area but it must be backticked in the ORDER BY with the way you are using the query. Honestly you shouldn't be using date as a field anyway. If this is your own project you should rename those fields to something different.

http://dev.mysql.com/doc/refman/4.1/en/ ... words.html
User avatar
jolinar
Forum Commoner
Posts: 61
Joined: Tue May 24, 2005 4:24 pm
Location: in front of computer

Post by jolinar »

Thanks :D "AKA Panama Jack"

Since it's my own project I'll rename the cols in question, preferably before the code police arrive :(
Post Reply