Import CSV file to MySQL table / CAST conversions

To import CSV unicode file to MySQL table via phpmyadmin (csv fields are delimited with tabulations ) :

More advanced options can be used at the shell level :

shell> cat /tmp/bit_test.txt
2
127
shell> mysql test
mysql> LOAD DATA INFILE '/tmp/bit_test.txt'
    -> INTO TABLE bit_test (@var1) SET b = CAST(@var1 AS UNSIGNED);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT BIN(b+0) FROM bit_test;
+----------+
| bin(b+0) |
+----------+
| 10       |
| 1111111  |
+----------+
2 rows in set (0.00 sec)

CAST and STR_TO_DATE are useful conversions calls while using mysql transactions :

Sample 1 : STR_TO_DATE converting string (like : 12/10/1989) in real date for obtaining real sorting on dates :
SELECT * FROM perio JOIN bulletinage ON bulletinage.per_ref = perio.per_ref 
WHERE perio.per_ref = 'PER00000001' 
ORDER BY str_to_date( bulletinage.per_numero, '%d/%m/%Y' ) ASC;

Sample 2 : CAST converting num strings in unsigned for obtaining integer order sorting :
SELECT * FROM perio per,bulletinage expp  
WHERE per.per_ref = expp.per_ref and per.per_ref= 'per00000003' order by CAST(expp.isn As UNSIGNED)

To use STR_TO_DATE With codeIgniter we can use :

 public function get_expp_by_id($id)
    {
        $sql="SELECT * FROM perio JOIN bulletinage ON bulletinage.per_ref = perio.per_ref 
        WHERE perio.per_ref = '" . $id . "' ORDER BY str_to_date( bulletinage.per_numero, '%d/%m/%Y' ) ASC;";
        
        $query = $this->db->query($sql);
       return $query;
 }    

When Conversion do not pose escape problems in codeIgniter like CAST we can use Active record like that :

public function get_expp1_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('perio');
        $this->db->join('bulletinage', 'bulletinage.per_ref = perio.per_ref');
        $this->db->where('perio.per_ref', $id);
        $this->db->order_by("CAST(bulletinage.isn As UNSIGNED)", "asc"); 
        $query = $this->db->get();
	return $query;   
    }