Monday, August 25, 2008

Search from multiple tables in mysql

CREATE TABLE `tagsearch`.`books` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(200) NOT NULL,
`author` varchar(100) NOT NULL,
`price` float(10,2) NOT NULL,
`comment` text,
`created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`updated_on` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
)

CREATE TABLE `tagsearch`.`tags` (
`id` int(11) NOT NULL auto_increment,
`tagname` varchar(200) NOT NULL,
`counter` int(10) NOT NULL default '0',
`created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`updated_on` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
)

Suppose we have in database these two tables.... and search query is as below.....

select * from books,tags WHERE MATCH(author,comment,tagname) AGAINST ('rails' in boolean mode);

Here, keyword is search from two tables and Match includes the field name of different table and against have search keyword

No comments:

Post a Comment

Contact Me for any help regarding rails/nodejs/php/mysql

Name

Email *

Message *