Issue when compare a value in column with number

Need Help
1

Hi All,
I have a issue, i do not know this is bug or not, could you please help to analyze problem.
My table hs a column ‘contents’ with type ‘varchar’.
when i insert value to this column (ex: xyz x#1, y, z can be any number) then i do query $this->find()->where([‘content >’ => ‘20’])->toArray();
The query will return xyz for me. that is my expectation.
but when i insert value like 1yz (y, z can be any number) then i do query $this->find()->where([‘content >’ => ‘20’])->toArray();
The query will return empty array.
I have tried with many value like 123; 145; 134; 1678 but it returned same result.
Do you had the same situation? please give me an advice.
Thank you very much.
(Ps: i have tried with cakephp 3.6 and cakephp 1.2)

2

its not cake fault, you have defined this field as varchar and mysql is comparing using string comparison not as integers

select '100' > '20';
>>> return 0

select '300' > '20';
>>> return 1

if you NEED this field as varchar you need to cast it to integer like https://stackoverflow.com/questions/12126991/cast-from-varchar-to-int-mysql

1 Like