Codeigniter关联查询无法获取arac_model表数据求助

问题分析 #

原代码存在以下关键问题导致无法拉取arac_model表的model数据：

• 未编写查询arac_model表的逻辑，直接使用未定义的$arac_model_data变量
• 生成URL时错误使用了$feed（结果集数组）而非循环中的$liste（单条feed数据）
• 获取部门经理邮箱时未判断查询结果是否存在，可能引发报错

修复方案 #

1. 关联查询feed与arac_model表（推荐，提升效率） #

在初始查询feed数据时，直接通过JOIN关联arac_model表，一次性获取所需的model名称：

// 替换原feed查询代码
$this->db->select('feed.*, arac_model.model as arac_model_name');
$this->db->from('feed');
$this->db->join('arac_model', 'feed.model = arac_model.model_id', 'left');
$this->db->where('feed.islenme', 0);
$feed = $this->db->get()->result();

2. 修正URL生成的错误 #

将$feed->sube和$feed->model改为循环中的$liste->sube和$liste->model：

// 替换原URL生成代码
$url = md5($liste->musteri_tel . $key1 . $liste->sube . $liste->model . date("Y-m-d H:i:s"));

3. 替换邮件中的model数据 #

使用关联查询得到的$liste->arac_model_name替换原有的$liste->model，同时处理可能的空值：

// 替换邮件from和内容中的model相关代码
$arac_model_name = !empty($liste->arac_model_name) ? $liste->arac_model_name : $liste->model;
$this->email->from('info@arkasotomotiv.com.tr', 'Arkas Otomotiv ' . $arac_model_name . ' CRM');

// 邮件内容部分
$text='<h3>Arkas Otomotiv ' . $arac_model_name . ' Leads </h3><br>';

4. 修复部门经理邮箱查询的潜在报错 #

先判断查询结果是否存在，再获取邮箱：

// 替换原mudur查询代码
$mudur_row = $this->db->where("personel_isyeri", $pe->personel_isyeri)
                      ->where("personel_departman", 2)
                      ->get("personel")->row();
$mudur = $mudur_row ? $mudur_row->eposta : '';

完整修正后的控制器代码 #

class Atama extends CI_Controller
{
    public function index ()
    {
        // 关联查询feed与arac_model表
        $this->db->select('feed.*, arac_model.model as arac_model_name');
        $this->db->from('feed');
        $this->db->join('arac_model', 'feed.model = arac_model.model_id', 'left');
        $this->db->where('feed.islenme', 0);
        $feed = $this->db->get()->result();
        
        $personeladet=array();

        foreach ($feed as $liste) {
            $personeladet=array();

            $personel=$this->db->where("personel_isyeri",$liste->sube)
                              ->where("durum",1)
                              ->where("personel_departman",1)
                              ->get("personel")->result();

            foreach($personel as $adet){
                $kayitadet=$this->db->query("select * from is_emri where month(isemri_giris)=month(now()) and year(isemri_giris)=year(now()) and isemri_kullanici='".$adet->personel_id."'")->num_rows();
                $personeladet[$adet->personel_id]=$kayitadet;
            }
            asort($personeladet);

            $key1=array_key_first($personeladet);

            // 修正URL生成的变量错误
            $url=md5($liste->musteri_tel.$key1.$liste->sube.$liste->model.date("Y-m-d H:i:s"));

            $array=array(
                'isemri_kullanici'=>$key1,
                'isemri_giris'=>date("Y-m-d H:i:s"),
                'durum'=>0,
                'isemri_aciklama'=>'',
                'isemri_arac'=>'0',
                'isemri_cikis'=>date("Y-m-d H:i:s"),
                'isemir_tamamlama'=>date("Y-m-d H:i:s"),
                'tamamlama_aciklama'=>'',
                'musteri_tel'=>$liste->musteri_tel,
                'musteri_ad'=>$liste->musteri_ad,
                'musteri_posta'=>$liste->musteri_eposta,
                'marka'=>$liste->marka,
                'model'=>$liste->model,
                'arac_isyeri'=>$liste->sube,
                'kanal'=>'1',
                'fiyat'=>0,
                'url'=>$url
            );
            
            $this->db->insert("is_emri",$array);
            
            $this->load->library("email");
            
            // 处理arac_model名称，为空则使用原model_id
            $arac_model_name = !empty($liste->arac_model_name) ? $liste->arac_model_name : $liste->model;
            $this->email->from('info@arkasotomotiv.com.tr', 'Arkas Otomotiv '.$arac_model_name.' CRM');      

            $pe=$this->db->where("personel_id",$key1)->get("personel")->row();
            
            // 修复部门经理查询的潜在报错
            $mudur_row = $this->db->where("personel_isyeri", $pe->personel_isyeri)
                                  ->where("personel_departman", 2)
                                  ->get("personel")->row();
            $mudur = $mudur_row ? $mudur_row->eposta : '';
            
            $this->email->set_header('Importance', 'High');
            $this->email->set_header('X-MSMail-Priority', 'High');
            $this->email->set_header('X-MSMail-Priority', '1 (Highest)');
            
            $this->email->to($pe->eposta);
            // 仅当mudur邮箱存在时添加CC
            $cc_list = ["fatih.aydin@arkasotomotiv.com.tr"];
            if($mudur){
                $cc_list[] = $mudur;
            }
            $this->email->cc($cc_list);

            $this->email->subject('Crm - Arkasotomotiv ');

            $text='<h3>Arkas Otomotiv '.$arac_model_name.' Leads </h3><br>';
            $text.="Oluşturma: ".date('Y-m-d H:i:s')."<br>";
            $text.="Müşteri Adı:".$liste->musteri_ad."<br>";
            $text.="Müşteri Telefon:".$liste->musteri_tel."<br>";
            $text.="Müşteri adayı ile görüştükten sonra sonucunu işlemek için linki tıklayın.<br>";
            
            $text.="<a href='https://crm.egeinternet.com/leads/goster/?key=".$url."'>Talebi işle</a><br>";
            
            $this->email->message($text);
            
            $this->email->send();
            print_r($this->email->print_debugger());
            
            $guncelle=array("islenme"=>1);
            $this->db->where('feed_id',$liste->feed_id);
            $this->db->update("feed",$guncelle);
        }
    }
}

内容的提问来源于stack exchange，提问作者Fatih