php的order by,php-在codeigniter中使用Order By

这是我正常的mysql查询：

$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";

}

我需要有效记录中的CI.我尝试了以下方式：

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");

}

但是,我得到了这个错误：

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

怎么解决呢？实际上,我需要按上述格式排序的商品类别.这可以在普通的mysql查询中完成.但是,在活动记录中这样做会感到困惑.

解决方法:

尝试这个：：

$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');

}

来源：https://www.icode9.com/content-1-504801.html