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?
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);
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
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 ":"