Choose, count and where to use codeigniter and mysql

Question

I have two tables:

- tblSaler SalerID | SalerName | ----------------------| 1 | sothorn | ----------------------| 2 | Daly | ----------------------| 3 | Lyhong | ----------------------| 4 | Chantra | ----------------------| - tblProduct ProductID | Product | SalerID | --------------------------------| 1 | Pen | 3 | --------------------------------| 2 | Book | 2 | --------------------------------| 3 | Phone | 3 | --------------------------------| 4 | Computer | 1 | --------------------------------| 5 | Bag | 3 | --------------------------------| 6 | Watch | 2 | --------------------------------| 7 | Glasses | 4 | --------------------------------| 

The result I need is:

 sothorn | 1 Daly | 2 Lyhong | 3 Chantra | 1 

I tried this:

  $this->db->select('count(SalerName) as sothorn where tblSaler.SalerID = 1, count(SalerName) as Daly where tblSaler.SalerID = 2, count(SalerName) as Lyhong where tblSaler.SalerID = 3, count(SalerName) as Chantra where tblSaler.SalerID = 4'); $this->db->from('tblSaler'); $this->db->join('tblProduct', 'tblSaler.SalerID = tblProduct.SalerID'); 

Answer 1

You can use this query for this

 SELECT tblSaler.SalerName, count(tblProduct.ProductID) as Total FROM tblSaler LEFT JOIN tblProduct ON tblProduct.SalerID = tblSaler.SalerID GROUP BY tblSaler.SalerID 

And here is the active record for this

 $select = array( 'tblSaler.SalerName', 'count(tblProduct.ProductID) as Total' ); $this->db ->select($select) ->from('tblSaler') ->join('tblProduct','roduct.SalerID = tblSaler.SalerID','left') ->group_by('tblSaler.SalerID') ->get() ->result_array(); 

Demo

OUTPUT

 | SALERNAME | TOTAL | |-----------|-------| | sothorn | 1 | | Daly | 2 | | Lyhong | 3 | | Chantra | 1 | 

Answer 2

Please try this code. His work is wonderful for me, and it will also help you.

 $this->db->select('SalerName, count(*)'); $this->db->from('tblSaler'); $this->db->join('tblProduct', 'tblSaler.SalerID = tblProduct.SalerID'); $this->db->group_by('tblSaler.SalerID'); $query = $this->db->get(); 

You can get the whole SQL query using this line below

 $query = $this->db->get(); echo $this->db->last_query(); 

Answer 3

try it

 $this->db->select('SalerName, count(*)'); $this->db->from('tblSaler'); $this->db->join('tblProduct', 'tblSaler.SalerID = tblProduct.SalerID'); $this->db->group('SalerID'); 

Answer 4

  ## try this ## $this->db->select($column_name); $this->db->where($column_name,$type); $q=$this->db->get($table_name); $count=$q->result(); return count($count);'