SELECT with LIMIT in Codeigniter

Try this...

function nationList($limit=null, $start=null) {    if ($this->session->userdata('language') == "it") {        $this->db->select('nation.id, nation.name_it as name');    }    if ($this->session->userdata('language') == "en") {        $this->db->select('nation.id, nation.name_en as name');    }    $this->db->from('nation');    $this->db->order_by("name", "asc");    if ($limit != '' && $start != '') {       $this->db->limit($limit, $start);    }    $query  = $this->db->get();    $nation = array();    foreach ($query->result() as $row) {        array_push($nation, $row);    }    return $nation;     }

For further visitors:

// Executes: SELECT * FROM mytable LIMIT 10 OFFSET 20// get([$table = ''[, $limit = NULL[, $offset = NULL]]])$query = $this->db->get('mytable', 10, 20);// get_where sample, $query = $this->db->get_where('mytable', array('id' => $id), 10, 20);// Produces: LIMIT 10$this->db->limit(10);  // Produces: LIMIT 10 OFFSET 20// limit($value[, $offset = 0])$this->db->limit(10, 20);

I don't know what version of CI you were using back in 2013, but I am using CI3 and I just tested with two null parameters passed to limit() and there was no LIMIT or OFFSET in the rendered query (I checked by using get_compiled_select()).

This means that -- assuming your have correctly posted your coding attempt -- you don't need to change anything (or at least the old issue is no longer a CI issue).

If this was my project, this is how I would write the method to return an indexed array of objects or an empty array if there are no qualifying rows in the result set.

function nationList($limit = null, $start = null) {    // assuming the language value is sanitized/validated/whitelisted    return $this->db        ->select('nation.id, nation.name_' . $this->session->userdata('language') . ' AS name')        ->from('nation')        ->order_by("name")        ->limit($limit, $start)        ->get()        ->result();}

These refinements remove unnecessary syntax, conditions, and the redundant loop.

For reference, here is the CI core code:

/** * LIMIT * * @param   int $value  LIMIT value * @param   int $offset OFFSET value * @return  CI_DB_query_builder */public function limit($value, $offset = 0){    is_null($value) OR $this->qb_limit = (int) $value;    empty($offset) OR $this->qb_offset = (int) $offset;    return $this;}

So the $this->qb_limit and $this->qb_offset class objects are not updated because null evaluates as true when fed to is_null() or empty().