mySQL help in codeigniter

Question

Hello,

I am running a query to populate a dropdown menu, however the column I am using a list of company names, this means that the company name is often repeated, is there way to only get each repeated value only once? So for instance if I have in the table something like,

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

But I would like the dropdown to return,

Company 1
Company 2
Company 3
Company 4

I am using the database libray and active record to write the sql like this currently I need to know what I use to only show each result once,

function getAllUsers() {
    $this->db->select('*');
    $this->db->from('userProfileTable');

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

Or what would the raw sql be?

Answer 1

Use DISTINCT keyword:

SELECT DISTINCT * from userProfileTable;

Answer 2

Hi,

Use,

$this->db->distinct();

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

$this->db->from('userProfileTable');

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

return $query->result_array();

Regards,
Pedro

Answer 3

Hi! This should do the trick:

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

Answer 4

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.