+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: why Codeigniter database query is case-insensitive? (/showthread.php?tid=73346)
why Codeigniter database query is case-insensitive? - squashking - 04-13-2019
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!
RE: why Codeigniter database query is case-insensitive? - muuucho - 04-13-2019
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.
RE: why Codeigniter database query is case-insensitive? - dave friend - 04-13-2019
(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
RE: why Codeigniter database query is case-insensitive? - squashking - 04-13-2019
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!