MySQL fulltext search on multiple tables with different fields

Question

I am trying to join multiple tables and perform a full text search on them.

Most of the tables are unrelated but have similar fields.

I have had the fulltext searches working but i need to be able to create links from the results which is the next step but i don't thin k it will work becuase i haven't got enoygh fields to get enough info.

Basically I want to search for the title and the content of each table but i also want to search my forum tables which are topics and messages. The topics and messages tables are linked.

This query will do the trick without querying the forum tables bu t i need to be able to search those tables.

SELECT * FROM (SELECT title, content,
    MATCH(title, content) AGAINST('folk*' IN BOOLEAN MODE)
    as score FROM news WHERE MATCH(title, content) 
    AGAINST('folk*' IN BOOLEAN MODE)
    UNION ALL
    SELECT title, content,
    MATCH(title, content) AGAINST('folk*' IN BOOLEAN MODE)
    as score FROM events WHERE MATCH(title, content) 
    AGAINST('folk*' IN BOOLEAN MODE)
    UNION ALL
    SELECT title, content,
    MATCH(title, content) AGAINST('folk*' IN BOOLEAN MODE)
    as score FROM blogs WHERE MATCH(title, content) 
    AGAINST('folk*' IN BOOLEAN MODE)
    UNION ALL
    SELECT title, content,
    MATCH(title, content) AGAINST('folk*' IN BOOLEAN MODE)
    as score FROM honeylands WHERE MATCH(title, content) 
    AGAINST('folk*' IN BOOLEAN MODE)
    UNION ALL
    SELECT title, content,
    MATCH(title, content) AGAINST('folk*' IN BOOLEAN MODE)
    as score FROM articles WHERE MATCH(title, content) 
    AGAINST('folk*' IN BOOLEAN MODE)
    UNION ALL
    SELECT title, content,
    MATCH(title, content) AGAINST('folk*' IN BOOLEAN MODE)
    as score FROM info WHERE MATCH(title, content) 
    AGAINST('folk*' IN BOOLEAN MODE)
    UNION ALL
    SELECT topicid as title, boardid as content,
    MATCH(title, content) AGAINST('folk*' IN BOOLEAN MODE)
    as score FROM articles WHERE MATCH(title, content) 
    AGAINST('folk*' IN BOOLEAN MODE)
    UNION ALL
    SELECT topicid as title, message as content,
    MATCH(title, content) AGAINST('folk*' IN BOOLEAN MODE)
    as score FROM info WHERE MATCH(title, content) 
    AGAINST('folk*' IN BOOLEAN MODE)) a ORDER BY score DESC

I should be able to create links for the tables that have commom field names such as events.php?id=1 getting the id from the record, but how would i do this for the tables topics and messages topic.php?boardid=1&topic=2 ?

Here is my table structure CREATE TABLE articles ( id int(4) NOT NULL auto_increment, title varchar(70) NOT NULL default '', content text NOT NULL, PRIMARY KEY (id) );

     CREATE TABLE `blogs` (
       `id` int(3) NOT NULL auto_increment,
       `title` varchar(100) NOT NULL default '',
       `content` text NOT NULL,
       PRIMARY KEY  (`id`)
     );

     CREATE TABLE `events` (
       `id` int(11) NOT NULL auto_increment,
       `title` varchar(100) NOT NULL default '',
       `content` text NOT NULL,
       PRIMARY KEY  (`id`)
     );

     CREATE TABLE `honeylands` (
       `id` int(4) NOT NULL auto_increment,
       `title` varchar(100) NOT NULL default '',
       `content` text NOT NULL,
       PRIMARY KEY  (`id`)
     );

     CREATE TABLE `info` (
       `id` int(1) NOT NULL auto_increment,
       `title` varchar(50) NOT NULL default '',
       `content` text NOT NULL,
       PRIMARY KEY  (`id`)
     );

     CREATE TABLE `messages` (
       `messageid` int(6) NOT NULL auto_increment,
       `boardid` int(2) NOT NULL default '0',
       `topicid` int(4) NOT NULL default '0',
       `message` text NOT NULL,
       `author` varchar(255) NOT NULL default '',
       `postdate` datetime default NULL,
       PRIMARY KEY  (`messageid`)
     );

     CREATE TABLE `news` (
       `id` int(4) NOT NULL auto_increment,
       `title` varchar(100) NOT NULL default '',
       `content` text NOT NULL,
       PRIMARY KEY  (`id`)
     );


     CREATE TABLE `topics` (
       `topicid` int(4) NOT NULL auto_increment,
       `boardid` int(2) NOT NULL default '0',
       `topicname` varchar(255) NOT NULL default '',
       `author` varchar(255) NOT NULL default '',
       `counter` int(5) NOT NULL default '0',
       `sticky` char(1) NOT NULL default 'n',
       `locked` char(1) NOT NULL default 'n',
       PRIMARY KEY  (`topicid`)
     );

This is how i currently get all the records but there is no way of adding extra fields for the topics and messages tables using a UNION

SELECT * FROM (SELECT title, content,
    MATCH(title, content) AGAINST('$keywords*' IN BOOLEAN MODE)
    as score FROM news WHERE MATCH(title, content) 
    AGAINST('$keywords*' IN BOOLEAN MODE)
    UNION ALL
    SELECT title, content,
    MATCH(title, content) AGAINST('$keywords*' IN BOOLEAN MODE)
    as score FROM events WHERE MATCH(title, content) 
    AGAINST('$keywords*' IN BOOLEAN MODE)
    UNION ALL
    SELECT title, content,
    MATCH(title, content) AGAINST('$keywords*' IN BOOLEAN MODE)
    as score FROM blogs WHERE MATCH(title, content) 
    AGAINST('$keywords*' IN BOOLEAN MODE)
    UNION ALL
    SELECT title, content,
    MATCH(title, content) AGAINST('$keywords*' IN BOOLEAN MODE)
    as score FROM honeylands WHERE MATCH(title, content) 
    AGAINST('$keywords*' IN BOOLEAN MODE)
    UNION ALL
    SELECT title, content,
    MATCH(title, content) AGAINST('$keywords*' IN BOOLEAN MODE)
    as score FROM articles WHERE MATCH(title, content) 
    AGAINST('$keywords*' IN BOOLEAN MODE)
    UNION ALL
    SELECT title, content,
    MATCH(title, content) AGAINST('$keywords*' IN BOOLEAN MODE)
    as score FROM info WHERE MATCH(title, content) 
    AGAINST('$keywords*' IN BOOLEAN MODE)
    UNION ALL
    SELECT topicname as title,message as content,
    MATCH(topicname, message) AGAINST('$keywords*' IN BOOLEAN MODE)
    as score FROM topics t INNER JOIN messages m ON t.topicid=m.topicid  
    WHERE MATCH(topicname, message) 
    AGAINST('$keywords*' IN BOOLEAN MODE)) a ORDER BY score DESC

Answer 1

I came across this issue building a search on a website with multiple types of content (movie database). I wanted the user to be able to do one search and find an actor, movie, or character name.

Instead of trying to get one big SQL statement, I did a match for each type of content (movie_title, movie_plot, actor_name, character_name, etc.) and stuck the id of the row, the type of content, and the score of the match into a multidimensional array. I would usually limit each content type to the top 50 matches.

I was then able to sort the array based on score. Then I would use the id and content type to look up the information I needed for each result.

EDIT (adding code)

Disclaimer: This is old code, and there is probably more efficient ways of doing it

$topResults = array();
$topResults[0] = array('nil', 'nil', 0);

$movieFound = 0;
$plotFound = 0;
$actorFound = 0;
$characterFound = 0;

// example of movie title... follow the same procedure for the others
$sql = "SELECT movies.Movie_ID as mid, MATCH (Movie_Title) AGAINST ('$searchstring') AS Score FROM movies, Rating_Movie_Relationships WHERE MATCH (Movie_Title) AGAINST ('$searchstring') AND Front_Image_File IS NOT NULL AND movies.Movie_ID = Rating_Movie_Relationships.Movie_ID $sqlwhere ORDER BY Score DESC LIMIT 0, 20";
$result = @mysql_query($sql);
while ($row = mysql_fetch_array($result)) {
    for ($i = 0; $i < count($topResults);$i++){
        if ($row['Score'] > $topResults[$i][2]){
            for ($j = count($topResults); $j > $i; $j--){
                $topResults[$j] = $topResults[$j-1];
            }
            $topResults[$i] = array($row['mid'], 'm', $row['Score'] - $movieWeight);
            break;
        }
    }
    $movieFound = 1;
}

//.... add the other content types here following the movie title example

for ($i = 0; $i < count($topResults); $i++){
    if ($topResults[$i][1] == 'm'){
        if ($countMovies < $limit) {
            $movieTitleDivText .= str_replace('\'','&#39;',createPersonMovieImageLink($topResults[$i][0]));
            $countMovies++;
        }
}