Concatinate fields in mysql query

Posted: November 13, 2007 in Mysql Stuff, Mysql Tips
Tags: , ,

Normally we store data cobine values in different fields in database like First Name, Middle Name, Last Name. But while retriving data usually we wan’t to display data as a single string like display First Name, Middle Name, Last Name as a single string “User Name”.

To achive this output we concatinate all these sub values, some thing like this in PHP

<?php
$sql = "select first_name,middle_name,last_name from student where Id=1";
$rst = mysql_query($sql);if($rst)
{
    $row = mysql_fetch_array($rst);
    echo "User Name - ".$row['first_name']." ".$row['middle_name']." ".$row['last_name'];
}?>

<?php
$sql = "select concat_WS(' ',first_name,middle_name,last_name) as name from student where Id=1";
$rst = mysql_query($sql);if($rst)
{
    $row = mysql_fetch_array($rst);
    echo "User Name - ".$row['name'];
}?>

For more details on concat_ws() and mysql string function you can also refer mysql manual.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s