We are here with the fix to the most recent problem we faced while working on a CRM i.e TYPE CASTING Problem.
For those who are not aware what, TYPE CASTING is let us suppose a scenario where you have by mistake or intentionally used a Varchar Data type field for what supposed to be the Integer/Decimal field.
Here the most basic problem occurs while querying the data by using ORDER BY clause is that it orders all the data of that particular field by 1st character for example.
Let us suppose a situation where we want to obtain all the ages in an ascending order from the table below whose AGE field is a Varchar which was supposed to be Integer.
Table Name – Users
|ID||Name||Age (This field has varchar datatype)|
Now for getting all users age you will have a query
Now the above query will give you the most frustrating result like.
Here Since the field datatype is Varchar so the MySQL is ordering the Age by using 1st character and first come first serve basis i.e 19 came ahead of 17 so 19 will be ordered first.
Now in order to tackle the situation you don’t need to change the data type of the field as you might end up losing your precious data. Now Typecasting will act as a life saver for you for above query to work correctly you will have to use CAST Function in order by clause. Take a look
Now the code will be
$this->db->select(‘Age’)->from(‘users’)->order_by('CAST(Age AS DECIMAL(10,6)) ASC');
This will simply tell MYSQL to CAST i.e to change the field from Varchar to Decimal for this particular query and then order it in ascending order.
The above Query will yield the output-
Hurray! Hope this tutorial will help you in fixing the Type Casting or Wrong selection of data type issue for our newbie’s who have started their careers and are unaware of these horrendous effects of choosing the wrong data type.
Stay Tuned for next Quick fix.
Because we believe Hurdles aren’t really Hurdles