CodeIgniter Forums
Using stored functions in Active Record - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Using stored functions in Active Record (/showthread.php?tid=4800)

Using stored functions in Active Record - El Forum - 12-17-2007

[eluser]Alexxz[/eluser]
How to use some DB functions in Active record?
For example in MySQL,
Code:
UPDATE table SET value = 'new value', changed = NOW();
How can I produce that kind of query with active record?
May be there is some dirty hack?
I can't use php's time functions because i have a lot of servers with different time and one DB.

Using stored functions in Active Record - El Forum - 12-17-2007

[eluser]Alexxz[/eluser]
First type of hack I've found is use comments in unqoted field names like this
Code:
$set = array(  
    'changed = NOW() /* ' => '',
    ' */ , value' => 'new value',
    );
this produces
Code:
changed = NOW() /* = '', */ , value = 'new value'
but it is not elegant way to solve this problem.

Using stored functions in Active Record - El Forum - 12-17-2007

[eluser]Alexxz[/eluser]
Another solving method is to make own mysql driver, which improves CI

mysqlex_driver.php
Code:
<?php  if (!defined('BASEPATH')) exit('No direct script access allowed');


require_once(BASEPATH.'database/drivers/mysql/mysql_driver.php');

class CI_DB_mysqlex_driver extends CI_DB_mysql_driver {
    function _update($table, $values, $where)
    {
        foreach($values as $key => $val)
        {
            if( ! strpos($key, '=')){
                $valstr[] = $key." = ".$val;
            } else {
                $valstr[] = $key;
            }
        }
        
        return "UPDATE ".$this->_escape_table($table)." SET ".implode(', ', $valstr)." WHERE ".implode(" ", $where);
    }
    
    function _insert($table, $keys, $values)
    {    
        $data = array_combine($keys, $values);
        
        foreach($data as $key => $val)
        {
            if( ! strpos($key, '=')){
                $valstr[] = $key." = ".$val;
            } else {
                $valstr[] = $key;
            }
        }
        
        return "INSERT INTO ".$this->_escape_table($table)." SET ".implode(', ', $valstr);
    }
    
}

The utility and result parts of driver just inherit standard mysql driver.

In configuration file $db['default']['dbdriver'] = "mysqlex";

Now i can write
Code:
$data = array();
$data['state'] = $newstate;
$data["changed = NOW() "] = null;
and the same syntax for $where statement (for $where works without this changes).

Also I want to make 'LIMIT' restrictions for 'DELETE' statement.

Using stored functions in Active Record - El Forum - 10-06-2008

[eluser]arslan ali[/eluser]
i was looking for following query

Code:
SELECT * FROM (`banners`) WHERE banner_type = Menu AND status =Active AND start_date>=current_date() ORDER BY `id` desc


After looking above post i tried this code and it worked magically for me.


Code:
$this->db->select(*);
$this->db->from('banners');
$this->db->where(array('banner_type'=>'Menu','status'=>'Active','start_date>=current_date()'=>"",'end_date>=current_date()'=>""))