Not getting DISTINCT values from query

Not getting DISTINCT values from query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Not getting DISTINCT values from query (/showthread.php?tid=49109)

Not getting DISTINCT values from query - El Forum - 02-08-2012

[eluser]jordantkj[/eluser]
Hi,
The following code is not returning DISTINCT item values. If I run this query from phpmyadmin, it works as expected. Any ideas? Is there at least a way to echo out the query string provided to mysql?

Code:
$this->db->distinct('item');

$this->db->join('chassis_support', 'chassis_support.part_num = component.part_num', 'inner');

$query = $this->db->get('component');

if ($query->num_rows() > 0) {

   foreach($query->result() as $row) {

     echo 'Query Result: ' . $row->item . '</br>';

   }

}

OUTPUT:
Query Result: Midplane
Query Result: Power Supply
Query Result: Power Supply
Query Result: CFEB
Query Result: Fan Tray
Query Result: Midplane
Query Result: FPM GBUS
Query Result: FPM Display
Query Result: CIP
Query Result: PEM
Query Result: PEM
Query Result: SCG
Query Result: SCG
Query Result: CB
Query Result: CB
Query Result: SPMB
Query Result: SPMB
Query Result: SIB
Query Result: SIB
Query Result: SIB
Query Result: Fan Tray
Query Result: Fan Tray
Query Result: Fan Tray

Not getting DISTINCT values from query - El Forum - 02-08-2012

[eluser]meigwilym[/eluser]
Have you tried explicitly connecting the fields to the tables?

Code:
$this->db->distinct('cs.item');

$this->db->join('chassis_support AS cs', 'cs.part_num = c.part_num', 'inner');

$query = $this->db->get('component AS c');

Not getting DISTINCT values from query - El Forum - 02-08-2012

[eluser]Bhashkar Yadav[/eluser]
plz try this

Code:
$this->db->distinct();

$this->db->select('item');

$this->db->join('chassis_support', 'chassis_support.part_num = component.part_num', 'inner');

$query = $this->db->get('component');

Code:
$this->db->select('DISTINCT(item)');

$this->db->join('chassis_support', 'chassis_support.part_num = component.part_num', 'inner');

$query = $this->db->get('component');

Not getting DISTINCT values from query - El Forum - 02-08-2012

[eluser]jordantkj[/eluser]
First one worked great. Thanks for the posts!