CodeIgniter Forums

Full Version: AR update bug when calculating with fields
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[eluser]xwero[/eluser]
I have a simple update query
Quote:update menu set number = number-1 where number > ?
But when i rewrite it as an AR update query
Code:
$data = array('number' => 'number-1');
$this->db->where('number >',$row->number);
$this->db->update('menu',$data);
it generates following sql statement
Quote:UPDATE menu SET number = 'number-1' WHERE number > '2'
And it subtracts all records.

If no records were subtracted i could at least see the logic but this is just weird to me.

El Forum

[eluser]Derek Allard[/eluser]
Funny. I just added this to AR last night, but it hasn't worked its way into the svn yet.
Quote:set() will also accept an optional third parameter ($escape), that will prevent data from being escaped if set to FALSE. To illustrate the difference, here is set() used both with and without the escape parameter.

$this->db->set('field', 'field+1', FALSE);
$this->db->update('mytable');
// gives INSERT INTO mytable (field) VALUES (field+1)

$this->db->set('field', 'field+1');
$this->db->update('mytable');
// gives INSERT INTO mytable (field) VALUES ('field+1')

This is will be in the svn shortly.

El Forum

[eluser]xwero[/eluser]
Great, i didn't know about the set method but once it is available i'm going to use this nice addition.

Another small addition in the AR class would be selecting the wildcard position for the like method; both (default), left, right

Code:
/**
     * Like
      *
     * Generates a &#xLI;KE% portion of the query. Separates
     * multiple calls with AND
     *
     * @access    public
     * @param    mixed
     * @param    mixed
     * @return    object
     */
    function like($field, $match = '',$wildcard = 'both') // changed
    {
        return $this->_like($field, $match, 'AND ', $wildcard);
    }
    
    // --------------------------------------------------------------------

    /**
     * OR Like
     *
     * Generates a &#xLI;KE% portion of the query. Separates
     * multiple calls with OR
     *
     * @access    public
     * @param    mixed
     * @param    mixed
     * @return    object
     */
    function orlike($field, $match = '',$wildcard = 'both') // changed
    {
        return $this->_like($field, $match, 'OR ',$wildcard); // changed
    }
    
    // --------------------------------------------------------------------

    /**
     * Like
     *
     * Called by like() or orlike()
     *
     * @access    private
     * @param    mixed
     * @param    mixed
     * @param    string
@param    string
     * @return    object
     */
    function _like($field, $match = '', $type = 'AND ', $wildcard = 'both')
    {
        if ( ! is_array($field))
        {
            $field = array($field => $match);
        }
    
        foreach ($field as $k => $v)
        {
            $prefix = (count($this->ar_like) == 0) ? '' : $type;
            
            $v = $this->escape_str($v);
                // begin change                    
                switch($wildcard)
                        {
                           case 'left': $this->ar_like[] = $prefix." $k LIKE '%{$v}'"; break;
                           case 'right': $this->ar_like[] = $prefix." $k LIKE '{$v}%'"; break;
                           default: $this->ar_like[] = $prefix." $k LIKE '%{$v}%'"; break;
                        }
                        // end change
        }
        return $this;
    }

El Forum

[eluser]Derek Allard[/eluser]
Thats been in there since Dec 17 Wink