I'm using Codeigniter 2. I'm trying to make a mysql query using Active Record. The problem is I got error when array passed to where_in is empty. I'm asking if there any way to ignore where_in condition if the array is empty
This the condition example :
$this->db->where_in('p.brand_id', $brands);
And this is the error I got :
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND
p.brand_idIN ()
Just don't call where_in() if the array is empty.
if(is_array($brands) && count($brands) > 0){
$this->db->where_in('p.brand_id', $brands);
}
I changed in Active Record (DB_active_rec.php class) and I Added this code to Ignore where_in condition if values array's empty. So it's works now for me.
The code I added is between //@begin and //@end
/**
* Where_in
*
* Called by where_in, where_in_or, where_not_in, where_not_in_or
*
* @param string The field to search
* @param array The values searched on
* @param boolean If the statement would be IN or NOT IN
* @param string
* @return object
*/
protected function _where_in($key = NULL, $values = NULL, $not = FALSE, $type = 'AND ')
{
if ($key === NULL OR $values === NULL)
{
return;
}
//@begin
// Ignore where_in condition if values array's empty
if(is_array($values) && empty($values))
{
return $this;
}
//@end
// More method stuff
You can cleanly omit the WHERE IN expression by falling the value parameter back to null when the array is empty.
$this->db->where_in('p.brand_id', $brands ?: null);
Some snippets of source code:
where_in() calls _where_in().
public function where_in($key = NULL, $values = NULL, $escape = NULL)
{
return $this->_where_in($key, $values, FALSE, 'AND ', $escape);
}
In _where_in() there is a conditional early return which avoids adding to the cache of WHERE conditions.
protected function _where_in($key = NULL, $values = NULL, $not = FALSE, $type = 'AND ', $escape = NULL)
{
if ($key === NULL OR $values === NULL)
{
return $this;
}
// ... more native code ...
These CI2 and CI1 repos contain a flaw in that the early return doesn't return $this. These functions serve to allow the omission of the WHERE IN statement, but that feature also breaks query builder chainability.
https://github.com/rhivent/master_codeigniter2/blob/master/system/database/DB_active_rec.php
protected function _where_in($key = NULL, $values = NULL, $not = FALSE, $type = 'AND ')
{
if ($key === NULL OR $values === NULL)
{
return;
}
// ... more native code ...
https://github.com/kidino/CodeIgniter-1/blob/develop/system/database/DB_query_builder.php
protected function _where_in($key = NULL, $values = NULL, $not = FALSE, $type = 'AND ', $escape = NULL)
{
if ($key === NULL OR $values === NULL)
{
return $this;
}
// ... more native code ...