Sql error in knowledge base search

delia

I'm getting an error when searching the knowledge base:

- Column 'dept_id' in where clause is ambiguous

Since I haven't seen anyone else complaining about this, I'm pretty mystified as to what is unique about this particular installation. Using latest version of osticket.

masino_sinaga

Here is the solution...

I didn't get an error message using that SQL, but I was wondering the search always gave me result: Query returned 0 results, even if I entered the keyword that exists in the premade table (title or answer field).

I am also not sure whether that SQL is right or wrong, cause I have tried by using MySQL tools, the query always returned 0 record. Just FYI, I am using MySQL version 5.0.51b.

So I decided to make my own modification, and I'm sure this will also fix your problem, because I have tried it before release this solution here... and it works good now! :)

Let's getting started now.

Find:

//List premade answers.

Select or highlight all code from that comment until the comment below

//I admit this crap sucks...but who cares??

then replace with all code below... or... Finally, you should have the complete code just like this (see all the complete code below):

//List premade answers.

$select='SELECT DISTINCT(d.dept_name), premade.* ';

$from='FROM '.KB_PREMADE_TABLE.' premade, '.DEPT_TABLE.' d ';

//make sure the search query is 3 chars min...defaults to no query with warning message

if($_REQUEST=='search') {

if(!$_REQUEST || strlen($_REQUEST)<3) {

$errors=I_S_PREM_SEARCH_TERM;

}else{

//fulltext search.

$search=true;

$qstr.='&a='.urlencode($_REQUEST);

$qstr.='&query='.urlencode($_REQUEST);

$searchfor = $_REQUEST;

$where=" WHERE (premade.title LIKE '%$searchfor%'

OR premade.answer LIKE '%$searchfor%')

AND d.dept_id = premade.dept_id ";

if($_REQUEST)

$where.=' AND d.dept_id='.db_input($_REQUEST);

}

}if (!isset($where)) $where = 'WHERE d.dept_id = premade.dept_id';
//I admit this crap sucks...but who cares??

Hope this helpful also to everyone.

Thanks for remind me about this, so I focused to check that SQL and make sure this search feature works. ;)

Best regards,

Masino Sinaga

craigreilly

I think you could do this to your SQL:

SELECT count(*) FROM ost_kb_premade premade LEFT JOIN ost_department

USING(dept_id) WHERE MATCH(title,answer) AGAINST ('PDF') AND ost_department.dept_id=1;

Which was add a table name before dept_id so it is no longer ambiguous.

masino_sinaga

I think you could do this to your SQL:
SELECT count(*) FROM ost_kb_premade premade LEFT JOIN ost_department
USING(dept_id) WHERE MATCH(title,answer) AGAINST ('PDF') AND ost_department.dept_id=1;
Which was add a table name before dept_id so it is no longer ambiguous.

I have tested that SQL query, but it always return 0 result (there is no error). I have tried to search with a keyword that exist in my ost_kb_premade table (let's say I enter sample as my keyword and assuming the text in title field is: "What is osTicket (sample)?") and it return 0 result..., whereas this query below will work as good as we want (this is my MOD that I sent at post #2 above):

SELECT DISTINCT(d.dept_name), premade.* FROM ost_kb_premade premade, ost_department d WHERE (premade.title LIKE '%sample%' OR premade.answer LIKE '%sample%') AND d.dept_id = premade.dept_id LIMIT 0,10

Best regards,

Masino Sinaga

delia

I had tried putting in the table names but they are only ambiguous if they exist in both tables - not the case.

I'm going to try the code rewrite above from Masino - that obviously is a fix but I'm still wondering why the code as written doesn't work for some and must work for others. This is something that needs to be investigated and documented.

delia

Well, that worked like a charm. Thanks, Masino.

masino_sinaga

You're welcome, delia.

It's nice to hear good news from you.

Best regards,

Masino Sinaga

masino_sinaga

I found a little bit missing...

Hi delia,

Please read the thread from the link below for the newest change:

http://www.osticket.com/forums/showthread.php?t=2046(http://www.osticket.com/forums/showthread.php?t=2046)

Thanks.

Best regards,

Masino Sinaga