PHP mysql search multiple tables using a keyword

Question

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!

Is there a reason why you're not just doing 3 different queries? The columns probably aren't the same across all of them. — evan, Jul 04 '11 at 17:48
Full Text Search (FTS) is the recommended approach - [MySQL has native syntax, but it's for MyISAM only](http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html). Otherwise, you have to look to 3rd party support, like Sphinx. — OMG Ponies, Jul 04 '11 at 17:55

score 104 · Accepted Answer · answered Jul 04 '11 at 17:48

104

$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 04 '11 at 17:48

MD Sayem Ahmed

28,628
27
111
178

Definitely the right answer. Thanks! the type approach was exactly what I needed. – Eyad Fallatah Jul 04 '11 at 18:04
Used this query works perfect, only thing is why can't i add another field like "SELECT content, title, username"? when i do that i get a server error 500. You have any idea? Thanks in advance! – Frank W. Jan 13 '16 at 14:42
@FrankW.: Hi Frank, sorry that I did not notice your comment earlier. I hope you solved your problem? – MD Sayem Ahmed Apr 22 '16 at 12:18
dear sir, how to achieve if has different column name one table to other ? – Pisumathu Feb 08 '17 at 12:50

evan · Answer 2 · 2020-07-23T17:24:40.593

8

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%'

edited Jul 23 '20 at 17:24

answered Jul 04 '11 at 17:50

evan

12,307
7
37
51

score 0 · Answer 3 · answered Jul 04 '11 at 17:45

0

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 04 '11 at 17:45

Bruno Alano

643
1
11
21

2

Not quite what I was looking for. Thank you for the answer though! – Eyad Fallatah Jul 04 '11 at 18:05
it'll return duplicated values! – Amir Fo Nov 12 '17 at 11:22

score -1 · Answer 4 · edited Mar 05 '17 at 15:32

Html Search form:

<div class="header_top_right">
    <form action="search.php" method="GET" class="search_form">
        <input type="text" placeholder="Text to Search.." name="search">
        <input type="submit" class="btn btn-default" value="">
    </form>
</div>

Search.php:

<?php
    if (isset($_GET['search']) || !empty($_GET['search'])) {
        $search = mysqli_real_escape_string($db->link, $fm->validation($_GET['search']));
    }
    else{
        header("Location:404.php");
    }
?>
<?php
    $query = "SELECT * FROM news_post WHERE title LIKE '%$search%' OR body LIKE '%$search%' OR tags LIKE '%search%'";
    $post = $db->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();

PHP mysql search multiple tables using a keyword

4 Answers4

Linked

Related