PHP mysql search multiple tables using a keyword

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!

Answers


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


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

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.


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

    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();
    $query = "SELECT * FROM post WHERE title LIKE '%$search%' OR body LIKE '%$search%'";
    $post = $db->select($query);
    if ($post) {
    while ($result = $post->fetch_assoc()) {
 echo"Database Table colum" 
} else{
 echo "Search result not Match";
}
}

Need Your Help

Reading a UTF8 CSV file with Python

python utf-8 csv character-encoding

I am trying to read a CSV file with accented characters with Python (only French and/or Spanish characters). Based on the Python 2.5 documentation for the csvreader (http://docs.python.org/library/...

XCode 4.4 .strings file add new localization option gone

objective-c ios xcode localization

Upgraded to Xcode 4.4 today, and when clicking on my strings file and looking in the Utilities window, I no longer see a plus button allowing me to add a new language.