MySQL, Concatenate two columns
PhpMysqlSqlPhp Problem Overview
There are two columns in a MySQL table: SUBJECT
and YEAR
.
I want to generate an alphanumeric unique number which holds the concatenated data from SUBJECT and YEAR.
How can I do this? Is it possible to use a simple operator like +
?
Php Solutions
Solution 1 - Php
You can use the CONCAT
function like this:
SELECT CONCAT(`SUBJECT`, ' ', `YEAR`) FROM `table`
Update:
To get that result you can try this:
SET @rn := 0;
SELECT CONCAT(`SUBJECT`,'-',`YEAR`,'-',LPAD(@rn := @rn+1,3,'0'))
FROM `table`
Solution 2 - Php
You can use mysql built in CONCAT() for this.
SELECT CONCAT(`name`, ' ', `email`) as password_email FROM `table`;
change filed name as your requirement
then the result is
and if you want to concat same filed using other field which same then
SELECT filed1 as category,filed2 as item, GROUP_CONCAT(CAST(filed2 as CHAR)) as item_name FROM `table` group by filed1
Solution 3 - Php
In php, we have two option to concatenate table columns.
First Option using Query
In query, CONCAT keyword used to concatenate two columns
SELECT CONCAT(`SUBJECT`,'_', `YEAR`) AS subject_year FROM `table_name`;
Second Option using symbol ( . )
After fetch the data from database table, assign the values to variable, then using ( . ) Symbol and concatenate the values
$subject = $row['SUBJECT'];
$year = $row['YEAR'];
$subject_year = $subject . "_" . $year;
Instead of underscore( _ ) , we will use the spaces, comma, letters,numbers..etc
Solution 4 - Php
In query, CONCAT_WS()
function.
This function not only add multiple string values and makes them a single string value. It also let you define separator ( ” “, ” , “, ” – “,” _ “, etc.).
Syntax –
CONCAT_WS( SEPERATOR, column1, column2, ... )
Example
SELECT
topic,
CONCAT_WS( " ", subject, year ) AS subject_year
FROM table
Solution 5 - Php
I have two columns: prenom and nom so to concatenate into a column with name chauffeur_sortant I used this script:
SELECT date as depart, retour, duree_mission, duree_utilisation, difference, observation, concat( tb_chaufeur_sortant.prenom, ' ', tb_chaufeur_sortant.nom) as chauffeur_sortant, concat(tb_chaufeur_entrant.prenom, ' ', tb_chaufeur_entrant.nom) as chauffeur_entrant
FROM tb_passation
INNER JOIN tb_vehicule
ON tb_vehicule.id = tb_passation.id_vehicule
INNER JOIN tb_chaufeur_sortant
ON tb_chaufeur_sortant.id = tb_passation.id_sortant
INNER JOIN tb_chaufeur_entrant
ON tb_chaufeur_entrant.id = tb_passation.id_entrant WHERE tb_vehicule.id = '';
Solution 6 - Php
$crud->set_relation('id','students','{first_name} {last_name}');
$crud->display_as('student_id','Students Name');