order_by problem

[eluser]Bigil Michael[/eluser]
return $this->db->get(‘state’)->result_array();

in this code if i remove " $this->db->get(‘state’) " result will repeat

like this



Gujarat

Jamnagar
Bhavnagar
Bhuj
Kutch

Gujarat
Jamnagar
Bhavnagar
Bhuj
Kutch

Gujarat
Jamnagar
Bhavnagar
Bhuj
Kutch

Gujarat
Jamnagar
Bhavnagar
Bhuj
Kutch

any other method to avoid this problem?????

[eluser]toopay[/eluser]
work around with this :

Code:

function select_all_state($limit=NULL, $pgoffset=NULL)
{
      $sql = "SELECT DISTINCT state.state_name,state.state_id FROM state JOIN location ON (location.state_id = state.state_id) ORDER BY state.state_name";
      if(!is_null($limit) AND !is_null($pgoffset))
      {
          $sql .= " LIMIT ".$limit." , ".$pgoffset." ";
      }
      $query = $this->db->query($sql);
      return $query->result_array();
}

[eluser]Bigil Michael[/eluser]
but it shows database error

A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

SELECT DISTINCT state.state_name,state.state_id FROM state JOIN location ON (location.state_id = state.state_id) ORDER BY state.state_name LIMIT 10 ,

[eluser]Bigil Michael[/eluser]
without limit it is working properly

thank you. Thank you so muchhhhhhhh

if possible help me to correct the limit

[eluser]danmontgomery[/eluser]
You have:

Code:

LIMIT 10 ,

either define an offset or check for it's existence before you add the ,. Or use active record, not really sure why we went away from that in this thread.

Code:

function select_all_state($limit=NULL, $pgoffset=NULL)
{
      $query = $this->db->select('state.state_name, state.state_id')
        ->distinct()
        ->join('location', 'location.state_id = state.state_id')
        ->order_by('state.state_name')
        ->limit($limit,$pgoffset)
        ->get('state');
      return $query->result_array();
}

[eluser]Bigil Michael[/eluser]
when i use
$pgoffset='0';

it willnot print any result

[eluser]toopay[/eluser]
of course that will result nothing! That means you told MySQL to bring 0 row!

Code:

function select_all_state($limit=NULL, $pgoffset=NULL)
{
      $sql = "SELECT DISTINCT state.state_name,state.state_id FROM state JOIN location ON (location.state_id = state.state_id) ORDER BY state.state_name";
      // i've just notice that you give '' as predefined value for $limit, thats why the first one not working
      if(($limit != '' AND $limit != 0) AND ($pgoffset != '' AND $pgoffset != 0))
      {
          $sql .= " LIMIT ".$limit." , ".$pgoffset." ";
      }
      $query = $this->db->query($sql);
      return $query->result_array();
}

or do with active records way, as @noctrum did...

Code:

function select_all_state($limit=NULL, $pgoffset=NULL)
{
      $this->db->select('state.state_name, state.state_id');
      $this->db->distinct();
      $this->db->join('location', 'location.state_id = state.state_id');
      $this->db->order_by('state.state_name');
      if(($limit != '' AND $limit != 0) AND ($pgoffset != '' AND $pgoffset != 0))
      {
         $this->db->limit($limit,$pgoffset);
      }
      $query = $this->db->get('state');
      return $query->result_array();
}

[eluser]danmontgomery[/eluser]
If you're building the query manually, offset has to come before limit.

Code:

$sql .= 'LIMIT '.(!empty($pgoffset)?$pgoffset.', ':'').$limit;