How to combine two columns into one in mysql?

For examples if we take first name,last name as the attribute values and if i want to print the data as a single column then how do we do it?

re: How to combine two columns into one in mysql?

select concat(firstname,lastname) as 'name' from tablename

re: How to combine two columns into one in mysql?

Hi
nelaturuk
Welcome to TSDN.

You have reached the right place for knowledge shairing.

Here you will find a vast resource of related topics and code.

Feel free to post more doubts/questions in the forum.

But before that give a try from your side and if possible try to post what/how you have approached to solve the problem.
It will help Experts in the forum in solving/underestanding your problem in a better way.

re: How to combine two columns into one in mysql?

Use simple concatination and a column alias to display as a single field.

re: How to combine two columns into one in mysql?

This works fine for me when only outputting one column.

I have several fields:

no, firstname, surname, position, shoots, height, weight

I need to join firstname and surname and output as a single field along with the other fields;

so the output fields would be.

no, (firstname + lastname), position, shoots, height, weight


Thanks for any advice, here is my current code, it outputs everything I want, however the name field has a value of 'Array' in each row

<?php
/**
* @version $Id: mod_roster.php 2007-07-20
* @copyright Copyright (C) 2007 Stephen Hoult - SMH WebDesign. All rights reserved.
*/

// no direct access
defined( '_VALID_MOS' ) or die( 'Restricted access' );

// Get data records from table.

$result=mysql_query("SELECT * FROM war_roster ORDER BY position DESC, surname ASC");

mysql_query("SELECT CONCAT(firstname, surname) FROM war_roster = $name");

?>

<table width="500" class="roster_border" cellpadding="20" cellspacing="1" >
<tr bgcolor="#78171e" class="table_header">
<td valign="top"><strong>#</strong></td>
<td valign="top"><strong>Name</strong></td>
<td valign="top"><strong>Position</strong></td>
<td valign="top"><strong>Birthdate</strong></td>
<td valign="top"><strong>Height</strong></td>
<td valign="top"><strong>Weight</strong></td>
<td valign="top"><strong>Shoots</strong></td>
</tr>

<?
// Make a variable "$num" with a value "0".
$num=1;

// Do while loop for out put records.
while($row=mysql_fetch_assoc($result)){

// Plus 1 at $num.
$num++;

// Use modulus by 2 in $num value and set the value of "$bg" if result equal 0 or not.
if(($num%2)!=0){
$bg="#E6D78E";
}else{
$bg="#dcc55d";
}
?>
<tr class="table_content" bgcolor="<? echo $bg; ?>">
<td><? echo $row['no']; ?></td>
<td><? echo $row .$name; ?></td>
<td><? echo $row['position']; ?></td>
<td><? echo $row['birthdate']; ?></td>
<td><? echo $row['height']; ?></td>
<td><? echo $row['weight']; ?></td>
<td><? echo $row['shoots']; ?></td>
</tr>

<?
// End while loop.
}

// Close database connection.
mysql_close();
?>
<? echo '</table>'; ?>

re: How to combine two columns into one in mysql?

Fixed the problem,

Here is the fixed code if anyone is interested, also any advice on simplifying it would be appreciated.

re: How to combine two columns into one in mysql?

Everything is fine. To keep the script clean remove unwanted code:).

You can use ternary operator to show iterrative row color as follows
No need for this whole code
[PHP]// Plus 1 at $num.
$num++;

// Use modulus by 2 in $num value and set the value of "$bg" if result equal 0 or not.
if(($num%2)!=0){
$bg="#E6D78E";
}else{
$bg="#dcc55d";
}[/PHP]

use this one

re: How to combine two columns into one in mysql?

One more quick (i hope) question is;

The code I've been trying to write outputs a hockey team roster, name, number, position, etc. Here is the code in action: http://www.whitleywarriors.net/index.php?option=com_content&task=view&id=12&Itemi d=26

Eventually each player name will need to act as a hyperlink to that players player profile/bio, as seen here: http://www.newyorkrangers.com/team/roster.asp

As a newbie to myql/php how should I go about doing that?

Here is my updated code,

Thanks for your help guys.

re: How to combine two columns into one in mysql?

Use <a> tag around the name and provide a link to profile page with id. This id will be used to fetch the palyer information on the view profile page.