phpmysqlcodeigniteractiverecordsql-order-by

How to use CodeIgniter's order_by() method with FIELD function


This is my normal mysql query:

$sql = "SELECT * FROM item order by ";
if (my_condition) {
       $sql. = "FIELD(`category`, 'Auto & Accessories', 'Couch', 'Bed', 
       'Desk &     Office', 'Bike &            Scooter', 'Tools', 'Leisure', 
       'Iron & Wood', 'Cabinet', 'Kitchen & Accessories', 'Refrigerator & Appliances',
       'Toys & Games', 'Chair', 'Table', 'Garden & Terrace', 'TV, HIFI & Computers', 
       'General Item')";
} else {
     $sql .= "category asc";
}

I need it in CI in active record. I tried in following way:

if (my_condition) {
    $this->db->order_by("FIELD(`category`, 'Auto & Accessories', 'Couch',
        'Bed', 'Desk & Office', 'Bike & Scooter', 'Tools', 'Leisure', 'Iron & 
        Wood', 'Cabinet', 'Kitchen & Accessories', 'Refrigerator & 
        Appliances', 'Toys & Games', 'Chair', 'Table', 'Garden & Terrace', 
        'TV, HIFI & Computers', 'General Item')"
    );
} else {
    $this->db->order_by("category", "asc");
}

But, I got this error::

Error Number: 1064
You have an error in your SQL syntax; check the manual that corresponds to your           
MySQL server version for the right syntax to use near 'Couch'`, `'Bed'`, `'Desk` &  
Office', `'Bike` & Scooter', `'Tools'`, `'Leisure'`,' at line 6
   
Filename: C:\xampp\htdocs\straatjutter_service\system\database\DB_driver.php

Line Number: 330

How to solve it? Actually I need my item's category sorted in above format. I can do this in a normal mysql query, but I am struggling with using active record methods.


Solution

  • Try this::

                $this->db->select('i.id, condition, description, created_at, updated_at, category, latitude, longitude, status, d.device_token, d.facebook, d.preferred, d.email, d.phone');
                $this->db->select("CONCAT('$url', `image_url_original`) AS `image_url_original`", false);
                $this->db->select("CONCAT('$url', `image_url_mid`) AS `image_url_mid`", false);
                $this->db->select("CONCAT('$url', `image_url_thumb`) AS `image_url_thumb`", false);
                $this->db->select("FIELD(`category`, 'Auto & Accessories', 'Couch', 'Bed', 'Desk & Office', 'Bike & Scooter', 'Tools', 'Leisure', 'Iron & Wood', 'Cabinet', 'Kitchen & Accessories', 'Refrigerator & Appliances', 'Toys & Games', 'Chair', 'Table', 'Garden & Terrace', 'TV, HIFI & Computers', 'General Item') AS `sort_col`", false);
    
                $this->db->from('item as i');
                $this->db->join('device as d', 'i.device_id = d.device_token');
    
                $this->db->where('created_at > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 24 HOUR)');
                $this->db->where('i.status', 'shared');
    
                if($language === 'nl_NL') {
                    $this->db->order_by('sort_col', 'asc');
                } else {
                    $this->db->order_by('category', 'asc');
                }