php - MySQL 联合丢失表名

Question

我有一个 MySQL 查询，我试图同时搜索 2 个表。这是一个自动完成搜索框，用于搜索常规客户和商业客户。这是代码:

$query = mysql_query("SELECT * FROM clients WHERE lastname LIKE '$q%' AND agentid = '$agentid'
                          UNION
                          SELECT * FROM busclients WHERE busname LIKE '$q%' AND agentid = '$agentid'")or die(mysql_error());
            if($query) {
                while ($result = mysql_fetch_array($query)) {
                    $busname = $result['busname'];
                    print_r($result);

                    if(isset($busname)){

                        $description['id'] = 'viewbusiness.php?id=' . $result['ID'];
                    $description['value'] = $busname ;
                    array_push($return_arr,$description);
                    }
                    else
                    {

                    $description['id'] = 'viewclient.php?id=' .$result['ID'];
                    $description['value'] = $result['lastname'] . ", " . $result['firstname'] ;
                    array_push($return_arr,$description); 
                    }

                    }
                }

问题是业务客户端从常规客户端获得表名，因此代码从不使用 if(isset($busname))，因为busname 变成了姓氏，并将您定向到 veiwclient 页面。

Answer 1

SELECT 
  a.lastname , 
  b.busname 
FROM clients as a 
INNER JOIN  
  busclients as b 
  on b.agent_id = a.agent_id 
WHERE a.agent_id = $agent_id 
      AND (a.lastname LIKE '$q%' OR b.busname LIKE '$q%')