[eluser]tsandesh23[/eluser]
My Database Name is StraatJutter and there are 10's of tables. But, tables related to above question are:
1> item (id, condition, description, created_at, updated_at, image_url_original, image_url_mid, image_url_thumb, category , device_id, latitude, longitude, flagged, inappropriate, status)
2> device(id, device_token, email, facebook, phone, preferred)
Previous code was::
Code:
$sql = "select i.id, `condition`, `description`, `created_at`, `updated_at`,
concat('$url', `image_url_original`) as image_url_original,
concat('$url', `image_url_mid`) as image_url_mid,
concat('$url', `image_url_thumb`) as image_url_thumb, `category`, `latitude`, `longitude` ,
d.device_token, d.facebook, d.preferred, d.email, d.phone, status from
item AS i, device AS d WHERE created_at > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 24 HOUR)
AND status='shared' AND i.device_id = d.device_token order by ";
if($language === 'nl_NL') {
$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";
}
The above code is working properly. If the language is 'nl_NL', that is dutch language, then the categories should be ordered in above format so that in dutch language, that will be like alphabetical order.
Now, I need it in CI active records format. I did following thing::
Code:
$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");
$this->db->select("CONCAT( '$url', image_url_mid ) AS image_url_mid");
$this->db->select("CONCAT( '$url', image_url_thumb ) AS image_url_thumb");
$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("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");
}
The else part is working properly, but if part is not working , (note: $url is a variable with some url value and it's working.., it has no problem).
So, if it can be done by CASE in select, then, please give one short example. I am getting confusion in using CASE in active records and order by too.
