我正在condeigniter(newbie)中从事一个项目。我的项目包括使用类别,子类别,关键字,最低-最高价格等不同过滤器列出产品。我已经在自定义php中完成了这100次,在此我$where
为where
子句中的所有条件制作了变量。例如
$where = '';
if($category != '')
{
$where .= " category = '$category'";
}
现在在codeigniter中,我有多个参数,我可以controller
通过它们uri segment
并调用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();
回显查询并根据需要对其进行调整。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句