Search from Multiple Table Query

[Sajesh Mohan]

hi All

 

I have totaly 6 tables . i need to search from these 6 table using keyword search (LIKE Query)

in 6 table common field is eco_id.

fields are eco_id,heading,img,content,tags......(same field are in the all tables)

 

how we will do the search query.

please help me

 

thanks in advance.

 

 

 

[Sajesh Mohan]

 

i need to search from multiple tables using like %keyword% .

these are the tables

 

CREATE TABLE IF NOT EXISTS `knews_economy` (
  `eco_id` int(4) NOT NULL AUTO_INCREMENT,
  `heading` varchar(600) NOT NULL,
  `img` varchar(255) NOT NULL,
  `contents` blob NOT NULL,
  `summary` varchar(600) NOT NULL,
  `tags` varchar(600) NOT NULL,
  `pubdate` varchar(255) NOT NULL,
  `inews` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `status` int(2) NOT NULL COMMENT '1 for active 0 for inactive',
  PRIMARY KEY (`eco_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=45 ;

-- --------------------------------------------------------

--
-- Table structure for table `knews_intl`
--

CREATE TABLE IF NOT EXISTS `knews_intl` (
  `eco_id` int(4) NOT NULL AUTO_INCREMENT,
  `heading` varchar(255) NOT NULL,
  `img` varchar(255) NOT NULL,
  `contents` blob NOT NULL,
  `summary` varchar(600) NOT NULL,
  `tags` varchar(600) NOT NULL,
  `pubdate` varchar(255) NOT NULL,
  `inews` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `status` int(2) NOT NULL COMMENT '1 for active 0 for inactive',
  PRIMARY KEY (`eco_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;

-- --------------------------------------------------------

--
-- Table structure for table `knews_local`
--

CREATE TABLE IF NOT EXISTS `knews_local` (
  `eco_id` int(4) NOT NULL AUTO_INCREMENT,
  `heading` varchar(600) NOT NULL,
  `img` varchar(255) NOT NULL,
  `contents` blob NOT NULL,
  `summary` varchar(600) NOT NULL,
  `tags` varchar(600) NOT NULL,
  `pubdate` varchar(255) NOT NULL,
  `inews` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `status` int(2) NOT NULL COMMENT '1 for active 0 for inactive',
  PRIMARY KEY (`eco_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;

-- --------------------------------------------------------

--
-- Table structure for table `knews_mnews`
--

CREATE TABLE IF NOT EXISTS `knews_mnews` (
  `eco_id` int(4) NOT NULL AUTO_INCREMENT,
  `heading` varchar(255) NOT NULL,
  `img` varchar(255) NOT NULL,
  `contents` blob NOT NULL,
  `tags` varchar(600) DEFAULT NULL,
  `summary` varchar(600) DEFAULT NULL,
  `pubdate` varchar(255) NOT NULL,
  `status` int(2) NOT NULL COMMENT '1 for active 0 for inactive',
  PRIMARY KEY (`eco_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=41 ;

-- --------------------------------------------------------

--
-- Table structure for table `knews_parliment`
--

CREATE TABLE IF NOT EXISTS `knews_parliment` (
  `eco_id` int(4) NOT NULL AUTO_INCREMENT,
  `heading` varchar(600) NOT NULL,
  `img` varchar(255) NOT NULL,
  `contents` blob NOT NULL,
  `tags` varchar(600) NOT NULL,
  `summary` varchar(600) NOT NULL,
  `pubdate` varchar(255) NOT NULL,
  `inews` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `status` int(2) NOT NULL COMMENT '1 for active 0 for inactive',
  PRIMARY KEY (`eco_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

-- --------------------------------------------------------

--
-- Table structure for table `knews_sports`
--

CREATE TABLE IF NOT EXISTS `knews_sports` (
  `eco_id` int(4) NOT NULL AUTO_INCREMENT,
  `heading` varchar(255) NOT NULL,
  `img` varchar(255) NOT NULL,
  `contents` blob NOT NULL,
  `tags` varchar(600) NOT NULL,
  `summary` varchar(600) NOT NULL,
  `pubdate` varchar(255) NOT NULL,
  `inews` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `status` int(2) NOT NULL COMMENT '1 for active 0 for inactive',
  PRIMARY KEY (`eco_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;

how it is doing ? please help me

 

thanks in advance

 

[awjudd]

Why is this separated out into separate tables?  It looks to me that they could all be stored in one table and add in an extra column which is the news type (i.e. sports, international, local).  Then you would just be doing a query on one table ...

 

Otherwise you would likely want to UNION each of the selects together.

 

~juddster

[Sajesh Mohan]

i need to display one news seperatly  on the pages and need to select from admin side. thats why why make tables like this.

pls tell me the solution .how it possible to solve this issue

thanks

[fenway]

That's what FULLTEXT is for.

[awjudd]

On the separate pages you would grab where a specific news type is set.

 

SELECT * FROM knews_local WHERE summary LIKE '%keyword%'
UNION
SELECT * FROM knews_mnews WHERE summary LIKE '%keyword%'

[fenway]

Sure -- but for anything other than a single, correctly-spelled word, you're S.O.L.

[awjudd]

I agree entirely

 

~juddster