CODEWITHSUNDEEP
sqlselectcodeigniter
designer-trying-coding
designer-trying-coding

Reputation: 6064

SELECT MAX query returns only 1 variable + codeigniter

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

Upvotes: 1

Views: 18103

Answers (4)

Phil Sturgeon
Phil Sturgeon

Reputation: 30766

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.

Upvotes: 1

k00k
k00k

Reputation: 17573

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

Upvotes: 0

Trav L
Trav L

Reputation: 15192

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

Upvotes: 5

Kobi
Kobi

Reputation: 138017

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

Upvotes: 4

Related Questions