Welcome, %1$s. Please login or register.
Did you miss your activation email?



 

2637 705 - 2601 - : smuddel

eTicket CommunitySupportTips & Tricks (Moderators: jason, Hummdis)Topic: Improving Search Results For 'Query'
: [1]   
: Improving Search Results For 'Query'  ( 7558 )
0 and 1 Guest are viewing this topic.
DanBaker
New Member
*

Karma: +0/-0
Offline Offline

: 1


« : December 02, 2011, 02:02:39 AM »

Thought that someone might find this helpful.  Using a temporary table, I've enabled boolean searches, sorting by accuracy, quoted strings, etc.  My users are very happy with these improvements.  Here's the query:

DROP TEMPORARY TABLE IF EXISTS temp_table;
CREATE TEMPORARY TABLE temp_table (ID INT(6),
status ENUM('new','onhold','custreplied','awaitingcustomer','reopened','closed','raised'),
timestamp TIMESTAMP,
cat INT(5),
subject VARCHAR(255),
rep INT(5),
email VARCHAR(255),
priority TINYINT(1),
name VARCHAR(255),
fullTextString LONGTEXT);
ALTER TABLE temp_table ADD FULLTEXT INDEX (fullTextString);
ALTER TABLE temp_table DISABLE KEYS;
INSERT INTO temp_table
SELECT DISTINCT tickets. ID AS ID,
tickets. status,
tickets. timestamp as timestamp,
tickets. cat AS cat,
tickets. subject AS subject,
tickets. rep AS rep,
tickets. email AS email,
priority,
tickets. name AS name,
CONCAT_WS(' ', tickets. email, tickets. name, tickets. subject, ticket_messages. message, ticket_answers. message, tickets. subject, tickets. name) as fullTextString
FROM ( ( tickets LEFT JOIN ticket_messages ON ticket_messages. ticket = tickets. ID ) LEFT JOIN ticket_answers ON ticket_answers. reference = ticket_messages. ID );
ALTER TABLE temp_table ENABLE KEYS;
SELECT DISTINT ID, MATCH(temp_table. fullTextString) AGAINST ('dan baker') AS Relevance, status, timestamp, cat, subject, rep, email, priority, name FROM temp_table WHERE MATCH(temp_table. fullTextString) AGAINST ('+dan +baker' IN BOOLEAN MODE) HAVING Relevance > 0. 2 ORDER BY Relevance DESC;

Searches for "Dan Baker" (unquoted).  Let me know, if someone is interested I will post the PHP modification that need to be made to main. php to implement this query.

Dan
Hummdis
Moderator
Super Member
*****

Karma: +13/-0
Offline Offline

: 625



« #1 : February 15, 2012, 01:01:12 AM »

Hi Dan,
Thank you for taking the time to post this.  I'm most certainly interested in the change that you've made for searching. Would you mind posting the PHP for 'main.php' so that I can see it?

If I don't hear from you via the forum (since it appears broken and cannot post as of right now), I'll hit you up via email in a couple days.

Cheers,
Hummdis

Don't PM me directly for help.  Post to the forums, that's what they are for after all.  PM's to me that request help will be ignored.

Hummdis Web Design - Freelance Website Design & IT Consulting
: [1]   
eTicket CommunitySupportTips & Tricks (Moderators: jason, Hummdis)Topic: Improving Search Results For 'Query'
: