php - codeigniter查询始终返回null

我有两张桌子：

+-------------+--------------+
| 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 |
+----+-------------+----------+----------+

我想为每个订单id选择产品名称和数量。我是在普通sql中完成的，但是当1试图在Codeigniter中创建select子句时，它返回null。
我怎么知道它是空的？我有一个方法来验证结果是否为空。如果是，控制器将显示404。
注意：控制器的$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;

控制器：

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');
}

最佳答案

我将使用连接子句：

$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文档中的joinshere