PHPJunkyard support forum

how to add a EXTRA search option for in the message in table "hesk_replies / message" ?

into find_tickets.php

Some one already made a change for this.

See also:
viewtopic.php?t=2489&highlight=heskreplies

How can I add the message from the replies into the same search? See my code untill the message case

--------------------------------------------------------->

define('IN_SCRIPT',1);
define('HESK_PATH','../');

/* Get all the required files and functions */
require(HESK_PATH . 'hesk_settings.inc.php');
require(HESK_PATH . 'inc/common.inc.php');
require(HESK_PATH . 'inc/database.inc.php');

hesk_session_start();
hesk_dbConnect();
hesk_isLoggedIn();

/* Check permissions for this feature */
hesk_checkPermission('can_view_tickets');

/* Print header */
require_once(HESK_PATH . 'inc/header.inc.php');

$sql = 'SELECT * FROM `'.hesk_dbEscape($hesk_settings['db_pfix']).'tickets` WHERE ';

if (!empty($_GET['archive']))
{
$archive=1;
$sql .= "`archive`='1' AND ";
}
else
{
$archive=0;
}

$sql .= hesk_myCategories();
$sql .= " AND ";

/* Get all the SQL sorting preferences */
if (!isset($_GET['what']))
{
hesk_error($hesklang['wsel']);
}
$what = hesk_input($_GET['what'],$hesklang['wsel']);

switch ($what)
{
case 'trackid':
$extra = hesk_input($_GET['trackid'],$hesklang['enter_id']);
$sql .= "`trackid` = '".hesk_dbEscape($extra)."' ";
break;
case 'name':
$extra = hesk_input($_GET['name'],$hesklang['enter_name']);
$sql .= "`name` LIKE '%".hesk_dbEscape($extra)."%' ";
break;
case 'dt':
$extra = hesk_input($_GET['dt'],$hesklang['enter_date']);
if (!preg_match("/\d{4}-\d{2}-\d{2}/",$extra))
{
hesk_error($hesklang['date_not_valid']);
}
$sql .= "(`dt` LIKE '".hesk_dbEscape($extra)."%' OR `lastchange` LIKE '".hesk_dbEscape($extra)."%')";
break;

case "message":
$extra=hesk_input($_GET['message'],$hesklang['enter_message']);
$sql .= "`message` LIKE '%$extra%' ";
break;

<---------------------------------------------------------

How can i execute the following sql command wenn i use the own creates search case "Message"

--->

SELECT t1 . * FROM `hesk_tickets` AS t1 LEFT JOIN `hesk_replies` AS t2 ON t1.`id` = t2.`replyto` WHERE t1.`subject` LIKE '%$extra%' OR t1.`message` LIKE '%$extra%' OR t2.`message` LIKE '%$extra%'

<---

It also have to be orderd with the following line:

--->

ORDER BY t1.`status` ASC , t1.`priority`

<---

But wenn i change the line:
$sql .= ' ORDER BY `status` ASC, `priority`';

with the above it will give me the following eror:

Can't execute SQL: ORDER BY t1.`status` ASC , t1.`priority` ASC

MySQL said:
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 'ORDER BY t1.`status` ASC , t1.`priority` ASC' at line 1

wenn choosing the message search

Somebody, can help me out with this?

*kick*

As you know I don't provide much support for custom modifications, but if you have "Can't execute SQL: ORDER BY t1.`status` ASC , t1.`priority` ASC" it's obvious most of your SQL query is missing (just the last part is sent to the MySQL). Perhaps you forgot a dot in "$sql .=" somewhere? Can't say where for sure from the info you posted...

Will try to put in into the new 2.2 version! But the only thing i wanna search in:

- Message customer
- Message Staff
- Note Staff

Will update wenn i will try the other time!

Thanks for updating!