How to combine two columns into one in mysql?

[nelaturuk]

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?

[frankwu]

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

[debasisdas]

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.

[debasisdas]

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

[keeps21]

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>'; ?>

[keeps21]

Fixed the problem,

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

<?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");
 
$result=mysql_query("SELECT no, position, birthdate, height, weight, shoots, CONCAT(firstname,' ',surname) AS 'name' FROM war_roster ORDER BY position DESC, surname ASC");
 
?>
 
<table width="500" class="roster_border" cellpadding="0" 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 class="padding" align="left"><? 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>'; ?>
 

[mwasif]

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

<tr class="table_content" bgcolor="<?=(++$num%2==0 ? "#E6D78E" : "#dcc55d")?>">

[keeps21]

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.

<?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 no, position, birthdate, height, weight, shoots, CONCAT(firstname,' ',surname) AS 'name' FROM war_roster ORDER BY position DESC, surname ASC");
 
?>
 
<table width="600" class="roster_border" cellpadding="0" 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=0;
 
// Do while loop for out put records. 
while($row=mysql_fetch_assoc($result)){
 
 
?>
<tr class="table_content" bgcolor="<?=(++$num%2==0 ? "#E6D78E" : "#dcc55d")?>">
<td><? echo $row['no']; ?></td>
<td align="left"><? echo $row['name']; ?></td>
<td align="left"><? 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>'; ?>
 

[mwasif]

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.

<td align="left"><a href="playerprofile.php?id=<?=$row['no']?>"><? echo $row['name']; ?></a></td>