CODEWITHSUNDEEP
phpsqlcodeigniter
NSF
NSF

Reputation: 2549

Does CodeIgniter support named query binding?

I checked the manual of CI and found it supports this kind of query binding:

$sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?";
$this->db->query($sql, array(3, 'live', 'Rick'));

However I prefer the PDO style:

$sql = "SELECT * FROM some_table WHERE id = :id AND status = :status  AND author = :author";

In this way the binding could be more flexible and sequence would not matter.

Does CI support that kind of binding?

Upvotes: 4

Views: 5793

Answers (3)

Caio Waiteman
Caio Waiteman

Reputation: 11

I extended CI_Model to perform query with parameter by name.

class MY_Model extends CI_Model{

    protected $db;
    private $sql;
    private $bind_marker;

    public function __construct() {
        $this->load->database($this->setConfigCliente());
        $CI =& get_instance();
        $this->db = $CI->db;
        $this->bind_marker = $this->db->bind_marker;
        parent::__construct();
    }


    public function setConfigCliente() {
//        $db['hostname'] = 'localhost';
//        $db['dsn'] = "mysql:host=localhost;dbname=".$this->getDbName();
        $db['hostname'] = "mysql:host=localhost;dbname=".$this->getDbName();
        $db['username'] = 'root';
        $db['password'] = 'pass#######';
        $db['database'] = $this->getDbName();
//        $db['dbdriver'] = 'mysqli';
        $db['dbdriver'] = 'pdo';
        $db['dbprefix'] = '';
        $db['pconnect'] = TRUE;
        $db['db_debug'] = TRUE;
        $db['cache_on'] = FALSE;
        $db['cachedir'] = '';
        $db['char_set'] = 'utf8';
        $db['dbcollat'] = 'utf8_general_ci';
        $db['swap_pre'] = '';
        $db['autoinit'] = TRUE;
        $db['stricton'] = FALSE;

        return $db;


    }

    final protected function query($sql, $bind=false, $return_object = true){
        $this->sql = $sql;
        if(is_array($bind) and count($bind)>0){
            $bind = $this->process_bind($bind);
        }
        $query = $this->db->query($this->sql, $bind, $return_object);
        return $query;
    }

    private function process_bind($bind){
        $bindOrder = null;
        $bindList = null;

        $pattern = "/[^']:[A-Za-z0-9_]+[^']/";
        $preg = preg_match_all($pattern, $this->sql, $matches, PREG_OFFSET_CAPTURE);
        if($preg !== 0 and $preg !== false){
            foreach($matches[0] as $key=>$val){
                $bindOrder[$key] = trim($val[0]);
            }
            foreach($bindOrder as $field){
                $this->sql = str_replace($field, $this->bind_marker, $this->sql);
                $bindList[] = $bind[$field];
            }
        }else{
            $bindList = $bind;
        }

        return $bindList;
    }

    private function getDbName(){
        /*
         * LISTA DE NOME DE BANCO SEGUNDO O SUBDOMINIO.
         */

        //AMIG
        $sub['amig']['dbName'] = 'amig';
        $sub['10']['dbName'] = 'amig';

        //SISTEMA.ARKSYS
        $sub['sistema']['dbName'] = 'sistema';
        $sub['code']['dbName'] = 'sistema';


        $host = explode('.', $_SERVER['HTTP_HOST']);
        $subDom = $host[0];
        if(isset($sub[$subDom])){
            return $sub[$subDom]['dbName'];
        }else{
            return null;
        }

    }


}

Example usage:

class Login_usuarios extends MY_Model {

    public function __construct() {
        parent::__construct();
    }

    public function get_login_dados($email, $senha) {

        $sql = "
        SELECT
            u.usu_id,
            u.usu_nome,
            u.usu_alterar_senha,
            u.usu_situacao,
            (SELECT log_data FROM log_usuario_acesso l WHERE l.usu_id = u.usu_id ORDER BY l.log_data DESC LIMIT 1) AS data_ultimo_acesso,
            (SELECT
                p.par_nome_fantasia
            FROM 
                sys_parametros p
            WHERE
                p.par_id = 1) as par_nome_fantasia
        FROM 
            cad_usuarios u
        WHERE
            u.usu_email = :usu_email
        AND
            u.usu_senha = :usu_senha
        AND
            u.usu_situacao <> :usu_situacao";

        $bind[':usu_senha'] = $senha;
        $bind[':usu_email'] = $email;
        $bind[':usu_situacao'] = 'Descartado';

        $query = $this->query($sql, $bind);

        return $query->result_array();
    }
}

Parameter identified only by ":"

Upvotes: 1

Niklas
Niklas

Reputation: 39

By default Codeigniter doesn't support named bindings.

Because the fact that it is much more readable with named bindings, i "hacked" the database class so that it accept named or unnamed bindings.

If anyone is interesting in the "hack", let me know and i will upload it somewhere

Upvotes: 3

jarchuleta
jarchuleta

Reputation: 1241

You can use named query binding. let me provide an example.

$sql = 'SELECT @column FROM @table LIMIT @limit';
$this->db->bind('@column', 'username');
$this->db->bind('@table', 'users');
$this->db->bind('@limit', 10);
$query = $this->db->query($sql);

query binding

Upvotes: -1

Related Questions