MYSQL and PHP group results by

Question

I will like to display the following database in various html table group by Comp.

TeamA         TeamB             Comp
Kenya     Namibia       Africa   World Cup - Qual
Zimbabwe      Mozambique       Africa World Cup - Qual
Coventry City Colchester Utd   England - League One 
Bray Wanderers UCD League Of   Ireland - Premier Division
Dundalk    Drogheda United  League Of Ireland - Premier Division

example table 1

Comp: Africa World Cup - Qual
Kenya     Namibia
Zimbabwe  Mozambique

Example table 2

England - League One
Coventry City Colchester Utd

etc.

I tried

SELECT GROUP_CONCAT(Comp) as Comp 
  FROM database 
 Group By Comp`

but no luck.

can you please update your table and query in here : sqlfiddle.com — Syed Ahmed
– Syed Ahmed, Commented Sep 8, 2013 at 9:05
hey what is your query! you sql statement is wrong , we select from table not database, and you are concatenating that column in which you have applied groupby — developerCK
– developerCK, Commented Sep 8, 2013 at 9:06
You'd need to fetch every row from the db, order by comp and add grouping using php. Basic principle is explained here. — Marty McVry
– Marty McVry, Commented Sep 8, 2013 at 9:10

peterm · Accepted Answer · 2013-09-08 09:40:43Z

Are you looking for this?

SELECT comp,
       GROUP_CONCAT(CONCAT(TeamA, '|', TeamB)) details
  FROM table1
 GROUP BY comp

Output:

+--------------------------------------+--------------------------------------------+
| comp                                 | details                                    |
+--------------------------------------+--------------------------------------------+
| Africa World Cup - Qual              | Kenya|Namibia,Zimbabwe|Mozambique          |
| England - League One                 | Coventry City|Colchester Utd               |
| League Of Ireland - Premier Division | Bray Wanderers|UCD,Dundalk|Drogheda United |
+--------------------------------------+--------------------------------------------+

Here is SQLFiddle demo

If needed you can change delimiters both in CONCAT() and GROUP_CONCAT() from comma , and pipe | respectively. You can easily explode() details values while iterating over the result set.

Simplified php part using PDO might look like this

$db = new PDO('mysql:host=localhost;dbname=test', 'user', 'userpwd');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);


$sql = "SELECT comp,
               GROUP_CONCAT(CONCAT(TeamA, '|', TeamB)) details
               FROM table1
         GROUP BY comp";

$query = $db->prepare($sql);
$query->execute();
$rows = $query->fetchall(PDO::FETCH_ASSOC);

$query = null;
$db = null;

foreach($rows as $row) {
    echo $row['comp'] . '</br>';
    $details = explode(',', $row['details']);
    foreach($details as $detail) {
        list($teama, $teamb) = explode('|', $detail);
        echo $teama . ' - ' .$teamb . '</br>';
    }
    echo '</br>';
}

Output:

Africa World Cup - Qual
Kenya - Namibia
Zimbabwe - Mozambique

England - League One
Coventry City - Colchester Utd

League Of Ireland - Premier Division
Bray Wanderers - UCD
Dundalk - Drogheda United

Pianoman · Accepted Answer · 2013-09-08 09:42:15Z

I think you have a database design problem. Do I understand that your output is to display games in a certain competition? So team A plays against team B in competition C?

You should create more than 2 tables I think.

Table A: containing all your teams.
Table B containing all competitions (because probably a team could participate in more than one competition).
Table C to join teams in competitions.
Table D to hold games in a certain competition.

In table D we only link records from table C. Reason is to prevent a team playing in a competition that it is not participating in.

So :

Table A (Teams) contains columns (idTeam INT, Teamname VARCHAR 50)
Table B (Competitions) contains columns (idComp INT, Competitionname VARCHAR 50)
Table C (TeamCompetitions) contains columns (idTeam INT, idComp INT)
Table D (Games) contains columns (idTeamCompA INT, idTeamCompB INT, idComp INT)

Then the query isn't that hard:

SELECT 
  TeamA.Teamname as teamA,
  TeamB.Teamname as teamB,
  comp.Competitionname as competition
FROM 
  Games 
     JOIN TeamCompetitions AS compTeamA ON compTeamA.idTeamCompetition = Games.idTeamCompA
     JOIN Teams            AS TeamA     ON compTeamA.idTeam = TeamA.idTeam
     JOIN TeamCompetitions AS compTeamB ON compTeamB.idTeamCompetition = Games.idTeamCompB
     JOIN Teams            AS TeamB     ON compTeamB.idTeam = TeamB.idTeam
     JOIN Competitions AS comp ON compTeamA.idCompetition = comp.idCompetition

An example: Teams

1   Zimbabwe
2   Kenya
3   AJAX
4   Chelsea
5   Feyenoord
6   PEC Zwolle

Competitions

1   World Championship 2014
2   Dutch Premier League

TeamCompetitions

Games

Output of the query:

teamA   teamB   competition
Zimbabwe    Zimbabwe    World Championship 2014
Zimbabwe    Kenya   World Championship 2014
Feyenoord   AJAX    Dutch Premier League
Feyenoord   PEC Zwolle  Dutch Premier League

Hope this helps!

Collectives™ on Stack Overflow

MYSQL and PHP group results by

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related