image_browser/php/get_groups.php

0001 <?php
0002 require_once('./configurations.php');
0003
0004 $servername = $FEconfig['dbhost'];
0005 $username = $FEconfig['dbuser'];
0006 $password = $FEconfig['dbpass'];
0007 $dbname = $FEconfig['dbname'];
0008
0009 // Create connection
0010 $conn = new mysqli($servername, $username, $password, $dbname);
0011
0012 // Check connection
0013 if ($conn->connect_error) {
0014     die("Connection failed: " . $conn->connect_error);
0015 }
0016
0017 // Updated SQL to fetch groups and subgroups
0018 $sql = "SELECT SuperGroups.SuperPlotGroup_ID, SuperNames.Name AS SuperGroupName, PlotGroups.ID, PlotGroups.Name AS PlotGroupName
0019         FROM SuperGroups
0020         INNER JOIN PlotGroups ON SuperGroups.PlotGroup_ID = PlotGroups.ID
0021         INNER JOIN PlotGroups AS SuperNames ON SuperGroups.SuperPlotGroup_ID = SuperNames.ID
0022         ORDER BY SuperGroups.SuperPlotGroup_ID, PlotGroups.Name;";
0023
0024 $result = $conn->query($sql);
0025
0026 $data = [];
0027 if ($result && $result->num_rows > 0) {
0028     while ($row = $result->fetch_assoc()) {
0029         // Apply ucfirst to capitalize the first letter of SuperGroupName
0030         $superGroupName = ucfirst($row['SuperGroupName']);
0031         $plotGroupEntry = ['id' => $row['ID'], 'name' => $row['PlotGroupName']];
0032
0033         // Create super group array if it does not exist
0034         if (!array_key_exists($superGroupName, $data)) {
0035             $data[$superGroupName] = [];
0036         }
0037
0038         // Append the plot group entry to the corresponding super group
0039         $data[$superGroupName][] = $plotGroupEntry;
0040     }
0041     // Convert the data array to JSON and print it
0042     echo json_encode($data);
0043 } else {
0044     echo json_encode(['error' => 'No results found']);
0045 }
0046
0047 $conn->close();
0048 ?>