how to select data from multible columns into one accross tables
I am working on a web-based SMS system that works with Ozeki-NG SMS server. The Ozeki server stores records of received SMS messages (with their recipients’ and senders’ numbers) in a WAMP server based database table named ozekimessagein (id [PK], sender, receiver, message, senttime, receivedtime, status, msgtype, operator) and polls another table called ozekimessageout (id [PK], sender, receiver, msg, receivedtime, msgtype, operator) for outgoing messages to send to a number in its “receiver” column for which the “status” column has the value ‘send’ – it modifies this value upon sending or failure to send the message - in the ozekimessageout table.
The third table in the puzzle is one named books containing columns (id [PK], title, author, callnumber, number of copies).
I thus need a trigger that is compatible with MySQL Server Version 5.1.36-community-log to use the data in the “message” column in every new record (AFTER INSERT) in ozekimessagein to search for matching author or title in the books table and, select data from all the fields whose title or author columns’ data match that in the message column. The selected items should be put into the message field of ozekimessageout and the row updated with the rest of the fields filled in appropriately so that the Ozeki-NG SMS Server can send it.
What could be wrong with this code?
DROP TRIGGER IF EXISTS processReply ;
DELIMITER$$
CREATE TRIGGER processReply AFTER INSERT ON ozekimessagein
FOR EACH ROW
BEGIN
WHEN (SELECT * FROM books WHERE title = @NEW.msg)!='';
INSERT INTO ozekimessageout.msg SELECT * FROM books WHERE title = NEW.msg;
UPDATE ozekimessageout SET NEW.msg=CONCAT(SELECT * FROM books WHERE title = @NEW.msg), NEW.status='send' WHERE sender=NEW.receiver
END IF ;
IF (SELECT * FROM books WHERE author = @NEW.msg)!='' ; THEN
INSERT INTO ozekimessageout (sender,receiver,receivedtime,msgtype,operator) VALUES (NEW.receiver,NEW.sender,NEW.receivdtime,NEW.msgtype,NEW.operator) ;
UPDATE ozekimessageout SET NEW.msg=SELECT * FROM books WHERE title = @NEW.msg, NEW.status='send' ;
END IF ;
ELSE
INSERT INTO ozekimessageout (sender,receiver,msg,receivedtime,status,msgtype,operator) VALUES (NEW.receiver,NEW.sender,'Sorry,your search Item did not match any Book or Author in the Library',NEW.receivdtime,'send',NEW.msgtype,NEW.operator) ;
END$$
DELIMITER;