GROUP BY on a UNION Query

[patsfans]

I have two tables that are the same. One stores info for the offense and a second for a defense.

$sql = mysql_query("SELECT player FROM offense GROUP BY player 
UNION
SELECT player FROM defense GROUP BY player");

while ($row = mysql_fetch_array($sql)) {

$player = ($row['player']);

echo "$player<br>";

}

The problem I have is that it lists all the players, but if there's a player playing for both sides (ie: his name is listed in both tables) his name gets listed twice?
(this is just a simple example, but I'm trying to solve a bigger issue and this little one is holding me back on that.)

[leviathan185]

array_unique() could be what you are looking for

[LastMitch]

The problem I have is that it lists all the players, but if there's a player playing for both sides (ie: his name is listed in both tables) his name gets listed twice?

You must be a either a Hockey or Football fan.

Do you have the players First & Last Name? Why can't you make the $player into 2 category: $oplayer & $dplayer. It will be much simplier than what you are doing.

This is wrong you can't used 2 SELECT:

$sql = mysql_query("SELECT player FROM offense GROUP BY player
UNION SELECT player FROM defense GROUP BY player");

You can used SELECT DISTINCT player FROM offense GROUP BY player

then you can used a JOIN

[patsfans]

I tried array_unique, and it didn't eliminate the duplicate. Very odd.

On the first & last name, I don't but in that event it wouldn't matter because the neames are the same. The 2 Selects work as it gathers the names from both tables, and a join doesn't seem to work because there aren't two primary index's to link them on.

Needless to say it seems like it should be simple, but so far I haven't found a solution.

[LastMitch]

On the first & last name, I don't but in that event it wouldn't matter because the neames are the same. The 2 Selects work as it gathers the names from both tables, and a join doesn't work because there aren't two primary index's to link them otherwise.

I mean I'm confused. For example: If you have 4 players name Johnson then how would you separate it from the O and the D? If you used this:

$sql = mysql_query("SELECT player FROM offense GROUP BY player
UNION SELECT player FROM defense GROUP BY player");

It doesn't make sense to write that query?

I have seem SELECT(SELECT(),SELECT()) before but never that the one you have now.

Needless to say it seems like it should be simple, but so far I haven't found a solution.

This is not a simple query! The reason is that you have $player appearing in the O and the D at the same time.

You need query like this:

SELECT DISTINCT as 
From 
Where and 
Group by 

that will put separate the O and D.

[leviathan185]

Turns out array_unique() does not support multidimensional arryas. another way of attcking this would be.

<?php
    // define an empty array
    $players = array();

    $sql = mysql_query("SELECT player FROM offense GROUP BY player
    UNION
    SELECT player FROM defense GROUP BY player");

    while ($row = mysql_fetch_array($sql)) {
        $player $row['player'];

        // check if the palyer is a duplicate
        if(!in_array($player, $players) {
            echo $player."<br />";
        }

        // add player to array
        array_push($players, $player);
    }
?>

EDIT: kinda messed that up a bit. All fixed now.

[patsfans]

No...it still lists all of them. Had to add another ) on line 13, but thank you leviathan for taking the time. I think you're onto something there but I've tried a few variations and still get the same result.

And Mitch the "DISTINCT" doesn't seem to work on a UNION. That actually solved a previous issue for me on a "LEFT JOIN", but for some reason since there's two tables it doesn't recognize it. It's baffling.

To answer your question, I have the two lists separated in an "offense" table and a "defense" table, with a player field and a "unit" field which signifies offense or defense so I can just query that and have it sort accordingly. I'm just trying to list out the entire group of players that participated in the game but it's listing one player twice because he was in on both tables (because he played both offense & defense) and that's the problem I'm trying to fix.

Does that make better sense?

[leviathan185]

In the while loop if you put var_dump($row) what do you get?

[pritaeas]

No need for a PHP solution, change your query:

SELECT DISTINCT player FROM (
    SELECT player FROM offense
    UNION
    SELECT player FROM defense) AS temp

[patsfans]

pritaes, that query solved it ;)

To everyone else who contributed and provided guidance, I really appreciate all of you taking the time - it means a lot because I know you certainly didn't have to. So thank you again!