Wed 11 Feb 2009 04:29:31 PM UTC, original submission:
Since mysql 5, the search function in RUQueue started throwing an error. The following query:
error with query: SELECT *,
ticket.id as 'table_id',
ticket.id as 'ticket.id',
first_comment.subject as 'first_comment.subject',
first_comment.subject as 'table_subject',
#ticket.requester as 'ticket.requester',
user.name as 'table_requester',
ticket.current_status as 'ticket.current_status',
ticket.current_status as 'table_status',
ticket.queue as 'ticket.queue',
ticket.queue as 'table_queue',
ticket.date_created as 'ticket.date_created',
DATE_FORMAT(ticket.date_created, '%m-%d-%Y') as 'table_created',
last_comment.date_created as 'last_comment.date_created',
DATE_FORMAT(last_comment.date_created, '%m-%d-%Y') as 'table_last',
ticket.owner as 'table_owner',
ticket.owner as 'ticket.owner' FROM ticket, comment as last_comment, comment as first_comment
LEFT JOIN user on (ticket.user_id = user.id) WHERE 1=1
and ticket.last_comment_id=last_comment.id
and ticket.first_comment_id=first_comment.id and ticket.owner = 'username' GROUP BY ticket.id ORDER BY ticket.id DESC
Would give you this error:
MySQL said: #1054 - Unknown column 'ticket.user_id' in 'on clause'
The problem was with a change in mysql 5 join syntax. To fix this error, open the /web-interface/search.php with your text editor, and replace the following lines:
// Create the tables part of the query
$tables = "ticket, comment as last_comment, comment as first_comment
LEFT JOIN user on (ticket.user_id = user.id)";
with this (add parentheses):
// Create the tables part of the query
$tables = "(ticket, comment as last_comment, comment as first_comment)
LEFT JOIN user on (ticket.user_id = user.id)";
And you should be all set.
(Or I've attached the updated search.php file)
|