Filter any messages from the "Customer messages" page that contain a certain string?

[noesac]

I want to hide any message that contains the text "IP Address".

I changed this:

       SELECT m.id_message, m.id_cart, m.id_employee, IF(m.id_order > 0, m.id_order, \'--\') id_order, m.message, m.private, m.date_add, CONCAT(LEFT(c.`firstname`, 1), \'. \', c.`lastname`) AS customer,
       c.id_customer, count(m.id_message) nb_messages, (SELECT message FROM '._DB_PREFIX_.'message WHERE id_order = m.id_order ORDER BY date_add DESC LIMIT 1) last_message,
       (SELECT COUNT(m2.id_message) FROM '._DB_PREFIX_.'message m2 WHERE 1 AND m2.id_customer != 0 AND m2.id_order = m.id_order AND m2.id_message NOT IN 
       (SELECT mr2.id_message FROM '._DB_PREFIX_.'message_readed mr2 WHERE mr2.id_employee = '.intval($cookie->id_employee).') GROUP BY m2.id_order) nb_messages_not_read_by_me
       FROM '._DB_PREFIX_.'message m
       LEFT JOIN '._DB_PREFIX_.'orders o ON (o.id_order = m.id_order)
       LEFT JOIN '._DB_PREFIX_.'customer c ON (c.id_customer = m.id_customer)
       GROUP BY m.id_order
       ORDER BY '.(isset($orderBy) ? pSQL($orderBy) : 'date_add') .' '.(isset($orderWay) ? pSQL($orderWay) : 'DESC').'
       LIMIT '.intval($start).','.intval($limit));

to this:

       SELECT m.id_message, m.id_cart, m.id_employee, IF(m.id_order > 0, m.id_order, \'--\') id_order, m.message, m.private, m.date_add, CONCAT(LEFT(c.`firstname`, 1), \'. \', c.`lastname`) AS customer,
       c.id_customer, count(m.id_message) nb_messages, (SELECT message FROM '._DB_PREFIX_.'message WHERE id_order = m.id_order ORDER BY date_add DESC LIMIT 1) last_message,
       (SELECT COUNT(m2.id_message) FROM '._DB_PREFIX_.'message m2 WHERE 1 AND m2.id_customer != 0 AND m2.id_order = m.id_order AND m2.id_message NOT IN 
       (SELECT mr2.id_message FROM '._DB_PREFIX_.'message_readed mr2 WHERE mr2.id_employee = '.intval($cookie->id_employee).') GROUP BY m2.id_order) nb_messages_not_read_by_me
       FROM '._DB_PREFIX_.'message m
       LEFT JOIN '._DB_PREFIX_.'orders o ON (o.id_order = m.id_order)
       LEFT JOIN '._DB_PREFIX_.'customer c ON (c.id_customer = m.id_customer)
       GROUP BY m.id_order
       ORDER BY '.(isset($orderBy) ? pSQL($orderBy) : 'date_add') .' '.(isset($orderWay) ? pSQL($orderWay) : 'DESC').'
       LIMIT '.intval($start).','.intval($limit)'
       WHERE m.message NOT IN %ip address%
       );

Didn't work, any suggestions?

[oarecare]

try:

WHERE m.message NOT LIKE '%ip address%'

[noesac]

try:

WHERE m.message NOT LIKE '%ip address%'

 

Thanks I just tried this but the entire page shows up blank now!

 

I think it has to do with my syntax, I added the two lines at the bottom, do you know what I've done wrong here?

 

	$this->_list = Db::getInstance()->ExecuteS('
	SELECT m.id_message, m.id_cart, m.id_employee, IF(m.id_order > 0, m.id_order, \'--\') id_order, m.message, m.private, m.date_add, CONCAT(LEFT(c.`firstname`, 1), \'. \', c.`lastname`) AS customer,
	c.id_customer, count(m.id_message) nb_messages, (SELECT message FROM '._DB_PREFIX_.'message WHERE id_order = m.id_order ORDER BY date_add DESC LIMIT 1) last_message,
	(SELECT COUNT(m2.id_message) FROM '._DB_PREFIX_.'message m2 WHERE 1 AND m2.id_customer != 0 AND m2.id_order = m.id_order AND m2.id_message NOT IN 
	(SELECT mr2.id_message FROM '._DB_PREFIX_.'message_readed mr2 WHERE mr2.id_employee = '.intval($cookie->id_employee).') GROUP BY m2.id_order) nb_messages_not_read_by_me
	FROM '._DB_PREFIX_.'message m
	LEFT JOIN '._DB_PREFIX_.'orders o ON (o.id_order = m.id_order)
	LEFT JOIN '._DB_PREFIX_.'customer c ON (c.id_customer = m.id_customer)
	GROUP BY m.id_order
	ORDER BY '.(isset($orderBy) ? pSQL($orderBy) : 'date_add') .' '.(isset($orderWay) ? pSQL($orderWay) : 'DESC').'
	LIMIT '.intval($start).','.intval($limit)


// added the following two lines (this comment is for the forums only, not in my code)

	'
	WHERE m.message NOT LIKE '%ip address%' 

	);

[rocky]

The quotes aren't properly escaped. Try:

 

'
               WHERE m.message NOT LIKE \'%ip address%\''
               );

[noesac]

No luck, the page just comes up blank when I try that!

 

	LIMIT '.intval($start).','.intval($limit)


	'
               WHERE m.message NOT LIKE \'%ip address%\''

	);

[Mr. Lent]

LIMIT '.intval($start).','.intval($limit).'

WHERE m.message NOT IN '.%ip address%.'

[noesac]

LIMIT '.intval($start).','.intval($limit).'

WHERE m.message NOT IN '.%ip address%.'

 

Doesn't work either! Still shows a blank screen