image_browser/php/generate_sidebar.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 $superPlotGroup_ID = isset($_GET['superPlotGroup_ID']) ? (int) $_GET['superPlotGroup_ID'] : 0;
0018 if ($superPlotGroup_ID == 0) {
0019     die("Invalid superPlotGroup_ID provided.");
0020 }
0021
0022 $stmt = $conn->prepare("
0023     SELECT
0024         SuperGroups.SuperPlotGroup_ID,
0025         SuperNames.Name AS SuperGroupName,
0026         PlotGroups.ID AS PlotGroupID,
0027         PlotGroups.Name AS PlotGroupName,
0028         SubPlotGroups.ID AS SubPlotGroupID,
0029         SubPlotGroups.Name AS SubPlotGroupName
0030     FROM
0031         SuperGroups
0032     INNER JOIN
0033         PlotGroups ON SuperGroups.PlotGroup_ID = PlotGroups.ID
0034     INNER JOIN
0035         PlotGroups AS SuperNames ON SuperGroups.SuperPlotGroup_ID = SuperNames.ID
0036     INNER JOIN
0037         SuperGroups AS SubGroups ON SubGroups.SuperPlotGroup_ID = PlotGroups.ID
0038     INNER JOIN
0039         PlotGroups AS SubPlotGroups ON SubGroups.PlotGroup_ID = SubPlotGroups.ID
0040     WHERE
0041         SuperGroups.SuperPlotGroup_ID = ?
0042 ");
0043
0044 $stmt->bind_param("i", $superPlotGroup_ID);
0045 $stmt->execute();
0046 $result = $stmt->get_result();
0047
0048
0049 // Check if result is empty
0050 $plotGroups = [];
0051 if ($result->num_rows > 0) {
0052     while ($row = $result->fetch_assoc()) {
0053         $plotGroupName = $row['PlotGroupName'];
0054         $subPlotGroupName = $row['SubPlotGroupName'];
0055
0056         if (!isset($plotGroups[$plotGroupName])) {
0057             $plotGroups[$plotGroupName] = [];
0058         }
0059
0060         $plotGroups[$plotGroupName][] = $subPlotGroupName;
0061     }
0062 } else {
0063     // If no result, fetch from PlotType_Groupings where superPlotGroup_ID = PlotGroup_ID
0064     $groupingStmt = $conn->prepare("
0065         SELECT PlotType_Groupings.PlotGroup_ID, Plot_Types.Name, Plot_Types.Description, Plot_Types.DisplayName
0066         FROM PlotType_Groupings
0067         INNER JOIN Plot_Types ON PlotType_Groupings.PlotType_ID = Plot_Types.ID
0068         WHERE PlotType_Groupings.PlotGroup_ID = ?
0069     ");
0070     $groupingStmt->bind_param("i", $superPlotGroup_ID);
0071     $groupingStmt->execute();
0072     $groupingResult = $groupingStmt->get_result();
0073
0074     while ($groupingRow = $groupingResult->fetch_assoc()) {
0075         $plotGroupID = $groupingRow['PlotGroup_ID'];
0076         $plotTypeName = $groupingRow['Name'];
0077         $plotTypeDescription = $groupingRow['Description'];
0078         $plotTypeDisplayName = $groupingRow['DisplayName'];
0079
0080         if (!isset($plotGroups[$plotGroupID])) {
0081             $plotGroups[$plotGroupID] = [];
0082         }
0083
0084         $plotGroups['PlotTypes'][] = ['Name' => $plotTypeName, 'Description' => $plotTypeDescription, 'DisplayName' => $plotTypeDisplayName['DisplayName']];
0085     }
0086
0087     $groupingResult->free();
0088     $groupingStmt->close();
0089 }
0090
0091
0092 // Second query to fetch PlotType_IDs and names
0093 $plotTypeStmt = $conn->prepare("
0094     SELECT Plot_Types.ID, Plot_Types.Name, Plot_Types.Description, Plot_Types.DisplayName
0095     FROM Plot_Types
0096     INNER JOIN PlotType_Groupings ON Plot_Types.ID = PlotType_Groupings.PlotType_ID
0097     WHERE PlotType_Groupings.PlotGroup_ID = ?
0098 ");
0099 $plotTypeStmt->bind_param('i', $superPlotGroup_ID);
0100 $plotTypeStmt->execute();
0101 $plotTypeResult = $plotTypeStmt->get_result();
0102
0103 $plotTypes = [];
0104 while ($typeRow = $plotTypeResult->fetch_assoc()) {
0105     $plotTypes[] = [
0106         'Name' => $typeRow['Name'],
0107         'Description' => $typeRow['Description'],
0108         'DisplayName' => $typeRow['DisplayName']
0109     ];
0110 }
0111
0112 $data = [];
0113 foreach ($plotGroups as $key => $value) {
0114     $data[$key] = $value;
0115     $data['PlotTypes'] = $plotTypes;
0116
0117 }
0118
0119 header('Content-Type: application/json');
0120 echo json_encode($data);
0121
0122 $result->free();
0123 $plotTypeResult->free();
0124 $stmt->close();
0125 $plotTypeStmt->close();
0126 ?>