PHP, MySQL search by keyword from multiple tables

Question

I have code like this:

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

        $sql = " ( SELECT name AS type 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 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 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();
    }

The function is working. What I don't know is how to know which data is from which table, and then to show the results on the page?

https://stackoverflow.com/questions/6574564/php-mysql-search-multiple-tables-using-a-keyword — Dharman, Jul 22 '20 at 20:11

score 1 · Accepted Answer · answered Mar 29 '12 at 10:11

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!

score 0 · Answer 2 · answered Mar 29 '12 at 10:17

In select statement insert anew column that identifies the table: SELECT name AS type, 'tab_x' as from_table FROM global_info WHERE .... SELECT name AS type, 'tab_x1' as from_table FROM global_info WHERE

when you use the returned values check the "from_table" field to see the orginal table the result come from

PHP, MySQL search by keyword from multiple tables

2 Answers2