problem in select distinct

[eluser]Bigil Michael[/eluser]
my table structure like this
table name cities

id city_name
1 tvm
2 tvm
3 eklm
4 klm
5 tvm

i want to select the distinct city name and their corrosponding id

my code like this

Code:

function get_allcities()
    {
        $this->db->distinct();
        $result_deals = $this->db->get('cities');
        return $result_deals->result();
    
    }

it list all rows..
can any one help me??
thanks in advance...

[eluser]LinkFox[/eluser]
[quote author="Bigil MM" date="1305296640"]my table structure like this
table name cities

id city_name
1 tvm
2 tvm
3 eklm
4 klm
5 tvm

i want to select the distinct city name and their corrosponding id

my code like this

Code:

function get_allcities()
    {
        $this->db->distinct();
        $result_deals = $this->db->get('cities');
        return $result_deals->result();
    
    }

it list all rows..
can any one help me??
thanks in advance...[/quote]

Well technically they are distinct because the key and city value combined are different.

In raw SQL you would do something like SELECT DISTINCT city_name, id FROM cities.

CI is producing SELECT DISTINCT * FROM cities which will return all rows.

Hope this helps.


Regards

David

[eluser]Bigil Michael[/eluser]
also tried like this

Code:

function get_allcities()
    {
        
        $result_category = $this->db->query("SELECT DISTINCT city_name,id FROM cities");
        return $result_category->result();
    }

but the result is same.

[eluser]d1a8lo24[/eluser]
Try the following

Code:

// You can also elaborate on how you want your reult you can do a where
// a limit, order by etc...
$this->db->distinct()->group_by('somefield')->get('sometable');

You must use group by in order to get what you want.

Most of the time you use group by in a distinct query

[eluser]Bigil Michael[/eluser]
when i execute in database it give correct result

but in my view display nothing

i think i didn't returned any value.

i used code like this

Code:

function get_allcities()
    {
        $this->db->distinct()->group_by('city_name')->get('cities');
        //echo $this->db->last_query();
    }

[eluser]d1a8lo24[/eluser]
I'm not sure how you want your result but the following will return an array

This is what I do when I get a result know that everyone is different, I just found many uses for doing this.

Code:

function get_allcities()
{
   $query = $this->db->distinct()->group_by('city_name')->get('cities');
  
   if ($query->num_rows() > 0)
   {
      foreach($query->result() as $city)
      {
         // Reason for doing this is that i can also add item to my result
         $city->my_result = 'some result or thing etc...';
         $cities[] = $city;
      }
      return $cities;
   }
   // else you can ruturn an array or false
   return FALSE;
}

Now on your view just do a foreach loop to get the results.

You can also use any array function to split, re arrange and so on for your output is all up to you what you do with your filter cities. or in this case unique cities.

[eluser]Bigil Michael[/eluser]
it is working well
thanks.