CODEWITHSUNDEEP
mysql
Chatyak
Chatyak

Reputation: 183

Combining MySQL Columns From Different Tables

I'm new to this but will explain it as best I can. The goal.... to have a MySQL table which displays the following:

Name | Weapon Used | Victim | Map

The problem: The information for this is split between two tables under the same databse.

The "name" of the player is stored in one table but the data of "who killed who" is stored in another via "killerID" and I don't know how to correlate the "killerID" to the "name".

I can get the table to work fine by printing out the information with the killerID #, but I would like to replace that with the person's name. Please see the two attached images:

Table 01

Table 02

So instead of having the table show on the website as:

13 | fire_cracker_blast | 7 | c1m2_streets

I would like it to be:

NeoMaxQ-C | fire_cracker_blast | {YNB} Chatyak | c1m2_streets

The "killerID" is the same as the "playerID" from the other table. Essentially, I want it to understand that killerID = playerID = Print player's name from playerID.

Yes, I have looked for tutorials and got my own tables working - but am struggling with this as I don't know the syntax. I understand that one column from a table must "equal" the other... but am still confused.

EDITS TO SHOW CODE

<?php
// Make a MySQL Connection
mysql_connect("URL", "user", "pw") or     die(mysql_error());
mysql_select_db("DBNAME") or die(mysql_error());
mysql_set_charset("UTF8");
// Get specific data from table
$result = mysql_query("SELECT map,killerID,victimID,weapon
FROM hlstats_Events_Teamkills
LEFT JOIN hlstats_PlayerNames
ON hlstats_Events_Teamkills.killerID=hlstats_PlayerNames.playerID;")
or die(mysql_error()); 


echo "<table class=\"tablesorter-blackice\">";
echo "<thead>";
echo "<tr><th>Map</th><th>Team Killer</th><th>Victim</th><th>Weapon Used</th></tr>";
echo "</thead>";
echo "<tbody>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>"; 
echo $row['map'];
echo "</td><td>"; 
echo $row['killerID'];
echo "</td><td>";
echo $row['victimID'];
echo "</td><td>";
echo $row['weapon'];
echo "</td></tr>"; 
} 
echo "</tbody>";
echo "</table>";
?>

Ok, I back tracked a bit... with the current code above... what you see is the second table on this page: http://chatyak.com/l4d2-mysql.php

I"m confused on the simplicity and how to actually get the combined data to print just the name, instead of the ID. I was trying both responses but perhaps I goofed something up.

Upvotes: 0

Views: 73

Answers (2)

Andrew Miner
Andrew Miner

Reputation: 6125

I think you're looking for a plain old JOIN. In your case, it would be something like:

select p.name, k.weapon_used, v.name, k.map
from events_teamKills k
join playerNames p on k.killerId = p.id
join playerNames v on k.victimId = v.id

Of course, you'll need to make sure to use the proper names of both your tables and columns (I simplified a bit for clarity).

Upvotes: 0

Jimmy Knoot
Jimmy Knoot

Reputation: 2388

You should use multiple JOINs for this, LEFT JOIN in this case:

SELECT KillerNames.name as 'Name', TeamKills.weapon as 'Weapon Used', VictimNames.name as 'Victim', TeamKills.map as 'Map'
FROM hlstats_Events_Teamkills TeamKills
LEFT JOIN hlstats_PlayerNames KillerNames
    ON TeamKills.killerId=KillerNames.playerId;
LEFT JOIN hlstats_PlayerNames VictimNames
    ON TeamKills.victimId=VictimNames.playerId;

Upvotes: 1

Related Questions