Using CONCAT() in active record

Using CONCAT() in active record - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Using CONCAT() in active record (/showthread.php?tid=15744)

Pages: 1 2

Using CONCAT() in active record - El Forum - 02-13-2009

[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

Using CONCAT() in active record - El Forum - 02-13-2009

[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.

Using CONCAT() in active record - El Forum - 02-13-2009

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

Thanks

Using CONCAT() in active record - El Forum - 02-13-2009

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

Using CONCAT() in active record - El Forum - 05-14-2009

[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

Using CONCAT() in active record - El Forum - 05-14-2009

[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).

Using CONCAT() in active record - El Forum - 05-14-2009

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

Using CONCAT() in active record - El Forum - 05-14-2009

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

Using CONCAT() in active record - El Forum - 08-13-2009

[eluser]nigwoko[/eluser]
Thanks TheFuzzy0ne

Using CONCAT() in active record - El Forum - 08-14-2009

[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