Database keywords

Question: What is the best way to do the following:
1. When I receive a message I want it stored in a database based on keywords. For example if the keyword is 'Pizza', it will be stored in the database, addressbook for a Pizza Parlor. If the keyword is Dance it will be stored in the database for a Dance School. These databases will be used to send messages each group as needed.

Answer: I assume you use the MySQL database server. Here is what you should do
Modify you inbox table definition to include the keyword field: 

mysql> drop table ozekimessagein

mysql> CREATE TABLE ozekimessagein (
  id int(11) NOT NULL auto_increment,
  sender varchar(30) default NULL,
  receiver varchar(30) default NULL,
  msg varchar(160) default NULL,
  senttime varchar(100) default NULL,
  receivedtime varchar(100) default NULL,
  operator varchar(100),
  msgtype varchar(160) default NULL,
  reference varchar(100) default NULL,
  keyword varchar(160) default NULL,
  PRIMARY KEY (id)
  );


Modify the insert template in the SQL plugin configuration (Figure 1) form to the following text: 
insert into ozekimessagein (operator,sender,receiver,msg,
senttime,receivedtime,msgtype,reference,keyword) values
('$operator', '$sender','$receiver','$msg','$senttime',
'$receivedtime','$msgtype','$reference','$keyword')

Figure 1 - SQL template configuration

To send a message to everybody who has sent a message containg the "Dance" keyword, you should use the following SQL statement: 

mysql> insert into ozekimessageout (receiver,msg,status);
select sender,'Come to dance tonight','send'
from ozekimessagein where keyword='DANCE' group by sender;
To provide unsubscribe functionality you need to set up a database trigger. (Note: this requires MySQL 5.0 or later) 

mysql> DELIMITER |;

drop trigger unsubscribe |
CREATE TRIGGER unsubscribe AFTER INSERT ON ozekimessagein
  FOR EACH ROW BEGIN
    IF New.msg like 'CANCEL DANCE' THEN
       DELETE FROM ozekimessageout WHERE keyword='DANCE' and sender=New.sender;
  END;
|

More information