⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

Concatenate two or more fields into one field?



nilesht

nilesht
  • profile picture
  • Member

Posted 21 May 2012 - 12:40 PM

I want to concatenate two fields into one. For example: CONCAT('First_name', 'Last_name') as name for display in grocery crud table. How can I achieve this?

I have tried following but it is not works:
$crud->columns('id', 'CONCAT(\'First_name\', \' \', \'Last_name\') as Full_name','DOB','City', 'Status');

How can I used CONCAT operation in grocery crud?

mztriz

mztriz
  • profile picture
  • Member

Posted 21 May 2012 - 14:07 PM

I believe you have to use set_model to create a custom model for this : http://www.grocerycr...tions/set_model

Then do something like this in the model:
[left]
$this->db->select("CONCAT(firstname, '.', lastname) AS full_name", FALSE); 
[/left]

That should display it, but I don't know if it'll allow you to edit it with that alone. I think you have to use callbacks afterwards.

j-curato

j-curato
  • profile picture
  • Member

Posted 05 September 2012 - 07:30 AM

this could help you....use the [color=#008000]set relation function[/color]..for example lets say we have a [color=#ff0000]students [/color]table compose of the following fields: [color=#0000cd]id,first_name,last_name ,id [/color][color=#000000]is the primary key [/color][color=#000000]and we have another table say..[/color][color=#ff0000]subjects [/color][color=#000000]table[/color][color=#ff0000] [/color][color=#000000]with the following fields: [/color][color=#0000cd]id, student_id, subject [/color][color=#000000]where [/color][color=#0000ff]id [/color][color=#000000]is the primary key and [/color][color=#0000ff]student_id[/color][color=#000000] is the foreign key in relation to [/color][color=#ff0000]students[/color][color=#000000] table. Now if you want to display the[/color][color=#0000ff] first_name[/color][color=#000000] and [/color][color=#0000ff]last_name [/color][color=#000000]fields being concatenated replacing the [/color][color=#0000ff]student_id[/color][color=#000000] field in the [/color][color=#ff0000]subject [/color][color=#000000]table as [/color][color=#0000ff]Students Name[/color][color=#000000] field,you will just do the following:[/color]


[color=#ff0000]$crud[/color]->set_relation('[color=#008000]id[/color]','[color=#008000]students[/color]','{[color=#008000]first_name[/color]} {[color=#008000]last_name[/color]}');
[color=#ff0000]$crud[/color]->display_as('[color=#008000]student_id[/color]','[color=#008000]Students Name[/color]');



just like that...hope this will help....

saulimus

saulimus
  • profile picture
  • Member

Posted 05 September 2012 - 14:14 PM

j-curato's suggestion should work if the fields are in another table...


If they aren't, one option is to create an SQL View:



CREATE VIEW User_view AS
SELECT id, CONCAT(First_name, ' ', Last_name), 'DOB', 'City', 'Status' FROM User;


Then you need to specify to GroceryCrud what the primary key of your view is before using it:
$crud->set_primary_key('User_view', 'id');


Another method is using a callback:

// Added a new column called 'fullname'
// First_name and Last_name need to be included to be able to reference them in the callback
$crud->columns('id', 'Full_name', 'First_name', 'Last_name', 'DOB', 'City', 'Status');
// unset the other name columns from view
$crud->unset_columns('First_name', 'Last_name');
$crud->callback_column('Full_name',array($this,'_cb_col_full_name'));

public function _cb_col_full_name($value, $row)
{
$str = $row->First_name.' '.$row->Last_name;
return $str;
}


Hope all the code works, just typed without checking it... :P
I don't have access to my dev environment right now...

212

212
  • profile picture
  • Member

Posted 06 June 2014 - 10:31 AM

@saulimus 

The callback code didn't work for me, nothing was displayed because the "Full_name" column doesn't exist. However, you can easily just put the data in an existing field and rename that.

 

My code (the relevant parts only):

//Table setup
$crud->columns('first_name','last_name');

$crud->unset_columns('first_name');
 
$crud->callback_column('last_name',array($this,'_cb_col_last_name'));
 
$crud->display_as('last_name','Name');
//Callback function
public function _cb_col_last_name($value, $row)
{
    return htmlentities($row->first_name.' '.$value,ENT_QUOTES,'utf-8');
}

The drawback of the callback approach is that only the last name is searchable. However, merging fields in a view is often not an option because it only works if you don't need to edit your data.