Problem by select from db when there is a space followed by a comma in where clause

[eluser]Libelle[/eluser]
Hi,

I got the following problem -

I have in my model something like

Code:

$this->db->select('select val from mytable where val like "%'.$where_clause.'%"', FALSE);
echo 'select value from mytable where value like "%'.$where_clause.'%"'; //for control

where clause($where_clause) is a parameter sent to the function. This parameter is a string.

The problem appears if there is a space followed by comma in $where_clause - f.i. 'aaa , bbb'. For any reason CI ignores the first space and string becomes 'aaa, bbb'

I just changed my statement in this way that db error is returned -

Code:

$this->db->select('select val1 from mytable where val like "%'.$where_clause.'%"', FALSE);

there is no val1 column I I get error

Code:

A Database Error Occurred

Error Number: 1054

Unknown column 'val1' in 'field list'

Then sql statement is dumped. And here I see that the statement is

Code:

select value from mytable where value like '%ааа, bbb%'

It must be

Code:

select value from mytable where value like '%ааа , bbb%'

One space was cutted and as a result it returns no rows.

$where_clause is sent to the function with the spaces - this could be verified by echoing the variable.
If I use

Code:

$this->db->from('mytable');
$this->db->like('val', $where_clause);
$query = $this->db->get();

it works like a charm. But I have quite complex sql query and have to use

Code:

$this->db->select

Any ideas what could be wrong?

[eluser]johnpeace[/eluser]
It doesn't look like you're using

Code:

$this->db->select()

correctly.

It looks like you're trying to pass an entire query to that method, when it only expects the fields you're trying to get data from in your query.

Your query could be probably be rewritten as an active record statement. If it can't, and you need to write a whole query to run you should try this:

Code:

$this->db->query('YOUR QUERY HERE');

[eluser]Libelle[/eluser]
Right, as I wrote it it is wrong. I don't use

Code:

$this->db->select('select val1..., but
$this->db->select('val1... - sorry, typo problem.

My statement is actually like

Code:

$this->db->select('col1, col2, count(*) from mytable1, mytable2 where col1 like ... and col3 like ... and col4 like ... and col5 in (select id from mytable3 where name like <here comes $where_clause>);

$where_clause is something like 'xxx xxx-xxx xxxx , xxxxx!' f.i.
It is processed like 'xxx xxx-xxx xxxx, xxxxx!' - xxxx, instead xxxx ,

[eluser]johnpeace[/eluser]
Right, that's not correct usage of

Code:

$this->db->select()

Try

Code:

$this->db->query($query)

instead.

[eluser]Libelle[/eluser]
Simple testcase

1. Create table mytable in your mysql database

Code:

CREATE TABLE IF NOT EXISTS `mytable` (
  `name` varchar(10) COLLATE utf8_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

2. Insert a row

Code:

INSERT INTO `mytable` (`name`) VALUES ('aaa , bbb');

3. Controller

Code:

<?php
class Test extends CI_Controller {

function index()
{
  $this->load->model('Test_model');
  $query = $this->Test_model->myfunc();
  foreach ($query->result() as $row) {
   echo $row->name;
  }
}
}
?>

4. Model
[code]<?php
class Test_model extends CI_Model {

function myfunc()
{
$this->db->select('name from mytable where name like "

[eluser]CroNiX[/eluser]
Your simple test case won't work because your query is still wrong in your model! That's not how you write it. Check the manual for Active Record. Several people mentioned this and you keep doing the same thing.

Code:

$this->db
  ->select('name')
  ->like('name', $your_search_val)
  ->get('table_name');

[eluser]Libelle[/eluser]
[quote author="johnpeace" date="1329416486"]Right, that's not correct usage of

Code:

$this->db->select()

Try

Code:

$this->db->query($query)

instead.[/quote]

johnpeace, thank you - it works now, blank character is not cut.

[eluser]Libelle[/eluser]
[quote author="CroNiX" date="1329420161"]Your simple test case won't work because your query is still wrong in your model! That's not how you write it. Check the manual for Active Record. Several people mentioned this and you keep doing the same thing.

Code:

$this->db
  ->select('name')
  ->like('name', $your_search_val)
  ->get('table_name');

[/quote]

Thank you.