• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Problem by select from db when there is a space followed by a comma in where clause

#1
[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?

#2
[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');

#3
[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 ,

#4
[eluser]johnpeace[/eluser]
Right, that's not correct usage of
Code:
$this->db->select()

Try
Code:
$this->db->query($query)
instead.

#5
[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:
&lt;?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;
  }
}
}
?&gt;


4. Model
[code]&lt;?php
class Test_model extends CI_Model {

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

#6
[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');

#7
[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.

#8
[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.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


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