mySQL: How to search in multiple tables in database?

Question

I want to search in two tables. Tried this but didn't worked. I'm getting error "Unkown Column...."

HTML:

<form method="post"
 <input type="text" name="search_keyword">
 <input type="submit" name="button_search"> 
</form>

MYSQL SELECT AND PHP:

    if(isset($_POST['button_search']))
    {
      $search_keyword = $_POST['search_keyword'];

      $select = mysql_query("SELECT * from table_1 WHERE match(name_1) against ($search_keyword) 
        union all SELECT * from table_2 WHERE match(name_2) against ($search_keyword)");
      while($row = mysql_fetch_array($select))
      {
        echo $row['question'];
        echo $row['category_name'];
      }
   }

TABLES:

For example:

Table 1:

table name: table_1

Values (table 1):

+-------+-------------+
| id_1  |   name_1    |
+-------+-------------+ 
|  1     | Phinoy     | 
|  2     | Go         | 
+-------+-------------+

Table 2:

table name: table_2

Values:

+-------+-------------+
| id_2  |   name_2    |
+-------+-------------+ 
|  1     |   Gi       | 
|  2     | Phinas     | 
+-------+-------------+

If I search "P" or "Ph" , it will show the values "Phinoy and Phinas".

you want to display "Phinoy and Phinas" as search result OR auto suggestion while type in the box — J.K, Apr 01 '16 at 03:13
possible duplicate [here](http://stackoverflow.com/questions/394041/mysql-how-to-search-multiple-tables-for-a-string-existing-in-any-column) which is answered below — roullie, Apr 01 '16 at 03:20

Fernando Torres · Answer 1 · 2016-04-04T16:09:42.537

Well. First you need is use an INNER JOIN for comparing two tables at the same time. In this code, we are concatening the result of the table 1 and the table 2, then we are calling the table_1 and with a INNER JOIN we will compare the table_2 if in the table_1 there are any result LIKE table_2 and only just happening in the column name_1 and name_2.

<?php
    $query = "SELECT 
    CONCAT(table_1.name_1,' and ',table_2.name_2) 
    FROM table_1 INNER JOIN table_2 
    ON table_1.name_1 LIKE table_2.name_2";
    mysql_query($query);
?>

mySQL: How to search in multiple tables in database?

1 Answers1