Select single value from db, >>fastest<< way

[eluser]hyperfire[/eluser]
DB_active_rec.php - LINE 1007 just under the function GET

Code:

// --------------------------------------------------------------------

    /**
     * GetVar
     *
     * Hack to get just a single row value
     *
     * @access    public
     * @param    string    the table
     * @param    string    the variable or field to get
     * @param    string    condition to be met
     * @return    string
     */
    function get_var($table = '', $var = '*', $condition = '1')
    {        
        $sql = 'SELECT ';
        $sql .= $var;
        $sql .= "\nFROM ";
        $sql .= $table;
        $sql .= "\nWHERE ";
        $sql .= "\n ";
        $sql .= $condition;

        $query = $this->query($sql);
        
        if ($query->num_rows() == 0) $result = '0';
        else $result = $query->row($var);
        
        $this->_reset_select();
        return $result;
    }

Usage:

Model:

Code:

function testGetVar()
    {
        $get = $this->db->get_var('users', 'username', 'uid = 1');
        return $get;
    }

Controller:

Code:

$content['testvar'] = $this->mdl_foo->testGetVar();

Sure you can pass parameters to your model function, make it public or private etc.
But using this function all we need is a single line of logic to get a single value.

Why not implement something like that as native code into CI...its so simple.

[eluser]xwero[/eluser]
a few remarks about your code

- the sql statement building functions are disregarded. This means manually adding prefixes, manually protecting identifiers, manually escaping values.
- your default query is SELECT * FROM table WHERE 1 while you build it to get a single value
- Zero can be a value that gets returned. The error return has to be something you know isn't possible as return
- the row method parameter is to set the row number to return not the field

If you are dead set to add the method to the AR class use this

Code:

function get_value($select = '', $table = '', $where = null)
    {
                if ($select != '')
        {
            $this->select($select);
        }        

                if ($table != '')
        {
            $this->from($table);
        }

        if ( ! is_null($where))
        {
            $this->where($where);
        }
        
        $this->limit(1);
            
        $sql = $this->_compile_select();

        $result = $this->query($sql);
        $this->_reset_select();
        if($result->num_rows == 0)
        {
            return false;
        }

        $row = $result->row();
        return $row->{$this->ar_select[0]};
    }

It's the get_where method with an added select parameter and . Now you can use it as you want or build the statement with methods.

Code:

echo $this->db->get_value('username','users','id=1');
// or
echo $this->db->select('username')->from('users')->where('id',1)->get_value();
// code now with method chaining for comparison
echo $this->db->select('username')->from('users')->where('id',1)->get()->row()->username;

But i repeat adding code to a CI class can cause problems when you are not careful when updating CI.

[eluser]hyperfire[/eluser]
Good job, a bit more clean. I knew someone not lazy as I am would just try to beat my code trying to make it fit into AR coding structure. Congratulations.
While I agree with you about hacking and stuff, isn't:

Code:

echo $this->db->get_value('username','users','id=1');

muuuuuch more simpler than:

Code:

echo $this->db->select('username')->from('users')->where('id',1)->get()->row()->username;

Of course we're not going to echo any of that, but talking about simplicity, I stay on my choice to use get_var.

After all, its much more easy to create a shortcut than opening the real folder to that application (talking about windows now, something like program files>application>executable folder>application executable ... come on, why reinvent wheel, shortcuts are here for that)

So the real question is: Why should I get stuck with method chaining if I can create a shortcut for that?

Not that I'm saying that CI is dumb or something, don't get me wrong, but if it can be improved, why not.

[eluser]Dam1an[/eluser]
[quote author="hyperfire" date="1240357615"]
So the real question is: Why should I get stuck with method chaining if I can create a shortcut for that?[/quote]

If too many shortcuts like this where built into CI, then it would become very cluttered
You can create as many specialised functions as you want, such as get_email_by_id($id), but it wouldn't be needed by most people, and even if someone else needed that functionality, they might prefer to have it over a few lines if it makes more sense to them.

CI is a platform, which lets you use whatever coding style you want, it doesn't force needless (bloat) shortcuts on you, but lets you add what you need

[eluser]hyperfire[/eluser]
We are talking about one...not too many...
The get_var() is a generic shortcut even for that get_mail you suggested.
The idea is just to get a generic wrapper to directly get a single value from db without messing with method chaining, parsing or multiple lines like the one on my first post.

[eluser]hyperfire[/eluser]
BTW, CI is not immaculate nor perfect... I don't see why this amazement about adding a simple function.
I like CI too fellows, its not about this.

[eluser]xwero[/eluser]
Of course CI can be improved but the developers have their reasons not to add it. I guess they want a clear separation beween the sql statement and the result methods. The only exception are the two count methods which are the last ones that were added to the AR class.

Quites some time ago i asked the same question, and i hammered on it like you do, but i accepted the EL decisions and found my own way.