Find text in a ticket !, not only the subject !

[lupolo]

Hello ,

I wan't to search all the records and not only the subject ..

I have changed the following from the find_tickets.php:
--->
case 'subject':
$extra = hesk_input($_GET['subject'],$hesklang['enter_subject']);
$sql = 'SELECT message FROM '.$hesk_settings['db_pfix'].'tickets'
. ' WHERE message LIKE \'%'.$extra.'%\''
. ' UNION'
. ' SELECT message FROM hesk_replies'
. ' WHERE message LIKE \'%'.$extra.'%\''
. ' UNION'
. ' SELECT message FROM hesk_notes'
. ' WHERE message LIKE \'%'.$extra.'%\''
. ' ';
break;
<---

But it will give me a error that i couldent execute the SQL statemant, i have execute the above with phpmyadmin and that worked ..

What else must i change ?

Guy

[Klemen]

Turn Debug mode ON in settings and see what error you get then.

[lupolo]

The code i use to search on other fields: (near line 90)
-->
case 'subject':
$extra = hesk_input($_GET['subject'],$hesklang['enter_subject']);
$sql = 'SELECT message FROM '.$hesk_settings['db_pfix'].'tickets'
. ' WHERE message LIKE \'%'.$extra.'%\''
. ' UNION'
. ' SELECT message FROM '.$hesk_settings['db_pfix'].'replies'
. ' WHERE message LIKE \'%'.$extra.'%\''
. ' UNION'
. ' SELECT message FROM '.$hesk_settings['db_pfix'].'notes'
. ' WHERE message LIKE \'%'.$extra.'%\''
. ' ';
echo $sql;
break;
<--

The strange thing is wenn i run this command within PhpMyAdmin (but with the normal table names), it will give me the right output ..

The echo will give me the folowing:
SELECT message FROM hesk_tickets WHERE message LIKE '%bde%' UNION SELECT message FROM hesk_replies WHERE message LIKE '%bde%' UNION SELECT message FROM hesk_notes WHERE message LIKE '%bde%'

This is also what i use within phpmyadmin and works great ! (bde is the search text).

The Debug errors:

--> (search on subject)
The Debug mode ON, will give:
Can't execute SQL: SELECT message FROM hesk_tickets WHERE message LIKE '%bde%' UNION SELECT message FROM hesk_replies WHERE message LIKE '%bde%' UNION SELECT message FROM hesk_notes WHERE message LIKE '%bde%' ORDER BY `status` ASC, `priority` ASC

MySQL zegt:
Unknown column 'status' in 'order clause'

AND (search on subject)

Het lukt niet SQL te draaien: SELECT * FROM `hesk_tickets` WHERE

MySQL zegt:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

[Klemen]

Try deleting

Code: Select all

 ORDER BY `status` ASC, 

[lupolo]

Try deleting

Code: Select all

 ORDER BY `status` ASC, 

You mean this line:

/* $sql .= ' ORDER BY `status` ASC, `priority`'; */

This have no effect :
Het lukt niet SQL te draaien: SELECT * FROM `hesk_tickets` WHERE ASC

MySQL zegt:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ASC' at line 1

[Klemen]

No, I mean just the exact code I mentioned (including the trailing comma).

[lupolo]

No, I mean just the exact code I mentioned (including the trailing comma).

Done that:
else
{
/* $sql .= ' ORDER BY `status` ASC, `priority`'; */
$sql .= '`priority`';
$sort = 'status';
}

if (isset($_GET['asc']) && $_GET['asc']==0)



Error:
Het lukt niet SQL te draaien: SELECT * FROM `hesk_tickets` WHERE `priority` ASC

MySQL zegt:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ASC' at line 1

My php: http://www.ace-europe.nl/helpdesk/admin ... ickets.zip

With the above changes !

[Klemen]

Change

Code: Select all

else 
{ 
/* $sql .= ' ORDER BY `status` ASC, `priority`'; */ 
$sql .= '`priority`'; 
$sort = 'status'; 
} 

to this

Code: Select all

else 
{ 
$sql .= ' ORDER BY `priority` '; 
$sort = 'status'; 
} 

If that doesn't work you'll have to perform search within each table independently (without UNION, a new query for each table).

[lupolo]

Change

Code: Select all

else 
{ 
/* $sql .= ' ORDER BY `status` ASC, `priority`'; */ 
$sql .= '`priority`'; 
$sort = 'status'; 
} 

to this

Code: Select all

else 
{ 
$sql .= ' ORDER BY `priority` '; 
$sort = 'status'; 
} 

If that doesn't work you'll have to perform search within each table independently (without UNION, a new query for each table).

Diden't help .. must go for option two than ..

[Klemen]

Maybe a query like this would do the trick, but it's up to you to implement it into your code

Code: Select all

SELECT t1 . * 
FROM `hesk_tickets` AS t1
LEFT JOIN `hesk_replies` AS t2 ON t1.`trackid` = t2.`replyto` 
WHERE t1.`subject` LIKE '%TEST%'
OR t1.`message` LIKE '%TEST%'
OR t2.`message` LIKE '%TEST%'
ORDER BY t1.`status` ASC , t1.`priority`

[lupolo]

Maybe a query like this would do the trick, but it's up to you to implement it into your code

Code: Select all

SELECT t1 . * 
FROM `hesk_tickets` AS t1
LEFT JOIN `hesk_replies` AS t2 ON t1.`trackid` = t2.`replyto` 
WHERE t1.`subject` LIKE '%TEST%'
OR t1.`message` LIKE '%TEST%'
OR t2.`message` LIKE '%TEST%'
ORDER BY t1.`status` ASC , t1.`priority`

To difficult will waint on a new version haha

[Raven]

Hi, I have been reading this thread with continued intrest. I also know that Klemen's time is limited but this is just my two cents, maybe there could be a few options within the next version that allows you to turn on/off searching in different areas (subject, tickets, replies, custom fields, notes, knowledgebase etc...).

Sorry but I have no idea how to write the code to search the correct sections of the database myself or I would have done it already Just my two cents as I said

[lupolo]

Anybody else have patched the search option to search all the text whithin the ticket?

viewtopic.php?t=2746&highlight=search