php - Mysql 查找两张表

Question

我有一个表名 tbl_marketing 包含很多列

db_id,db_customer,db_brand,db_client....

另一个表名tbl_phonecall包含许多列并与 tbl_marketing 链接在 db_mid这包含 tbl_marketing 的 id

db_id,db_subject,db_due,db_nextdate,db_mid

知道我创建了一个搜索表单，这个搜索表单应该在 tbl_marketing 和 db_due 以及 tbl_phonecall 中的 db_nextdate 的所有字段上进行搜索，当用户进行搜索时，结果应该是这样的

客户品牌客户到期日客户品牌和客户来自tbl_marketing其余来自 tbl_phonecall

对于该搜索，我使用这个 php代码

$q = array();  
    $sql = "";
     if(isset($_POST['txt_name']) && !empty($_POST['txt_name'])){  
    $name = mysqli_real_escape_string($conn,$_POST['txt_name']); 
    $q[] = "tbl_marketing.db_customer='".$name."' ";      
    }
    if(isset($_POST['txt_client']) && !empty($_POST['txt_client'])){  
    $client = mysqli_real_escape_string($conn,$_POST['txt_client']); 
    $q[] = "tbl_marketing.db_client='".$client."' ";      
    }
    if(isset($_POST['txt_brand']) && !empty($_POST['txt_brand'])){  
    $brand = mysqli_real_escape_string($conn,$_POST['txt_brand']); 
    $q[] = "tbl_marketing.db_brand='".$brand."' ";      
    }
    if(isset($_POST['txt_dateofcalling']) && !empty($_POST['txt_dateofcalling'])){  
    $dateofcalling= mysqli_real_escape_string($conn,$_POST['txt_dateofcalling']);
    $searchdateofcalling=date("Y-m-d H:i:s", strtotime($dateofcalling));    
    $q[] = "DATE(tbl_phonecall.db_due)='".$searchdateofcalling."' ";      
    }
     if(isset($_POST['txt_nextdate']) && !empty($_POST['txt_nextdate'])){  
    $nextdate= mysqli_real_escape_string($conn,$_POST['txt_nextdate']);
    $searchnextdate=date("Y-m-d H:i:s", strtotime($nextdate));       
    $q[] = "DATE(tbl_phonecall.db_nextdate)='".$searchnextdate."' ";      
    }
    $first = true; 
    foreach($q as $qu){  
        if($first){  
        $sql .= " where ".$qu;      
        $first = false;  
        }else{  
        $sql .= " and ".$qu;          
        } 
    } 
$query=mysqli_query($conn,"select tbl_marketing.*,tbl_phonecall.* from tbl_marketing,tbl_phonecall {$sql}")or die(mysqli_error($conn));

但是这个查询重复了这个值

我怎样才能解决这个问题并得到我之前发布的结果

Answer 1

看起来你错过了加入条件

where tbl_marketing.db_id = tbl_phonecall.db_mid

如果即使在 tbl_phonecall 表中不存在匹配记录，您也想从 tbl_marketing 返回记录，请在查询中使用左连接

select tbl_marketing.*,tbl_phonecall.* 
from tbl_marketing left join tbl_phonecall 
on tbl_marketing.id = tbl_phonecall.mid 
where ....<<where conditions here>>