How to Concatenate Column Values In MySQL Using PHP

In this article, we show how to concatenate column values in MySQL using PHP.

Concatenating column values means that you get values from multiple columns in one MySQL statement and can combine these values to do anything. This could mean you could form a statement based on the column values. Or you could do arithmetic such as add column values together or multiply them or anything. MySQL has a built-in function Concat() that allows you to concatenate any column values together and you can do whatever you want with those values.

Below we'll show an actual example of MySQL concatenation of multiple columns so that you can see the use.

So the MySQL code to copy a table definition is shown below.

SELECT Concat(name, ' works as a ', occupation)

So the above is the MySQL concatenate a column named name and a column named occupation. In between these 2 column values, we insert a string ' works as a '. Therefore, we output the person's name, then the ' works as a ' string, and then the occupation. So if one of the rows is Janice and her occupation is a software engineer, the full line will be, Janice works as a software engineer. And we've just concatenate 2 column values together.

We'll now show how to tie this in with PHP.

PHP Code

The PHP code to concatenate multiple column values is shown below.

<?php $user = "user"; $password = "password"; $host = "host"; $database= "database"; $connection= mysql_connect ($host, $user, $password); if (!$connection) { die ('Could not connect:' . mysql_error()); } mysql_select_db($database, $connection); $result= mysql_query("SELECT Concat(name, ' works as a ', occupation) AS fulldetail FROM Employees"); while($row = mysql_fetch_assoc($result)) { $sentence= $row['fulldetail'] . "<br>"; echo $sentence; } ?>

So the following code gets the data required to make a connection to a database and actually selects a database.

We then create a variable named $result that concatenates 2 columns. The columns are name and occupation. So the code concatenates these 2 column values together and saves this concatenation as if a new row in the fulldetail row. So these 2 concatenated values are now stored together in the fulldetail row. And these columns are obtained from the Employees table. With this concatenation, we add a string to the concatenation so that it reads as if a sentence. So if a row is composed of the name Janice, who works as a software engineer, the full sentence will read, "Janice works as a software engineer".

We then use a while loop so that we can read out all the rows in the table like this. The while loop fetches all the rows in the table and stores them in the $row variable.

We create a variable named $sentence that gets the rows of fulldetail, which is the concatenated string.

We then echo out the variable sentence.

If you want to see this exact example, see below.

Example of MySQL Concatenation Using PHP

So we created a table called Employees, which you can see below. It has 4 columns: ID, name, age, and occupation.

We use the following PHP code below.

<?php $user = "user"; $password = "password"; $host = "host"; $database= "database"; $connection= mysql_connect ($host, $user, $password); if (!$connection) { die ('Could not connect:' . mysql_error()); } mysql_select_db($database, $connection); $result= mysql_query("SELECT Concat(name, ', age ', age, ', works as a ', occupation) AS fulldetail FROM Employees"); while($row = mysql_fetch_assoc($result)) { $sentence= $row['fulldetail'] . "<br>"; echo $sentence; } ?>

So everything is the same, only now we concatenate 3 column values together, from the name column, the age column, and the occupation columns. We insert strings into the concatenated sentence.

Below is the output from the PHP code based on the above table.

Actual PHP Output