1

I would like to know whats the best practice in writing a database (mysql) query in codeigniter. i have seen many different ways but was confused what would be the best practice.

Following are few methods i have gone through

Method 1

$this -> db -> select('id, username, password'); $this -> db -> from('users'); $this -> db -> where('username = ' . "'" . $username . "'"); $this -> db -> where('password = ' . "'" . MD5($password) . "'"); $this -> db -> limit(1);

Method 2 

$query = $this->db->query("select * from user where username = '$uname' and password = '$pass'");

Method 3 (writing a stored procedure) 

$query = "call authenticateuser(" . $this->db->escape($parameters['username']) . ", '" . sha1($parameters['password']) . "')";

There might may be many other ways, but would like to know what the best way in writing a query, therefore if anyone could advise, it would be really helpful.

Improve this question
4
  • Active records is the best practice. Commented Nov 22, 2012 at 9:06
  • 2
    The problem with all the above methods are that they are open to SQL injection. You should really make use of the available parameters and write your WHERE functions like this $this -> db -> where('username',$username); . Which will automatically escape the strings for you. Commented Nov 22, 2012 at 9:50
  • 1
    Note: be careful. Codeigniter uses old mysql_* functions for escaping which is in the process of deprecation. Even if you are using codeigniter, please change the default driver to pdo. Commented Nov 22, 2012 at 10:00
  • Apparently, you could just switch the database config to use the mysqli_*() driver instead of the mysql_*() one, which should solve that problem. Commented Nov 22, 2012 at 10:08

4 Answers 4

Reset to default
1

Active records so best is method1 with little refactoring:

$this->db->select('id, username, password'); $this->db->from('users'); $this->db->where('username',$username); $this->db->where('password', MD5($password)); $this->db->limit(1);

check your where() and mine where() :)

Active record query building is more comfortable, and readable when building queries, expecially if you need many filters where,like,order_by,join,limit writing down by hands the queries should be probably little faster, but i encourage you to use active record building.

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

1st of all, it is NOT active record. Codeigniter developers are butchering the whole terms. This is just query builder and nothing else. 2nd, saw queries are always faster than active record/query builder. Third, for complicated queries, active record fails to perform because that is not what it is for.
so they should change their doc, i just report what is inside codeigniter.com/user_guide/database/active_record.html and it is called Active Record
@Ispuk, you should probably mention why your method is better.
Note, method 2 could also be acceptable if rewritten in the format: $this->db->query('SELECT * FROM users WHERE username = ?', array($username));.
he should ask better which is the best practice for a specific query he gives, anyway when possible active records always is my choice, don't know if you agree
|
0

i usually this to get data from database, it very easy and quick;

My model:

public function getdata($select,$table,$where,$limit,$offset,$order){ if(isset($select) && !empty($select)){ $this->db->select("$select"); } if(isset($limit) && !empty($limit)){ if(isset($offset) && !empty($offset)){ $this->db->limit($limit,$offset); } else{ $this->db->limit($limit); } } if(isset($where) && !empty($where)){ foreach ($where as $key => $value) { $this->db->where($key,$value); } } if(isset($order) && !empty($order)){ foreach ($order as $key => $value) { $this->db->order_by($key,$value); } } $query = $this->db->get("$table"); return $query->result(); }

My Controller:

$this->my_model->getdata('','user',array('userid' => 1,'username' => 'my_username'),'','','');

this means: 

select * from user where userid = 1 and username = 'my_username';

other example:

$this->my_model->getdata('username','user','',10,'','','');

means:

select username from user limit 10; //my_model is model class name;
Improve this answer

Comments

0

For me I'm using Stored Procedure although it is tedious in coding but the advantage are easy in maintenance, security and speed. Here is my example:

 function user_list($str_where, $str_order, $str_limit){ $str_where = $this->db->call_function('real_escape_string', $this->db->conn_id, $str_where); $str_order = $this->db->call_function('real_escape_string', $this->db->conn_id, $str_order); $str_limit = $this->db->call_function('real_escape_string', $this->db->conn_id, $str_limit); $qry_res = $this->db->query("CALL rt_sample_list('{$str_where}', '{$str_order}', '{$str_limit}');"); $res = $qry_res->result(); $qry_res->next_result(); // Dump the extra resultset. $qry_res->free_result(); // Does what it says. return $res;

}

Improve this answer

Comments

0

I use my own crud_model which is based upon CI's Active record in actual. It's better to pass $params array rather flooding functions with parameters.

In my Model:

//for JOINS etc stuff public function get_joined_data($params){ if (!empty($params['select'])) { $this->db->select($params['select']); } $this->db->from($params['from']); if (is_array($params['join']) || count($params['join']) > 1) { foreach ($params['join'] as $key => $value) { $join_values = explode(',', $value); $this->db->join($join_values[0], $join_values[1], $join_values[2]); } } else{ $join_values = explode(',', $params['join'][0]); $this->db->join($join_values[0], $join_values[1], $join_values[2]); } if (!empty($params['key']) && !empty($params['where'])) { $this->db->where($params['key'], $params['where']); } else if (is_array($params['where'])) { $this->db->where($params['where']); } if (isset($params['like']) && is_array($params['like'])) { $this->db->like($params['like']); } if (isset($params['orderby']) && is_array($params['orderby'])) { foreach ($params['orderby'] as $name => $order) { $this->db->order_by($name, $order); } } if (isset($params['limit'])) { $this->db->limit($params['limit']['count'], $params['limit']['start']); } $query = $this->db->get(); return $query->result(); }

And in Controller:

//joins() $params = array( 'from' => '$from', 'join' => $join_arr, 'where' => $where_arr, 'orderby' => $orderby_arr );`enter code here` $donors_data = $this->crud_model->get_joined_data($params);
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.