Mysql search across multiple tables

I have three tables in my database which are:

messages
topics
comments

Each of these tables has two fields called 'content' and 'title'. I want to be able to use 'Like' in my sql statement to look at 'messages.content', 'messages.title', 'topics.content', 'topics.title', 'comments.content' and 'comments.title' using a keyword.

So far, my query is able to find results from only one table:

mysql_query("SELECT * FROM messages 
WHERE content LIKE '%" . $keyword . "%' 
OR title LIKE '%" . $keyword ."%'");

I am also wondering, once I get the results from multiple tables, how can I tell what result is from what table?

Any help would be greatly appreciated!

OMG Ponies

318k78 gold badges511 silver badges494 bronze badges

asked Jul 4, 2011 at 17:42

Eyad FallatahEyad Fallatah

1,8294 gold badges22 silver badges33 bronze badges

2

$query = "(SELECT content, title, 'msg' as type FROM messages WHERE content LIKE '%" . 
           $keyword . "%' OR title LIKE '%" . $keyword ."%') 
           UNION
           (SELECT content, title, 'topic' as type FROM topics WHERE content LIKE '%" . 
           $keyword . "%' OR title LIKE '%" . $keyword ."%') 
           UNION
           (SELECT content, title, 'comment' as type FROM comments WHERE content LIKE '%" . 
           $keyword . "%' OR title LIKE '%" . $keyword ."%')";

mysql_query($query);

So, you are getting result from all of the three tables, and you can identify which row came from which table by looking at its type value.

answered Jul 4, 2011 at 17:48

MD Sayem AhmedMD Sayem Ahmed

28.2k26 gold badges109 silver badges177 bronze badges

4

What you are probably looking for is the UNION command:

SELECT id, 'messages' as 'table' FROM messages 
  WHERE content LIKE '%keyword%' 
    OR title LIKE '%keyword%'
UNION
SELECT id, 'topics' as 'table' FROM topics
  WHERE content LIKE '%keyword%' 
    OR title LIKE '%keyword%'
UNION
SELECT id, 'comments' as 'table' FROM comments
  WHERE content LIKE '%keyword%' 
    OR title LIKE '%keyword%'

answered Jul 4, 2011 at 17:50

Two search in other tables you use:

SELECT `categories`.`title`, `posts`.`title` WHERE `categories`.`title` LIKE {$a} OR `posts`.`title` LIKE {$a}

The CATEGORIES and POSTS are tables of your database.

answered Jul 4, 2011 at 17:45

Bruno AlanoBruno Alano

6431 gold badge11 silver badges21 bronze badges

2

Html Search form:

Search.php:

link, $fm->validation($_GET['search']));
    }
    else{
        header("Location:404.php");
    }
?>
select($query);
    if ($post) {
        while ($result = $post->fetch_assoc()) {
            echo"Database data like, $result['title']";
        }
    }
    else{
        echo "result Not found";
    }

include database.php in search.php

class Database{
    public function select($query){
        $result = $this->link->query($query) or die($this->link->error.__LINE__);
        if($result->num_rows > 0){
            return $result;
        }
        else {
            return false;
        }
    }
}
$db = new Database();

Donald Duck

7,84120 gold badges70 silver badges91 bronze badges

answered Mar 5, 2017 at 15:03

How do I search for data in two tables in SQL?

How retrieve data from 5 tables in SQL?

How do I search an entire database in MySQL?

How do you SELECT two tables in a single query?