Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

[MOD] Displaying and Searching in Knowledge Base

Hi all,

Some of you (I know, not all of you ;) ) must be have read my post answering the question regarding to the subject of this current topic above, even with the different subject that you can find here:

http://www.osticket.com/forums/showthread.php?t=2002

Now, if you have clicked that link above, you have now read my post in that topic, right?

Allow me to re-discuss in the topic #2002 in this new current topic for more detail. This MOD and topic #2002 is talking about improvement for displaying and searching in Knowledge Base in Admin Panel, and this was solution from me regarding to topic #2002:

Open \include\staff\premade.inc.php, 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['a']=='search') {
if(!$_REQUEST['query'] || strlen($_REQUEST['query'])<3) {
$errors['err']=I_S_PREM_SEARCH_TERM;
}else{
//fulltext search.
$search=true;
$qstr.='&a='.urlencode($_REQUEST['a']);
$qstr.='&query='.urlencode($_REQUEST['query']);
$searchfor = $_REQUEST['query'];
$where=\" WHERE (premade.title LIKE '%$searchfor%'
OR premade.answer LIKE '%$searchfor%')
AND d.dept_id = premade.dept_id \";
if($_REQUEST['dept'])
$where.=' AND d.dept_id='.db_input($_REQUEST['dept']);
}
}
if (!isset($where)) $where = 'WHERE d.dept_id = premade.dept_id';
//I admit this crap sucks...but who cares??


Okay. Until here, there is no problem if we search for or displaying premade answer that belongs to one certain department. It means: this MOD above works good if we only have premade answer in kb_premade table that related "one-to-one" to department table, until I found a little "missing" from that MOD! ;)

What was that? Here is the answer: If we have premade answer which has a relationship "one-to-many" to department table, then the premade answer never show up in the premade list at all. :(

Still confused? For example: Let's say that I have a premade answer with title "What is osTicket System" and this premade answer belongs to 4 departments. It means, the "dept_id" value in "kb_premade" table is "0" (0=all dept), whereas there is no "dept_id" with value "0" in "department" table, because the "dept_id" in "department" table is an auto-increment field.

So, this is my new MOD regarding for that:
FIND:
        $where=\" WHERE (premade.title LIKE '%$searchfor%' 
OR premade.answer LIKE '%$searchfor%')
AND d.dept_id = premade.dept_id \";


REPLACE WITH:
        $where=\" WHERE (premade.title LIKE '%$searchfor%' 
OR premade.answer LIKE '%$searchfor%')
AND (d.dept_id = premade.dept_id OR premade.dept_id = 0) \";



FIND:
if (!isset($where)) $where = 'WHERE d.dept_id = premade.dept_id';


REPLACE WITH:
if (!isset($where)) $where = 'WHERE d.dept_id = premade.dept_id 
OR premade.dept_id = 0';


Now, if you see after modified that WHERE clause above, in that case of my example above, it looks like that we have 4 premade answers for that 4 department. This is common because if we look at the "Category/Dept" column in the list, each of them has the different department.

The other words: if we don't add with " OR premade.dept_id = 0" clause, then the one-to-many premade answer will never show up in the list even it exists in database.

That's all everyone. Hope this is helpful for you. Cheers! :D

Best regards,
Masino Sinaga
Sign In or Register to comment.