0
$this->default->join('db D', 'C.col1 = D.col1 AND D.col2 = "MAIN"', 'LEFT');

I am getting 500 error on this join in CI but when i only use 

$this->default->join('db D', 'C.col1 = D.col1', 'LEFT');

query is ok.

How to do join in CI with and in the ON part of the join?

FYI

  1. Tried the entire query in MS SQL Server Management Studio and it runs ok.
  2. Putting D.col2 = "MAIN" in where clause also works
guradio
  • 15,524
  • 4
  • 36
  • 57

1 Answers1

1

What you have done should work but there are two things you could try to see if the outcome is better.

Test1: Which just exchanges where double and single quotes are used.

$this->default->join('db D', "C.col1 = D.col1 AND D.col2 = 'MAIN'", 'LEFT');

Test 2: Which turns off escaping the values and identifiers for the join call

this->default->join('db D', 'C.col1 = D.col1 AND D.col2 = "MAIN"', 'LEFT', FALSE);
DFriend
  • 8,869
  • 1
  • 13
  • 26
  • I did tried the double quotes and it worked. Can you add explanation on why `'` didnt work and `"` worked that would help – guradio Dec 03 '18 at 01:10
  • 1
    When it comes to escaping, CodeIgniter sometimes has trouble when it comes to figuring out what the different parts are. It probably has something to do the regex used to parse the inputs. I've never bothered to work through exactly how the parsing is done as I'm more interested in getting it to work. :) Why single vs double affects the outcome is a mystery. – DFriend Dec 03 '18 at 01:14
  • 1
    Also, PHP seems to be better a evaluting complex expressions that are contained in a double-quoted string. Again, I've no idea why. I do know that "Hi my name is $name"; will evaluate correctly but 'Hi my name is $name'; will not. – DFriend Dec 03 '18 at 01:17
  • explanation is great with the example happy coding mate – guradio Dec 03 '18 at 01:26