Hello,
I would like to group the following columns into one column on the grid:
- last name and first name,
- 2 email columns
- 2 telephone columns
- Columns in MySql: last name, first name, e-mail_1, e-mail_2, tel_1, tel_2
- Current columns in the grid: | Name | First name | Email | Email | Phone | Phone |
- Desired: | Name | Email | Phone |
Thank you for your help
You can do this in your query with the CONCAT function. I find it’s easier to use a sub-query selection so the sorting/grouping still works. The ” ” in the middle adds a space character – you can add whatever you like.
Something like this should work for you:
SELECT
fullname, fullemail, fullphone
FROM
(SELECT
CONTACT(firstname, ” “, lastname) as fullname,
CONCAT(email 1, ” “, email 2) as fullemail,
CONCAT(phone 1, ” “, phone 2) as fullphone
FROM
TABLE) subquery
Hello,
Concat solution will work. Alternate solution would be to have a new column along with these column values. e.g.
Make first_name and last_name coulmns hidden. $col = array(); $col["name"] = "first_name" $col["hidden"] = true; $cols[] = $col; $col = array(); $col["name"] = "last_name" $col["hidden"] = true; $cols[] = $col; Create a new virtual column that will show both column values. $col = array(); $col["name"] = "full_name" $col["title"] = "Full Name" $col["template"] = "{first_name} {last_name}" $cols[] = $col;
Wow !
This solution works great and I used it. However, when I run a PDF, the hidden columns are shown.
How to resolve this situation?
Thank you very much for thinking of this solution for me. It’s very appreciated.
To hide columns in export, You need to set following with those columns.
$col[“export”] = false;
Hello Abu, Now everything works perfectly with this solution. I am very satisfied with the simple and effective solutions. A big thank you!
Hello Abu,
I just noticed that since I created virtual columns, I have a problem with the research that informs me that the desired columns do not exist (see screenshot) …
How to remedy this situation?
Thank you,
Bonjour,
For more precision in my description of the problem encountered, I add part of the code used so that 2 full names can be placed one below the other, by using a virtual column.
So, when I search for a name, I get the error described in the previous message, because it seems that the name of the virtual column cannot be found in the database since it is virtual.
So, what way I could use for the fields of name_1 and name_2 can be found one below the other?
Thank you,
==========================
$col = array();
$col[“title”] = “Nom”;
$col[“name”] = “nom_1”;
$col[“width”] = “225”;
$col[“hidden”] = true;
$col[“editable”] = false;
$col[“search”] = true;
$col[“sortable”] = true;
$col[“export”] = false;
$cols[] = $col;
$col = array();
$col[“title”] = “Nom”;
$col[“name”] = “nom_2”;
$col[“width”] = “225”;
$col[“hidden”] = true;
$col[“editable”] = false;
$col[“search”] = true;
$col[“sortable”] = true;
$col[“export”] = false;
$cols[] = $col;
$col = array();
$col[“title”] = “Noms”;
$col[“name”] = “noms”;
$col[“width”] = “350”;
$col[“template”] = “{nom_1}<br>{nom_2}”;
$cols[] = $col;
============================
For virtual columns, you can either disable search like:
$col[“search”] = false;
OR you can set the sql expression to be used when this field is searched, like:
$col[“dbname”] = “concat(nom_1,’ ‘,nom_2)”;