Welcome Guest, Not a member yet? Register   Sign In
why Codeigniter database query is case-insensitive?
#1

(This post was last modified: 04-13-2019, 06:08 AM by squashking.)

Hi guys,

I want database query to be case sensitive. I have set the table Collation to be "utf8_bin", which is supposed to be case sensitive. However, when I ran the following query code:
$this->db->where('code', $code);
$this->db->select('*');
$query=$this->db->get('my_table');
return $query->row();

I found that the query was not case-sensitive. For example, my_table has a row where code="abc" but no row with code="Abc". However, if I use $code="Abc" and do the query,  which is expected to return null, but it actually returns the row where code="abc".

So, any ideas how I can make the query case-sensitive? BTW, I am using Codeignitr 3.X

Thank you!
Reply
#2

This is not a Codeigniter issue. I think that your database runs on a windows machine. On a Linux machine database columns names are case-sensitive.
Reply
#3

(This post was last modified: 04-13-2019, 08:34 AM by dave friend.)

(04-13-2019, 06:07 AM)squashking Wrote: Hi guys,

I want database query to be case sensitive. I have set the table Collation to be "utf8_bin", which is supposed to be case sensitive. However, when I ran the following query code:
$this->db->where('code', $code);
$this->db->select('*');
$query=$this->db->get('my_table');
return $query->row();

I found that the query was not case-sensitive. For example, my_table has a row where code="abc" but no row with code="Abc". However, if I use $code="Abc" and do the query,  which is expected to return null, but it actually returns the row where code="abc".

So, any ideas how I can make the query case-sensitive? BTW, I am using Codeignitr 3.X

Thank you!

What is the data type of the 'code' column?
Can you confirm that the Collation for the code column is utf8_bin? In other words, it has not been set to a Collation different than the table default.

I cannot test the idea that it's a "Windows" OS case sensitivity issue. It certainly doesn't have anything to do with column names.

That said and for what it's worth, I cannot recreate your problem on my Linux based OS. The results are case sensitive in my test using CodeIgniter version 3.1.9
Reply
#4

Thank you muuucho and dave for the answers. I was testing using WAMP webserver, so the OS was windows. Now I have tested in Linux database and it works. So indeed it is case sensitive in Linux but not in Windows. Since I actually want it to work in Linux, my problem is solved. Thank you!
Reply




Theme © iAndrew 2016 - Forum software by © MyBB