SELECT MAX query returns only 1 variable + codeign

问题:

I use codeigniter and have an issue about SELECT MAX ... I couldnot find any solution at google search...

it looks like it returns only id :/ it's giving error for other columns of table :/

Appreciate helps, thanks!

---

Model:

   function get_default() 
   {
        $this->db->select_max('id');
        $query = $this->db->getwhere('gallery', array('cat' => "1"));   

        if($query->num_rows() > 0) {
             return $query->row_array(); //return the row as an associative array
        }
    }

---

Controller:

$default_img = $this->blabla_model->get_default();
$data['default_id'] = $default_img['id']; // it returns this
$data['default_name'] = $default_img['gname']; // it gives error for gname although it is at table

回答1:

To achieve your goal, your desire SQL can look something like:

SELECT *
FROM gallery
WHERE cat = '1'
ORDER BY id
LIMIT 1

And to utilise CodeIgniter database class:

$this->db->select('*');
$this->db->where('cat', '1');
$this->db->order_by('id', 'DESC'); 
$this->db->limit(1);
$query = $this->db->get('gallery');

回答2:

That is correct: select_max returns only the value, and no other column. From the specs:

$this->db->select_max('age');
$query = $this->db->get('members');
// Produces: SELECT MAX(age) as age FROM members

You may want to read the value first, and run another query.
For an id, you can also use $id = $this->db->insert_id();

See also: http://www.hostfree.com/user_guide/database/active_record.html#select

回答3:

CodeIgniter will select * if nothing else is selected. By setting select_max() you are populating the select property and therefore saying you ONLY want that value.

To solve this, just combine select_max() and select():

$this->db->select('somefield, another_field');
$this->db->select_max('age');

or even:

$this->db->select('sometable.*', FALSE);
$this->db->select_max('age');

Should do the trick.

回答4:

It should be noted that you may of course also utilize your own "custom" sql statements in CodeIgniter, you're not limited to the active record sql functions you've outlined thus far. Another active record function that CodeIgniter provides is $this->db->query(); Which allows you to submit your own SQL queries (including variables) like so:

function foo_bar()

{
   $cat = 1;
   $limit = 1;
   $sql = "
        SELECT *
        FROM gallery
        WHERE cat = $cat
        ORDER BY id
        LIMIT $limit
   ";
   $data['query'] = $this->db->query($sql);
   return $data['query'];
}

Recently I have been utilizing this quite a bit as I've been doing some queries that are difficult (if not annoying or impossible) to pull off with CI's explicit active record functions. I realize you may know this already, just thought it would help to include for posterity.

2 helpful links are:

http://codeigniter.com/user_guide/database/results.html

http://codeigniter.com/user_guide/database/examples.html