+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: Libraries & Helpers (https://forum.codeigniter.com/forumdisplay.php?fid=11)
+--- Thread: Best Practice (/showthread.php?tid=71913)
Best Practice - emilio - 10-09-2018
Hello,
I have a doubt about using query on CI.
Should I prefer Standard Query or is better I use Query Builder?
In other words, pro and cons about Standard Query and Query Builder.
Tanks in advance
RE: Best Practice - Pertti - 10-09-2018
So you are talking about either manually writing SQL queries (SELECT x FROM t WHERE a = 'b') v using query builder $q = $this->db->select('x')->where('a', 'b')->get('t'); ?
The main difference is, for first option you are building the string for the query yourself, for other, you are using, in my humble opinion, nicer way to add logic.
PHP Code:
if ($b == 2) {
$a = 1;
}
if ($c == 3) {
$d = 1;
}
// building query manually
$sql = 'SELECT * FROM table1';
if (isset($a) || isset($d)) {
$sql .= ' WHERE';
if (isset($a)) {
$sql .= ' a = '.$a;
if (isset($b)) {
$sql .= ' AND';
}
}
if (isset($b)) {
$sql .= ' b = '.$b;
}
}
$q = $this->db->query($sql);
// same query using query builder
if (isset($a)) {
$this->db->where('a', $a);
}
if (isset($b)) {
$this->db->where('b', $b);
}
$q = $this->db->get('table1');
Additionally, query builder automatically escapes all values so you don't have to worry about SQL injection.
On down side, because it's building everything in $this->db instance, you have to make sure when you start building your query, that you also launch it, or flush everything before you start putting together another query:
PHP Code:
$this->db->where('a', 1);
if ($b == 2) {
// only call query if this condition matches
$q1 = $this->db->get('table1');
}
$q2 = $this->db->get('table2');
// If $b == 2 -> SELECT * FROM table2
// If $b != 2 -> SELECT * FROM table2 WHERE a = 1
I personally like using query builder over trying to build the query string manually myself.
RE: Best Practice - php_rocs - 10-10-2018
@emilio,
I prefer using a combo (writing my own queries and using the CI query binding feature) ... (https://codeigniter.com/user_guide/database/queries.html?highlight=query%20bind#query-bindings ).
RE: Best Practice - dave friend - 10-10-2018
My opinion? Query Builder (QB) is a great tool, but like any tool, it isn't appropriate for every task.
Most often I rely on good old $this->db->query(). And when the query requires inputs use Query Bindings so the values are escaped. The most important thing to know about QB is that it takes all the QB methods you called to create a string and then, quite literally, calls query() using the constructed string. Very often it makes little sense to jump through all the QB hoops just to build a simple query string, e.g.
PHP Code:
$query = $this->db->query("SELECT * from events");
The real value of QB - where it really shines - is when you want to produce different query strings based on some condition.
Here's a very partial list of potential changes/conditions.
- Additional or different "where" clause(s)
- Change sort order
- A different set of fields
- Insert vs Update
PHP Code:
/**
* Adds a where clause to the query based on the params
* @param string $search_option - The switch statement shows the valid options
* @param mixed $search_value - What to search for. Must be array if option is 'in' or 'ni'
* @param string $search_field - Name of the field to be searched
*/
protected function set_search($search_option, $search_value, $search_field)
{
switch ($search_option)
{
case 'cn': //'contains'
$this->db->like($search_field, $search_value);
break;
case 'nc': //'does not contain'
$this->db->not_like($search_field, $search_value);
break;
case 'eq': //'equal'
$this->db->where($search_field, $search_value);
break;
case 'ne':
//'not equal'
$this->db->where($search_field." !=", $search_value);
break;
case 'lt': //'less'
$this->db->where($search_field." <", $search_value);
break;
case 'le': //'less or equal'
$this->db->where($search_field." <=", $search_value);
break;
case 'gt': //'greater'
$this->db->where($search_field." >", $search_value);
break;
case 'ge': //'greater or equal'
$this->db->where($search_field." >=", $search_value);
break;
case 'bw': //'begins with'
$this->db->like($search_field, $search_value, 'after');
break;
case 'bn': //' does not begin with'
$this->db->not_like($search_field, $search_value, 'after');
break;
case 'in': //'is in' IMPORTANT: $search_string must be an array
$this->db->where_in($search_field, $search_value);
break;
case' ni': //'is not in' IMPORTANT: $search_string must be an array
$this->db->where_not_in($search_field, $search_value);
break;
case 'ew': //'ends with'
$this->db->like($search_field, $search_value, 'before');
break;
case 'en': //'does not end with'
$this->db->not_like($search_field, $search_value, 'before');
break;
}
}
The above method might be called multiple times depending on the number of fields the user selected to search. QB makes this kind of problem pretty easy to solve.
RE: Best Practice - emilio - 10-12-2018
Thank You for your expleining, I appreciated all three comments.
Forgive my delay but I had an accident and I was stopped some days in the bed
withouth the option to use a PC or a tablet.
Anyway, Thanx, your comments helped me very much.