How to SELECT MAX() as well as other columns with CodeIgniter's query builder query

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');

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

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

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.

Your querying logic is fundamentally flawed. Using MAX() without GROUP BY will return s single row of results. Appending non-aggregate data in the SELECT clause will yield data from a record which is undeterministic/unpredictable/unrelated.

So the following script can broadly be considered an antipattern:

public function BAD_getLatestRow_BAD(): ?array
{
    // DO NOT USE THIS SCRIPT!
    return $this->db
        ->select()
        ->select_max('id')
        ->get_where('gallery', ['cat' => '1'])
        ->row_array();
}

select_max() does not serve as a way to target a row with the max value in a particular column. Here's a SQLize Demo of how SELECT *, MAX(id) is simply flawed. When I tested SELECT MAX(id), *, the query simply failed.

Instead, use ORDER BY and LIMIT ro return a single row containing the highest id value.

public function getLatestRow(): ?array
{
    return $this->db
        ->order_by('id', 'desc')
        ->get_where('gallery', ['cat' => '1'], 1)
        ->row_array();
}