Active record can't handle 2 CONCATs?

[eluser]kirilisa[/eluser]
I just wrote a SQL statement as follows:

Code:

$this->db->select("c.contactId, c.disableFlag, c.typeCode, c.organisation");
$this->db->select("CONCAT_WS(' ', c.titleCode, c.firstName, c.lastName) as name");
$this->db->select("CONCAT_WS(' ', a.addressLine1, a.addressLine2, a.addressLine3) as address");
$this->db->from('Contact c');
$this->db->join('Address a', 'a.contactId = c.contactId');

it crashed with an error:

Code:

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 ') as address FROM (`Contact` c) JOIN `Address` a ON a.contactId =
c.contactId ' at line 1

SELECT c.contactId, c.disableFlag, c.typeCode, c.organisation, CONCAT_WS(' ', c.titleCode,
c.firstName, c.lastName) as name, a.addressLine1, a.addressLine2, a.addressLine3) as address FROM
(`Contact` c) JOIN `Address` a ON a.contactId = c.contactId

Note that the second CONCAT_WS doesn't show up. It seems that Active Record can't deal with two CONCATs being in 1 statement?

[eluser]Seppo[/eluser]
Yeah... the AR has issues when multiple selects uses the same word... A workaround this issue might be

Code:

$this->db->select(array('c.contactId', 'c.disableFlag', 'c.typeCode', 'c.organisation', "CONCAT_WS(' ', c.titleCode, c.firstName, c.lastName) as name", "CONCAT_WS(' ', a.addressLine1, a.addressLine2, a.addressLine3) as address"));
$this->db->from('Contact c');
$this->db->join('Address a', 'a.contactId = c.contactId');

[eluser]kirilisa[/eluser]
I tried it that way initially and got the same error. I just split it up so that it would be easier to read on the forum.

[eluser]Seppo[/eluser]
With CI 1.6.1 I0m getting the query as it's supposed to be using the code I posted:

SELECT c.contactId, c.disableFlag, c.typeCode, c.organisation, CONCAT_WS(' ', c.titleCode, c.firstName, c.lastName) as name, CONCAT_WS(' ', a.addressLine1, a.addressLine2, a.addressLine3) as address FROM (`Contact` c) JOIN `Address` a ON a.contactId = c.contactId

[eluser]kirilisa[/eluser]
Sweeeeeeeeet you're right, my bad, didn't notice that you had it in an array as opposed to a long string. The long string (which I had tried earlier) does not work, but your array does. I did not realize I could stick my fields in as an array tho I suppose I could have guessed it. Thanks!!