PHP MySQL Search Multiple Tables Using a Keyword

PHP mysql search multiple tables using a keyword

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

Php Sql Search multiple tables with keyword and display search results

For anyone who wants to know how the resulting query ended up:

$query = "(SELECT 'product' AS type, 'product' AS link, product_ID AS ID, product_name AS name, product_description AS description, product_image AS image FROM tbl_products WHERE product_name LIKE '%{$searchTerms}%' OR product_description LIKE '%{$searchTerms}%')
   UNION
   (SELECT 'blog' AS type, 'blog' AS link, blog_ID AS ID, blog_title AS name, blog_content AS description, blog_image AS image FROM tbl_blog WHERE blog_title LIKE '%{$searchTerms}%' OR blog_content LIKE '%{$searchTerms}%') 
   UNION
   (SELECT 'service' AS type, 'serviceCat' AS link, serviceCat_ID AS ID, serviceCat_name AS name, serviceCat_description AS description, serviceCat_image AS image FROM service_categories WHERE serviceCat_name LIKE '%{$searchTerms}%' OR serviceCat_description LIKE '%{$searchTerms}%') ORDER BY name ASC";     
   $result = mysqli_query($link, $query);

   if (mysqli_num_rows($result) < 1) {
     $errors[] = "Your search term yielded no results!<hr>";
   }
   else {
     //loop and store through all the results 
     while ($row=mysqli_fetch_array($result)) {
       extract($row);
       $results[] = '<a class="searchPageBox" href="view'.$type.'.php?'.$link.'ID='.$ID.'"><div style="width: 100%;"><div class="row"><div class="col-md-4">'.'<img style="width:100%; height=100%;" src="product-images/'.$image.'"  /></div><div class="col-md-8"><h3 class="searchPageTitle">'.$name.'</h3><div class="searchPageDesc">'.$description.'</div></div></div></div></a><hr>';
} 

     // end of while

   } // end of else

} // end of if

Searching multiple tables and columns with a keyword in php

Did you check PHP error_logs? I guess reason of the problem is line 14. You're using mysqli_* methods but on line 14, you had tried to use a mysql_* method. I think you should remove the code on this line and try again.

PHP, MySQL search by keyword from multiple tables

Perhaps, adding a field to the results of each SELECT.

function search_keyword(){
        $keyword = trim($_POST['keyword']);
        $search_explode = explode(" ", $keyword);
        $x = 0;

        $sql = " ( SELECT name AS type, \"table_global_info\" as mytable FROM global_info WHERE ";
        foreach($search_explode as $each){
            $x++;
            if($x == 1){
               $sql .= " name LIKE '%$each%'";}                          
            else {

                $sql .= " name LIKE '%$each%' ";
            }
        }

          $sql .= " ) UNION ALL "; 

          $sql .= " ( SELECT name AS type, \"person\" as mytable FROM person WHERE ";
        foreach($search_explode as $each){
            $x++;
            if($x == 1){
               $sql .= " name LIKE '%$each%'";}                          
            else {

                $sql .= " name LIKE '%$each%' ";
            }
        }

          $sql .= " ) UNION ALL ";

          $sql .= "( SELECT name AS type, \"event\" as mytable FROM event WHERE ";
        foreach($search_explode as $each){
            $x++;
            if($x == 1){
               $sql .= " name LIKE '%$each%'";}                          
            else {

                $sql .= " name LIKE '%$each%' ";
            }
        }

        $sql .= " ) ";

        $q = $this->db->query($sql);
         return $q = $q->num_rows() == 0 ? FALSE :  $q->result();
    }

Regards!

How to search for keyword in three different tables

The SQL query you're possible looking for is:

SELECT * FROM (
SELECT song_title AS title, song_author AS author, 'song' as type FROM songs
UNION
SELECT video_title AS title, video_author AS author, 'video' as type FROM videos
UNION
SELECT post_title AS title, NULL AS author, 'post' as type FROM posts) a
WHERE title LIKE '%'.$keyword.'%' OR author LIKE '%'.$keyword.'%'

Also, please consider two more important notes:

1. It is VERY unsafe to use something like '%'.$keyword.'%' in queries. Details here.
2. You might want to retrieve some kind of ids from your tables, to use further.

---

Related Topics