Using CONCAT() in active record

[eluser]Unknown[/eluser]
I need to concatenate a short string to one of the fields in this query but I think the quoting is creating problem. Is there any way to use CONCAT with a string in this context?

Code:

$this->db->select('CONCAT('IP-', info_prod.ID), STEP_NAME, FROM_UNIXTIME(RECEIVE_STAMP), WORK_TITLE');

Thanks

[eluser]TheFuzzy0ne[/eluser]

Code:

$this->db->select("CONCAT('IP-', info_prod.ID), STEP_NAME, FROM_UNIXTIME(RECEIVE_STAMP), WORK_TITLE");

The key is to escape apostrophes that are within strings that use apostrophes for quotes, or escaping double quotes that appear within strings that are enclosed within double quotes.

Basically:

Code:

$string1 = "/"" // Needs escaping.
$string2 = '\''; // Needs escaping.
$string3 = "'"; // Doesn't need escaping.
$string4 = '"'; // Doesn't need escaping.

The way you were doing it would result in a parse error, as the parser reads as far as the second apostrophe, and assumes the string ends, but doesn't find anything after it that it expects to find.

[eluser]Unknown[/eluser]
Sorry for the newbie question with an obvious answer. I'll think about it a little harder next time...

Thanks

[eluser]TheFuzzy0ne[/eluser]
Newbie? Perhaps. But we all make mistakes. What matters is that we learn from them.

[eluser]daniMichael[/eluser]
Hey TheFuzzy0ne,

Could you help me get my concat statement working, I couldn't find a solution using google, so I came here.

I need to concatenate first name and last name from the users table.

Code:

$this->db->query('SELECT DISTINCT(CONCAT(first_name," ", last_name)) as concat_first_last_name FROM '.$table_data['table_name']);

That works like a charm, obviously its just a straight sql query. This will work for me in one section of my application. But will need CONCAT in the $this->db->select() function due to dynamic-ness.

I'm trying:

Code:

$this->db->select("CONCAT('first_name', 'last_name')");

And basically a dozen different combinations of backticks, single quotes, double quotes, showing table names, etc. (I was just trying to get it working without the space between for now)

This is my first app using Code Igniter.
If you (or anyone else), could help me out this would greatly be appreciated.

Thanks

[eluser]TheFuzzy0ne[/eluser]
Try passing FALSE as the second parameter to the select method. That should prevent the Active Record class escaping the select statement (as it doesn't need escaping anyway).

[eluser]daniMichael[/eluser]
ahh Phenomenal!
first time using false parameter, lesson learned. Thank you very much!

[eluser]TheFuzzy0ne[/eluser]
...And first time visiting the forums, I just noticed. Welcome to the CodeIgniter community!

[eluser]nigwoko[/eluser]
Thanks TheFuzzy0ne

[eluser]AIM-andrew[/eluser]
I have a similar problem but I'm struggling with syntax.
This is my predecessor's code (which works-if the wrong way now):

Code:

public function addToUserId($email, $new_to_user_id) {
     $this->db->select('username');
     $this->db->from('fa_user');
     $this->db->from('user');
     $this->db->where('fa_user_id = id');
     $this->db->where('user_id', $new_to_user_id);
     $results = $this->db->get()->result();

     $email_to = $email->getTo();
     $email_to[] = $results[0]->username;
}

This is the code I wish to implement:

Code:

public function addToUserId($email, $new_to_user_id) {
     // SFPD uses [email protected] format
     // $this->db->select('username');
     $this->db->select('CONCAT (firstname, \'.\', lastname) AS full_name');
     $this->db->from('fa_user');
     $this->db->from('user');
     $this->db->where('fa_user_id = id');
     $this->db->where('user_id', $new_to_user_id);
     $results = $this->db->get()->result();

     $email_to = $email->getTo();
     // $email_to[] = $results[0]->username;
     $email_to[] = $results[0]->full_name;
}

This method retrieved the username based on the user_id currently logged in and appends a static domain (another method). Now we want to retrieve the firstname(dot)lastname combination as per the new email address format.

Any advise?
thx in advance