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!

Winfrid

Is there a reason why you're not just doing 3 different queries? The columns probably aren't the same across all of them. – Supposition

Full Text Search (FTS) is the recommended approach - MySQL has native syntax, but it's for MyISAM only. Otherwise, you have to look to 3rd party support, like Sphinx. – Lark

Answer 1

$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.

Alisealisen

Definitely the right answer. Thanks! the type approach was exactly what I needed. – Winfrid

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! – Bimbo

@FrankW.: Hi Frank, sorry that I did not notice your comment earlier. I hope you solved your problem? – Alisealisen

dear sir, how to achieve if has different column name one table to other ? – Decoration

Answer 2

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

Supposition

Answer 3

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.

Belton

Not quite what I was looking for. Thank you for the answer though! – Winfrid

it'll return duplicated values! – Kavita

Answer 4

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();

Careen