CODEWITHSUNDEEP
mysqljoin
Chatyak
Chatyak

Reputation: 183

Combine Data in Two MySQL Tables

I'm having trouble getting the data to combine between tables for an online game community. In my database I have two tables (and values in them):

Statsme2 ----> playerid head chest stomach leftarm rightarm leftleft rightleg

Players ---> lastName, playerid, shots

The playerid from "Statsme2" is the same playerid in "Players"... and that corresponds to lastName in "Players" which is a printout of the user's name.

I want the data to show as:

Name | % of head shots

user1 | % goes here

user2 | % goes here

I got it to print out stats, but instead of 1 row per user, it spits out hundreds and hundreds of rows for every individual user.

How do I combine all of the instances into just ONE row per user? Please see the image attached for reference (Statsme2 table):

An image of the Statsme2 table

    <?php
    // Make a MySQL Connection
connection data in here
    // Get specific data from table
    $result = mysql_query("SELECT ((shots)/(BodyParts.head) * 100) as 'Head', Player.lastName as 'Name', shots
    FROM hlstats_Events_Statsme2 BodyParts
    JOIN hlstats_Players Player ON BodyParts.playerId=Player.playerId
    WHERE Player.lastName NOT LIKE '%BOT%' AND Player.lastName NOT LIKE '%infected%' AND Player.lastName NOT LIKE '%witch%' AND connection_time > '36000';")
    or die(mysql_error()); 
    echo "<table class=\"tablesorter-blackice\">";
    echo "<thead>";
    echo "<tr><th>Player</th><th>% Of Head Shots</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['Name'];
        echo "</td><td>";
        echo round($row['Head'],2)."%";
        echo "</td></tr>"; 
    } 
    echo "</tbody>";
    echo "</table>";
    mysql_close();
    ?>

And this is how it looks instead of averaging all rows into just one row per user:

Current output

Upvotes: 0

Views: 47

Answers (2)

user27
user27

Reputation: 179

You need to group your result by 'lastName' column. Then the query would be like this,

SELECT players.lastName, Statsme2.head
FROM players
JOIN Statsme2
ON players.playerid = Statsme2.playerid
GROUP BY players.lastName

Upvotes: 1

nmio
nmio

Reputation: 798

The JOIN operator is used differently than I have in the past, and I suspect it is what you are having problems with. You might want to try either of the following syntax:

SELECT [ stuff from both tables ]
FROM A
INNER JOIN B ON A.id = B.id
WHERE [ whatever, omitting equality of B.id and A.id ]

or

SELECT [ stuff from both tables ]
FROM A, B
WHERE [ whatever, including the equality of B.id and A.id ]

Upvotes: 0

Related Questions