Error 1052

[Mijae]

Database error in vBulletin 3.0.0 Beta 4:

Invalid SQL: 
		### GET MOST RECENT ANNOUNCEMENT ###
		SELECT
			announcementid, startdate, title, views,
			user.username, user.userid, user.usertitle, user.customtitle
		FROM announcement AS announcement
		LEFT JOIN user AS user ON(user.userid = announcement.userid)
		WHERE startdate <= 1059898427
			AND enddate >= 1059898427
			AND forumid IN (3,31,-1)
		ORDER BY startdate DESC
		LIMIT 1
	
mysql error: Column: 'views' in field list is ambiguous

mysql error number: 1052

Whats wrong?

[SmartGnome]

I ran the query and it give me no problems.

mysql error: Column: 'views' in field list is ambiguous

This error you get when a field in the select part is available in both tables and the the option is to replace it with announcement.views

as far as I can see the views is only in the announcement table.

[Mijae]

And how do I fix that?

[Mijae]

Anyone?

[rylin]

change the query to use the tablename.
instead of

SELECT
announcementid, startdate, title, views,
user.username, user.userid, user.usertitle, user.customtitle

use

SELECT
announcement.announcementid, announcement.startdate, announcement.title, announcement.views,
user.username, user.userid, user.usertitle, user.customtitle

[Mike Sullivan]

Or remove the hack that added that column (it's probably not being used/doesn't exist for now anyway).

[Matthew Gordon]

Announcement views are standard in vB3.

[Mike Sullivan]

User views aren't though, and that appears to be the problem (the user.view column).

[Matthew Gordon]

It doesn't seem like that's hacked, as this is in my unhacked vB3 Beta 4 forumdisplay.php:

	if ($announcement = $DB_site->query_first("
		### GET MOST RECENT ANNOUNCEMENT ###
		SELECT
			announcementid, startdate, title, views,
			user.username, user.userid, user.usertitle, user.customtitle
		FROM " . TABLE_PREFIX . "announcement AS announcement
		LEFT JOIN " . TABLE_PREFIX . "user AS user ON(user.userid = announcement.userid)
		WHERE startdate <= " . TIMENOW . "
			AND enddate >= " . TIMENOW . "
			AND " . fetch_forum_clause_sql($foruminfo['forumid'], 'forumid') . "
		ORDER BY startdate DESC
		LIMIT 1
	"))
	{

[Mike Sullivan]

The hack adds a column. It has nothing to do with the file -- the change to the database is what's causing the problem.

[Matthew Gordon]

*backs away slowly*

I see what you mean now...

[Mijae]

I have no idea what hack did that to my database, but I just removed that whole lot of code since I never use announcements.

[Craigr]

I had the same problem with my board, i simply renamed the views column in the database under the user table to viewz.

This worked fine for me. I think the views table was from a hack i installed while running 2.2.9

Craig