How to fetch unique rows from a database table with CodeIgniter?

Question

I am running a query to populate a dropdown menu.

My trouble is that the column I am using contains duplicate company names. Is there a way adjust my CodeIgniter query to only get unique company names in my result set?

For instance, if I have the following company names in the table:

Company 1
Company 1
Company 2
Company 3
Company 4
Company 4

I would like to pass only unique company names to the dropdown field.

Company 1
Company 2
Company 3
Company 4

I am using the below active record script to generate the SQL. I need to know what I should use to only show each unique company name once.

function getAllUsers() {
    $this->db->select('*');
    $this->db->from('userProfileTable');
    
    $query = $this->db->get();
    return $query->result_array();
}

Or more simply, what would the raw SQL be? Then I can work on translating that into query builder methods.

Pedro · Accepted Answer · 2014-10-19 14:30:13Z

3

Use:

$this->db->distinct();
$this->db->select('*');
$this->db->from('userProfileTable');
$query = $this->db->get();
return $query->result_array();

edited Oct 19, 2014 at 14:30

user142162

answered Feb 11, 2010 at 13:07

Pedro

2,9277 gold badges34 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Randell · Accepted Answer · 2010-02-14 20:01:00Z

1

You can use $this->db->distinct(); which adds the DISTINCT keyword to your query.

Modify your function to this:

function getAllUsers() {
    $this->db->distinct();
    $query = $this->db->get('userProfileTable');

    return $query->result_array()
}

which produces

SELECT DISTINCT * FROM userProfileTable

Please note that select and from have been removed from your original function because get simply does the same thing.

answered Feb 14, 2010 at 20:01

Randell

6,1716 gold badges47 silver badges71 bronze badges

Comments

noonex · Accepted Answer · 2010-02-11 11:56:29Z

0

Use DISTINCT keyword:

SELECT DISTINCT * from userProfileTable;

answered Feb 11, 2010 at 11:56

noonex

2,1231 gold badge19 silver badges20 bronze badges

1 Comment

noonex Over a year ago

have you read this part of question: "Or what would the raw sql be?" ?

Industrial · Accepted Answer · 2010-02-11 20:06:05Z

0

This should do the trick:

function getAllUsers() {
    $this->db->distinct();
    $query = $this->db->get('userProfileTable');
    return $query->result_array();
}

answered Feb 11, 2010 at 20:06

Industrial

42.9k71 gold badges203 silver badges293 bronze badges

Comments

mickmackusa · Accepted Answer · 2025-03-07 06:18:50Z

You did not share any sample database table data, so we don't 100% know if your rows with shared company names might have unique values in other columns. This is important because if other columns have unique values, then merely adding DISTINCT before * will not work as needed. If full rows are identical, then I'd say you have a flaw in your data storage strategy.

Using SELECT DISTINCT * FROM userProfileTable:

public function getUniqueUsers(): array
{
    return $this->db
        ->distinct()
        ->get('userProfileTable')
        ->result_array();
}

Using SELECT DISTINCT id, name FROM userProfileTable:

public function getUniqueUsers(): array
{
    return $this->db
        ->distinct()
        ->select('id, name')
        ->get('userProfileTable')
        ->result_array();
}

If using GROUP BY, nominate the grouping columns in the SELECT clause because * will cause trouble when aggregate functions are expected.
Using SELECT id, name FROM userProfileTable GROUP BY id, name:

public function getUniqueUsers(): array
{
    return $this->db
        ->select('id, name')
        ->group_by('id, name')
        ->get('userProfileTable')
        ->result_array();
}

Finally, there may be performance ramifications, so read this for further insights:
What's faster, SELECT DISTINCT or GROUP BY in MySQL?

Collectives™ on Stack Overflow

How to fetch unique rows from a database table with CodeIgniter?

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related