• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
removing single quotes when using implode for comma seperated string of numbers

#1
[eluser]Otemu[/eluser]
Hi,

Currently my query is returning as
Code:
AND `articles`.`categoryid` IN ('2,3,4,5')
when I need it to return as
Code:
AND `articles`.`categoryid` IN (2,3,4,5)
without the single quotes

currently my controller gets a list of ids in an array
Code:
$subcatids = $this->M_Categories->getSubCatIds($catid);
then I am using PHP implode to get the comma separated number list
Code:
$commaCatIds = implode(',', $subcatids);
then in the model am running my query
Code:
$this->db->where_in('articles.categoryid', $commaCatIds);

How can I remove the single quotes or do I need to use another method to achieve this?

Any help appreciated.

Thanks






#2
[eluser]Cristian Gilè[/eluser]
where_in accepts an array as second parameter. You can skip the implode statement.

#3
[eluser]Otemu[/eluser]
[quote author="Cristian Gilè" date="1338835524"]where_in accepts an array as second parameter. You can skip the implode statement.[/quote]

That wraps the array in single quotes for each number
Code:
AND `articles`.`categoryid` IN ('2', '3', '4', '5')

How can I now remove them single quotes.

Thanks

#4
[eluser]Unknown[/eluser]
[quote author="Otemu" date="1338837030"][quote author="Cristian Gilè" date="1338835524"]where_in accepts an array as second parameter. You can skip the implode statement.[/quote]

That wraps the array in single quotes for each number
Code:
AND `articles`.`categoryid` IN ('2', '3', '4', '5')

How can I now remove them single quotes.

Thanks[/quote]

I ran into the sample problem. Here is my workaround for it:
1. Extend the Active Record class following instructions at http://mineth.net/blog/extending-codeign...hacky-way/
2. The contents of my /application/core/MY_DB_active_rec.php file
Code:
public function where_in_int_array($key, $values)
{
  return $this->_where_in_int($key, $values);
}

// This is a copy of the _where_in() fuction in system/database/DB_active_rec.php
// With the $this->ar_wherein[] value changed
protected function _where_in_int($key = NULL, $values = NULL, $not = FALSE, $type = 'AND ')
{
  if ($key === NULL OR $values === NULL)
  {
   return;
  }

  if ( ! is_array($values))
  {
   $values = array($values);
  }

  $not = ($not) ? ' NOT' : '';

  foreach ($values as $value)
  {
   //$this->ar_wherein[] = $this->escape($value); //this is where the problem comes from
   $this->ar_wherein[] = $value; //this is our fix
  }

  $prefix = (count($this->ar_where) == 0) ? '' : $type;

  $where_in = $prefix . $this->_protect_identifiers($key) . $not . " IN (" . implode(", ", $this->ar_wherein) . ") ";

  $this->ar_where[] = $where_in;
  if ($this->ar_caching === TRUE)
  {
   $this->ar_cache_where[] = $where_in;
   $this->ar_cache_exists[] = 'where';
  }

  // reset the array for multiple calls
  $this->ar_wherein = array();
  return $this;
}
3. In your model, replace
Code:
$this->db->where_in('prof_functional_expertise', $arr);
with
Code:
$this->db->where_in_int_array('prof_functional_expertise', $arr);


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.