Jump to content

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


noesac
 Share

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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%' 

	);

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More