您可以做的事情
public function view_customers()
{
$sql = "SELECT * FROM customer c LEFT JOIN customer_order co ON c.customer_number = co.customer_number AND co.order_status IN ('preparing', 'prepared') WHERE c.customer_status='unpaid' AND c.order_status = 'unserve' AND co.cus_ord_no IS null";
return $this->db->query($sql)->result();
}
您可以使用row()
方法将结果输出为一个object
,或者使用row_array()
方法将结果输出为一个array
。如果需要多个objects
,则可以使用result()
方法;如果需要多个arrays
,则可以使用result_array()
方法。
通常我的做法是这样的:
控制器:
public function view()
{
$this->load->model('My_Model');
$data = new stdclass;
$data->user_lists = $this->my_model->view_users(array('nationality'=>'AMERICAN'));
}
型号:
public function view_users($param = null) //no value passed
{
$condition = '1';
if (!empty($param)) { //Having this will trap if you input an array or not
foreach ($param as $key=>$val) {
$condition .= " AND {$key}='{$val}'"; //Use double quote so the data $key and $val will be read.
}
}
$sql = "SELECT * FROM users WHERE {$condition}"; //Use double quote so the data $condition will be read.
// Final out is this "SELECT * FROM users WHERE 1 AND nationality='AMERICAN'";
return $this->db->query($sql)->result();
}
如果查询语句没有需要根据条件改变的子句,则使用$this->db-query()
是正确的方法。
$sql = "SELECT * FROM customer c LEFT JOIN customer_order co
ON c.customer_number=co.customer_number AND co.order_status IN ('preparing', 'prepared')
WHERE c.customer_status='unpaid' AND c.order_status='unserve' AND co.cus_ord_no IS null";
$query = $this->db->query($sql)->result();
echo json_encode($query);
在调用query()
后最好加上一项检查,因为如果它失败(返回false),那么对result()
的调用将会抛出异常。可以通过以下方式进行处理。
$query = $this->db->query($sql);
if($query !== FALSE)
{
echo json_encode($query->result());
return;
}
echo json_encode([]); // respond with an empty array
查询生成器(QB)是一个不错的工具,但通常过于复杂。它增加了很多开销来创建一个字符串,该字符串直接传递给 $db->query()
。如果您知道字符串并且没有必要重构它,那么您不需要使用 QB。
当您想有条件地更改查询语句时,QB非常有用。排序可能是其中一种可能情况。
if($order === 'desc'){
$this->db->order_by('somefield','DESC');
} else {
$this->db->order_by('somefield','ASC');
}
$results = $this->db
->where('other_field', "Foo")
->get('some_table')
->result();
因此,如果$order
的值为'desc'
,则查询语句将会是:
SELECT * FROM some_table WHERE other_field = 'Foo' ORDER BY somefield 'DESC'
$query = $this->db
->join('customer_order co', "c.customer_number = co.customer_number AND co.order_status IN ('preparing', 'prepared')", 'left')
->where('c.customer_status','unpaid')
->where('c.order_status','unserve')
->where('co.cus_ord_no IS NULL')
->get('customer c');
//another variation on how to check that the query worked
$result = $query ? $query->result() : [];
echo json_encode($result);