Need help with active record

[eluser]profsoni[/eluser]
Hi,

i have this code in model:

Code:

$query=$this->db->like($field,'Insert%')
                ->get($table);

Basically, i was trying to get data that contains the word 'Insert'. But this doesn't work and num_rows always return 0.

i've run the query in database directly and it's return values.

i'm using sql server as database and sqlsrv driver.

Can anyone tell me what's wrong with this?

Thanks in advance.

[eluser]theshiftexchange[/eluser]
Did you try to remove the %?

Code:

$query=$this->db->like($field,'Insert')
                ->get($table);

http://ellislab.com/codeigniter/user-gui...tml#select

[eluser]profsoni[/eluser]
yeah, i've tried that too...

and this only happen if my field contains the word 'Insert' or 'Update'.

[eluser]theshiftexchange[/eluser]
can you please show us the exact SQL you used in your query which works and gives an output?

[eluser]profsoni[/eluser]
This is the code :

Code:

$query=$this->db->like('Username','Son')
                ->get('UserTable');

I also have try this :

This Works :

Code:

$query=$this->db->query("select * from UserTable where Username like '%Son%'");

This doesn't :

Code:

$query=$this->db->query("select * from UserTable where Username like '%Insert%'");

In the Sql Server there is Username 'InsertSoni'.

I'm using Sql server 2005, btw and sqlsrv driver.

Thanks

[eluser]theshiftexchange[/eluser]
Does this work?

$query=$this->db->query("select * from UserTable where Username like '%nsert%'");

[eluser]profsoni[/eluser]
yes it works.

Does that mean i can't use the word 'Insert' for parameter?

[eluser]CroNiX[/eluser]
It could be because "insert" is a mysql reserved word, but I thought that only affected column names.

[eluser]theshiftexchange[/eluser]
Ok - now try and add a new record called "testinserttest"

Now run

Code:

$query=$this->db->query(“select * from UserTable where Username like ‘%insert%’”);

Does that work?

No? Then the issue is with the word "insert". You might need to double escape it or something so MYSQL knows it is a text field, not a command.

Yes? Then the issue is because you are looking for a wildcard on the left, but there is no text to the left. i.e.

Code:

$query=$this->db->query(“select * from UserTable where Username like ‘%insert%’”);  

vs

$query=$this->db->query(“select * from UserTable where Username like ‘insert%’”);

[eluser]profsoni[/eluser]
Thanks @CroNiX & @theshiftexchange.

i've found out the problem.
Apparently it's because of the sqlsrv driver i used has been edited.

The edited code:

Code:

function _execute($sql)
{
   $sql = $this->_prep_query($sql);

          //add this part
   if(stripos($sql,'UPDATE') !== FALSE || stripos($sql,'INSERT') !== FALSE) {
            return sqlsrv_query($this->conn_id, $sql, null, array());
       }

   return sqlsrv_query($this->conn_id, $sql, null, array(
  'Scrollable'    => SQLSRV_CURSOR_STATIC,
  'SendStreamParamsAtExec' => true
   ));
}

if that part is remarked, this problem is solved.
But function

Code:

$this->db->affected_rows()

wouldn't work as that part is to fix it.

Is there any solution that can be used so that this problem won't collide each other?

Thanks.