Codeigniter causing mysql syntax error

Question

I would like to check whether r.is_show_map = 1, if true, then return r.lat, otherwise, return null

Here is the code

  $this->db->select('r.*, IF(r.is_show_map = 1, r.lat, null) as r.lat', false);

The problem is it caused the syntax 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 '.lat FROM (`request` as r) WHERE `r`.`adv_type` != 0 AND `r`.`is_delete` = 0 AN' at line 1

SELECT r.*, IF(r.is_show_map = 1, r.lat, null) as r.lat FROM (`request` as r) WHERE `r`.`adv_type` != 0 AND `r`.`is_delete` = 0 AND NOW() <= DATE_ADD(r.create_date, INTERVAL 15 DAY)

How to fix it? Thanks

Have you checked the manual? What is the error number associated to? — PatJ
– PatJ, Commented Mar 17, 2015 at 2:36

user4035 · Accepted Answer · 2015-03-17 02:40:39Z

1

Try this:

$this->db->select('r.*, IF(r.is_show_map = 1, r.lat, null) as lat', false);

answered Mar 17, 2015 at 2:40

user4035

24k11 gold badges69 silver badges101 bronze badges

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

7 Comments

user782104 Over a year ago

I wonder if I select r.* and r.lat at the same time, then what would be the final result , is the r.lat prior to the r.lat in r.*? thanks

user4035 Over a year ago

@user782104 f I select r.* and r.lat at the same time, then what would be the final result - field r.lat will be duplicated in the result. "is the r.lat prior to the r.lat in r.*" - what do you mean by the word "prior"? First in order? Id depends upon the order you put them in the query.

user782104 Over a year ago

that means r.* , r.lat , then the final result will be r.lat instead of r.lat inside r.*?

user4035 Over a year ago

@user782104 Actually, both of them are final result. It would be better to rename the second lat - alias of IF to some other name to be able to distinguish them.

user4035 Over a year ago

@user782104 Sorry, it's hard to answer your question about the duplicates without more information. Please, prepare a new new question with an sqlfiddle with sample data and the query, that you run. Then explain, what you need to get. I think, you'll get a meaningful answer.

|

Collectives™ on Stack Overflow

Codeigniter causing mysql syntax error

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related