Codeigniter IF inside $this->db->select [duplicate]

Question

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

what error are you getting? you haven't specified.

Farside
– Farside

2016-03-21 13:14:30 +00:00
Commented Mar 21, 2016 at 13:14 — Farside
– Farside, Commented Mar 21, 2016 at 13:14
hi @Farside, pls check edited Question.thx

mlms133
– mlms133

2016-03-21 13:15:57 +00:00
Commented Mar 21, 2016 at 13:15 — mlms133
– mlms133, Commented Mar 21, 2016 at 13:15

Farside · Accepted Answer · 2016-03-21 13:22:33Z

1

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.

answered Mar 21, 2016 at 13:22

Farside

10.4k4 gold badges52 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mlms133 Over a year ago

hi Farside thanks i have put FALSE as second parameter and select works with if. thanks :)

Collectives™ on Stack Overflow

Codeigniter IF inside $this->db->select [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related