Hi,
After trying to find the best way to have a global search function, I
decided to implement the following solution ... and it works
brilliantly.
Create a search table:
------------------------------------
CREATE TABLE `search` (
`id` int(11) NOT NULL auto_increment,
`model` varchar(300) default NULL,
`rel_id` int(11) default NULL,
`name` varchar(300) default NULL,
`description` text,
`active` int(1) default '1',
`modified` datetime NOT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `search_index` (`name`,`description`)
) ENGINE=MyISAM;
------------------------------------
The model points the the table, ie news, and the rel_id to the id, ie.
news.id
For each table you want to search, setup a 3 MYSQL triggers ie.
INSERT TRIGGER:
------------------------------------
DELIMITER $$
CREATE
TRIGGER `News Insert to Search` AFTER INSERT ON `news`
FOR EACH ROW BEGIN
INSERT INTO `search` SET `model` = 'News', `active` =
New.active, `name` = NEW.name, `description` = NEW.body, `rel_id` =
NEW.id;
END;
$$
DELIMITER ;
------------------------------------
UPDATE TRIGGER:
------------------------------------
DELIMITER $$
CREATE
TRIGGER `News Update to Search` AFTER UPDATE ON `news`
FOR EACH ROW BEGIN
DELETE FROM `search` WHERE `model` = 'News' AND `rel_id` = OLD.id;
INSERT INTO `search` SET `model` = 'News', `name` = NEW.name,
`description` = NEW.body, `rel_id` = NEW.id, `active` = New.active;
END;
$$
DELIMITER ;
------------------------------------
DELETE TRIGGER:
------------------------------------
DELIMITER $$
CREATE
TRIGGER `News Delete to Search` AFTER DELETE ON `news`
FOR EACH ROW BEGIN
DELETE FROM `search` WHERE `model` = 'News' AND `rel_id` =
OLD.id;
END;
$$
DELIMITER ;
------------------------------------
The trigger will be executed as soon as you add a new news story, or
update a news story or delete a news story.
So when you run a global search, you simply search through the search
table, with one query ...
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
