Group database records and echo group name and records of it

Question

I'm using PHP and MySQL Currently i have database structure like this

EventID | MatchName | Tournament | Results
1234    |  match1   | Tour1      | 1-0
5678    |  match2   | Tour2      | 0-1
7890    |  match3   | Tour1      | 1-2

I need to group them by Tournament and get each tournament records like this

<thead>
   <th>Tour1</th>
</thead>
<tr>
   <td>1234</td><td>match1</td><td>Tour1</td><td>1-0</td>
   <td>7890</td><td>match3</td><td>Tour1</td><td>1-2</td>
</tr>

<thead>
   <th>Tour2</th>
</thead>
<tr>
   <td>5678</td><td>match2</td><td>Tour2</td><td>0-1</td>
</tr>

Answer 1

I know that there are more easier ways to do that you want, however, with the following library you will be able to do that you want and much more:

FunctionalPHP

In the following example you will see how to do that you want:

use FunctionalPHP\common\Object;
use FunctionalPHP\common\functional\Collectors;
use FunctionalPHP\iterable\collection\lists\ArrayList;

class Event extends Object {

   protected $eventId;
   protected $matchName;
   protected $tournament;
   protected $results;

   public function __construct (int $eventId, string $matchName
                               ,string $tournament, string $results) {

      $this->eventId    = $eventId;
      $this->matchName  = $matchName;
      $this->tournament = $tournament;
      $this->results    = $results;
   }
}

$arrayListOfEvents = new ArrayList();
$arrayListOfEvents->add (new Event (1234, 'match1', 'Tour1', '1-0'));
$arrayListOfEvents->add (new Event (5678, 'match2', 'Tour2', '0-1'));
$arrayListOfEvents->add (new Event (7890, 'match3', 'Tour1', '1-2'));


$hashMap = $arrayListOfEvents->stream()
                             ->collect (Collectors::groupingBy (function (Event $event) : string {
                                                                   return $event->tournament;
                                                                }));

And you will be able to access to the stored elements with:

foreach ($hashMap->iterator() as $internalKey => $internalValue) {

    echo "\nKey of current element of Map: ", $internalKey;

    foreach ($internalValue->getIterable()->iterator() as $value) {

        echo "\n\tValue of current element of Map: ", $value->eventId
           , ", " , $value->matchName, ", ", $value->tournament
           , ", ", $value->results;
    }
 }

You will get the following:

Key of current element of Map: Tour1
   Value of current element of Map: 1234, match1, Tour1, 1-0
   Value of current element of Map: 7890, match3, Tour1, 1-2
Key of current element of Map: Tour2
   Value of current element of Map: 5678, match2, Tour2, 0-1

Now you only need to add the required HTML tags.

---

Updated

You will be able to access to mysql database using the following functions:

mysqli_connect
mysqli_fetch_array

See the example #2 of the following link:

Get results from mysql database

The final code could be something like this:

$link = mysqli_connect ("my_host", "my_user", "my_password", "my_database");
$results = mysqli_query ($link, "select * from events order by Tournament");

$currentTournament = "";
while ($row = mysqli_fetch_array ($results, MYSQLI_ASSOC)) {

   if (strcmp ($currentTournament, $row["Tournament"]) != 0) {

      $currentTournament = $row["Tournament"];
      echo "\n<thead><th>".$currentTournament."</th></thead>";
   }
   echo "\n<tr><td>".$row["EventID"]."</td><td>"
                    .$row["MatchName"]."</td><td>"
                    .$row["Tournament"]."</td><td>"
                    .$row["Results"]."</td></tr>";
}