CODEWITHSUNDEEP
codeigniter
mlms133
mlms133

Reputation: 53

Codeigniter IF inside $this->db->select

i have codeigniter code

$this->db->select("items.name as name, items.category as category, items.supplier_id as supplier_id, items.item_number as item_number,
 items.product_id as product_id, items.description as description, 
items.size as size, items.tax_included as tax_included, items.cost_price as cost_price, 
if(price_tiers.name='".$hasilsplit1."',items_tier_prices.unit_price,items.unit_price) as unit_price, items.promo_price as promo_price, 
items.start_date as start_date, items.end_date as end_date, items.reorder_level as reorder_level, items.item_id as item_id, items.allow_alt_description as allow_alt_description, items.is_serialized as is_serialized, items.image_id as image_id, items.override_default_tax as override_default_tax, items.is_service as is_service, items.deleted as deleted");
    $this->db->from('items');
    $this->db->join('items_tier_prices','items_tier_prices.item_id=items.item_id','left');
    $this->db->join("price_tiers","price_tiers.id=items_tier_prices.tier_id and price_tiers.name='".$hasilsplit1."'","left");
    $this->db->where('items.item_id', $hasilsplit0);
    $this->db->where('items.deleted', 0);

it generates error on part:

if(price_tiers.name='".$hasilsplit1."',items_tier_prices.unit_price,items.unit_price) as unit_price

the error is: 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 'as unit_price, items.promo_price as promo_price, items.start_date as sta' at line 1

how to write IF syntax correctly inside $this->db->select?

when i var_dump, it shows

SELECT items.name as name, items.category as category, items.supplier_id as supplier_id, items.item_number as item_number, items.product_id as product_id, items.description as description, items.size as size, items.tax_included as tax_included, items.cost_price as cost_price, if(price_tiers.name='Jendela Swing Double J106', items_tier_prices.unit_price, items.unit_price) as unit_price, items.promo_price as promo_price, items.start_date as start_date, items.end_date as end_date, items.reorder_level as reorder_level, items.item_id as item_id, items.allow_alt_description as allow_alt_description, items.is_serialized as is_serialized, items.image_id as image_id, items.override_default_tax as override_default_tax, items.is_service as is_service, items.deleted as deleted FROM (items) LEFT JOIN items_tier_prices ON items_tier_prices.item_id=items.item_id LEFT JOIN price_tiers ON price_tiers.id=items_tier_prices.tier_id and price_tiers.name='Jendela Swing Double J106' WHERE items.item_id = '1' AND items.deleted = 0

it seems if blocks getting character ` Thanks

Upvotes: 0

Views: 66

Answers (1)

Farside
Farside

Reputation: 10325

I think you need to check the manual, and to try to disable escaping when compiling SELECT:

$this->db->select() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names. This is useful if you need a compound select statement where automatic escaping of fields may break them.

And the second recommendation would be to escape SQL query, when you are using raw data, for example:

$this->db->escape($someString); // instead of using $someString when concatenating string for query.

Upvotes: 1

Related Questions