优化将参数从控制器传递到 MySQL 查询模型的方式 - optimize way of passing paramters from controller to model for mysql query

我正在condeigniter（新手）中做一个项目。我的项目包括使用不同的过滤器（如类别，子类别，关键字，最低-最高价格等）列出产品。我已经在自定义 php 中这样做了一百次，我为子句中的所有条件where变量$where。例如

$where = '';
if($category != '')
{
  $where .= " category = '$category'";
}

现在在编码器点火器中，我有多个参数，我可以通过uri segment进入我的controller并调用model函数，传递所有参数并在那里run mysql query。问题是我正在检查每个参数是否为空或已设置并相应地运行多个查询。例如。

if($category != '' && $subCategory != '')
{
  //whole query here
}
else  if($category == '' && $subCategory != '')
{
  //whole query here
}
//and so on

我需要的是任何优化的方式来做到这一点，因为我有连接（多个参数）。

我的示例条件和查询

if($subCat == '' && $vendor == '')
    {
        $cat = str_replace("-"," ",str_replace("-and-"," & ",$cat));
        $this->db->select('listings.* ');
        $this->db->from('listings');
        $this->db->join('categories','categories.id = listings.category');
        $this->db->where('categories.name',$cat);
        $this->db->limit($perpage,$page);
        $query = $this->db->get();
        return $query->result();
    }
    else if($subCat == '' && $vendor != '')
    {
        $cat = str_replace("-"," ",str_replace("-and-"," & ",$cat));
        $this->db->select('listings.* ');
        $this->db->from('listings');
        $this->db->join('categories','categories.id = listings.category');
        $this->db->join('vendors','vendors.id = listings.programme');
        $this->db->where('categories.name',$cat);
        $this->db->where('vendors.name',$vendor);
        $this->db->limit($perpage,$page);
        $query = $this->db->get();
        return $query->result();
    }
    else if($subCat != '' && $vendor == '')
    {
        $cat = str_replace("-"," ",str_replace("-and-"," & ",$cat));
        $this->db->select('listings.* ');
        $this->db->from('listings');
        $this->db->join('categories','categories.id = listings.category');
        $this->db->join('subcategories','subcategories.id = listings.subcategory');
        $this->db->where('categories.name',$cat);
        $this->db->where('subcategories.name',$subCat);
        $this->db->limit($perpage,$page);
        $query = $this->db->get();
        return $query->result();
    }
    else
    {
        $cat = str_replace("-"," ",str_replace("-and-"," & ",$cat));
        $subCat = str_replace("-"," ",str_replace("-and-"," & ",$subCat));
        $vendor = str_replace("-"," ",str_replace("-and-"," & ",$vendor));
        $this->db->select('listings.* ');
        $this->db->from('listings');
        $this->db->join('categories','categories.id = listings.category');
        $this->db->join('subcategories','subcategories.id = listings.subcategory');
        $this->db->join('vendors','vendors.id = listings.programme');
        $this->db->where('categories.name',$cat);
        $this->db->where('subcategories.name',$subCat);
        $this->db->where('vendors.name',$vendor);
        $this->db->limit($perpage,$page);
        $query = $this->db->get();  
        return $query->result();
    }

您只需要编写一次查询，而不是每次都编写查询。例如，您可能有几种条件，如您所描述的。例如，这是我所做的：

$this->db->select('resource.id,city.name as city_name,city.provinceID,resource.Rate, skills.name')->from('resource')
->join('city','resource.cityID = city.id')
->join('resourceToSkillsMap','resource.id = resourceToSkillsMap.resourceID')
->join('skills','skills.id =  resourceToSkillsMap.skillsID');
if($category != '' && $subCategory != '') #condition
{
    $this->db->where('condition');
}
else  if($category == '' && $subCategory != '') #condition
{
    $this->db->where('condition');
}
$rs = $this->db->group_by('resource.id')
->limit($limit,$offset)
->get();

查看查询是如何编写的only once但where condition是在if else内给出的。因此，您的查询将以这种方式建立。执行查询后，只需echo $this->db->last_query();回显查询并根据需要进行调整。

在你的助手中，你可以像这样构建你的$where array()

$where = array();
if($category != '')
{
   $where['category'] = $category;
} else {
   $where['someotherkey'] = 'someothervalue';
}

在

控制器中，您只需在调用模型时传递$where数组。

$this->foo_model->foo_method($where);

在模型中，您可以做这样的事情

$this->db->where($where);

编辑

if($category != '')
{
   //Along with your $this->db->where()
   $this->db->join('table1','condition1');
} else {
   //Along with your $this->db->where()
   $this->db->join('table2','condition2');
}

整理东西

$cat = str_replace("-"," ",str_replace("-and-"," & ",$cat));
$this->db->select('listings.* ');
$this->db->from('listings');
$this->db->join('categories','categories.id = listings.category');  
$this->db->where('categories.name',$cat);
if($vendor != '')      {
  $this->db->join('vendors','vendors.id = listings.programme');
  $this->db->where('vendors.name',$vendor);
}
if($subCat != ''){
  $subCat = str_replace("-"," ",str_replace("-and-"," & ",$subCat));
  $this->db->join('subcategories','subcategories.id = listings.subcategory');
  $this->db->where('subcategories.name',$subCat);
}    
$this->db->limit($perpage,$page);
$query = $this->db->get();
return $query->result();