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(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=='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??
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!
Best regards,
Masino Sinaga