php - codeigniter 查询总是返回 null

Question

我有这两张表:

+-------------+--------------+
| products_id | product_name |
+-------------+--------------+
|           1 | Pizza x      |
|           2 | Pizza Y      |
|           3 | Pizza A      |
|           4 | Pizza Z      |
|           5 | Pizza W      |
+-------------+--------------+

+----+-------------+----------+----------+
| id | products_id | order_id | quantity |
+----+-------------+----------+----------+
|  1 |           1 |        5 |        3 |
|  1 |           2 |        5 |        4 |
|  2 |           3 |        6 |        3 |
|  2 |           4 |        6 |        3 |
|  3 |           5 |        7 |        2 |
+----+-------------+----------+----------+

我想为每个 order_id 选择 products_name 和quantity。我在普通的 sql 中完成了它，但是当我试图在 Codeigniter 中创建 select 子句时，它返回 null。

我怎么知道它是空的？我有一种方法可以验证结果是否为空。如果是， Controller 将显示 404。

注意:来自 Controller 的 $id 来自 url。

Codeigniter 查询:

 public function get_products_from_orders($id){
    $this->db->select('products.product_name');
    $this->db->select('products_to_orders.quantity');
    $this->db->from('products');
    $this->db->from('products_to_orders');
    $this->db->where('products.products_id','products_to_orders.product_id');
    $this->db->where('products_to_orders.order_id',$id);

    $query = $this->db->get();
    return $query->result_array();
}

普通的sql:

$data = $this->db->query("select products.product_name, products_to_orders.quantity
                          from products, products_to_orders
                          where products.products_id = products_to_orders.product_id 
                          and products_to_orders.order_id ='" . $id."'");
return $data;

Controller :

public function view($id = NULL){

    $data['products'] = $this->order_model->get_products_from_orders($id);

    if(empty($data['products'])){
      show_404();
    }
    $this->load->view('templates/header');
    $this->load->view('orders/view',$data);
    $this->load->view('templates/footer');
}

Answer 1

我会使用连接子句:

$this->db->select('products.product_name, products_to_orders.quantity');
$this->db->from('products');
$this->db->join('products_to_orders', 'products.products_id=products_to_orders.product_id');

$this->db->where('products_to_orders.order_id',$id);

$query = $this->db->get();
return $query->result_array();

请参阅有关连接的 CI 文档 here

请参阅有关连接的 MySQL 文档 here