problem in select distinct - Printable Version

problem in select distinct - 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: problem in select distinct (/showthread.php?tid=41649)

problem in select distinct - El Forum - 05-13-2011

[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...

problem in select distinct - El Forum - 05-13-2011

[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

problem in select distinct - El Forum - 05-13-2011

[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.

problem in select distinct - El Forum - 05-14-2011

[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

problem in select distinct - El Forum - 05-14-2011

[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();

    }

problem in select distinct - El Forum - 05-14-2011

[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.

problem in select distinct - El Forum - 05-16-2011

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