How do I get a chart with nested and hierarchical structure from mysql table

Question

0.00/5 (No votes)

See more:

hi, I'm developing a rendering benchmark app. I store my data in a MySQL DB and want to extract them and visualize a chart for comparison purposes. I'm querying the DB from "getData.php" script on my server:

<?php

// Include the database connection
$conn = require_once ('db_connect.php');

// Function to fetch user benchmark results from the database
function getUserBenchmarkResults($conn, $userID)
{
    // Prepare the SQL query to fetch benchmark results
    $query = "
        SELECT 
            rd.RenderDataID, 
            u.UserName, 
            mm.MachineModelName, 
            cp.CPUModelName, 
            s.SoftwareName, 
            re.RenderEngineName, 
            rs.ResolutionName, 
            rd.RunNumber, 
            rd.TotalRenderingTime
        FROM 
            RenderData rd
            INNER JOIN MachineModels mm ON rd.MachineModelID = mm.MachineModelID
            INNER JOIN CPUs cp ON rd.CPUModelID = cp.CPUModelID
            INNER JOIN Software s ON rd.SoftwareID = s.SoftwareID
            INNER JOIN RenderEngines re ON rd.RenderEngineID = re.RenderEngineID
            INNER JOIN Resolutions rs ON rd.ResolutionID = rs.ResolutionID
            INNER JOIN Users u ON rd.UserID = u.UserID
        WHERE 
            rd.UserID = $userID
        ORDER BY 
            rd.ResolutionID ASC, 
            rd.RenderDataID ASC, 
            rd.RunNumber ASC"; // Adjust ORDER BY clause as needed

    // Execute the query
    $results = $conn->query($query);

    // Check for errors
    if ($results === false) {
        die("Error fetching benchmark results: " . $conn->error);
    }

    // Fetch results into an array
    $benchmarkResults = [];
    while ($row = $results->fetch_assoc()) {
        $benchmarkResults[] = $row;
    }

    // Free the result set
    $results->free();

    return $benchmarkResults;
}

// Do not close the connection here; let it be automatically cleaned up by PHP at script end

then i'm creating a "index.php" to show the chart and table

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Rendering Benchmark Results</title>
    <!-- Include Google Charts library -->
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <style>
        table {
            width: 100%;
            border-collapse: collapse;
            margin-bottom: 20px;
        }

        table, th, td {
            border: 1px solid black;
            padding: 8px;
            text-align: left;
        }

        th {
            background-color: #f2f2f2;
        }
    </style>
</head>
<body>
    <h1>Rendering Benchmark Results</h1>

    <!-- Canvas for Google Chart -->
    <div style="width: 80%; margin: auto;">
        <div id="chart_div"></div>
    </div>

    <!-- TABLE start -->
    <table>
        <thead>
            <tr>
                <th>User Name</th>
                <th>Machine Model</th>
                <th>CPU Model</th>
                <th>Software</th>
                <th>Render Engine</th>
                <th>Resolution Name</th>
                <th>Run Number</th>
                <th>Total Rendering Time (ms)</th>
            </tr>
        </thead>
        <tbody>
            <?php
            // Include file containing database connection and getUserBenchmarkResults function
            include_once 'getData.php';

            // Example usage:
            $userID = 1; // Replace with the actual user ID you want to retrieve results for
            $userResults = getUserBenchmarkResults($conn, $userID);

            // Output results in table format
            foreach ($userResults as $row) {
                echo "<tr>";
                echo "<td>{$row['UserName']}</td>";
                echo "<td>{$row['MachineModelName']}</td>";
                echo "<td>{$row['CPUModelName']}</td>";
                echo "<td>{$row['SoftwareName']}</td>";
                echo "<td>{$row['RenderEngineName']}</td>";
                echo "<td>{$row['ResolutionName']}</td>";
                echo "<td>{$row['RunNumber']}</td>";
                echo "<td>{$row['TotalRenderingTime']}</td>";
                echo "</tr>";
            }

            // Close the connection
            $conn->close();
            ?>
        </tbody>
    </table>
    <!-- TABLE end -->

    <!-- PHP to JavaScript: Convert PHP array to JavaScript object for Google Chart -->
    <script>
        var userResults = <?php echo json_encode($userResults); ?>;
    </script>

    <!-- JavaScript to render Google Chart -->
    <script type="text/javascript">
        google.charts.load('current', {'packages':['corechart']});
        google.charts.setOnLoadCallback(drawChart);

        function drawChart() {
            var data = new google.visualization.DataTable();
            data.addColumn('string', 'Resolution Name');
            data.addColumn('number', 'Run 1');
            data.addColumn('number', 'Run 2');
            data.addColumn('number', 'Run 3');

            // Prepare chart data from PHP array
            var chartData = [];
            <?php
            foreach ($userResults as $row) {
                $resolutionName = $row['ResolutionName'];
                $runNumber = $row['RunNumber'];
                $totalRenderingTime = $row['TotalRenderingTime'];

                // Initialize the chartData array for each resolution name
                if (!isset($chartData[$resolutionName])) {
                    $chartData[$resolutionName] = [$resolutionName, null, null, null];
                }

                // Assign the total rendering time to the correct run number index (1-based index)
                $chartData[$resolutionName][$runNumber] = (int) $totalRenderingTime;
            }

            // Convert chartData object to an array
            $chartData = array_values($chartData);

            // Output chart data for Google Charts
            foreach ($chartData as $dataPoint) {
                echo "data.addRow(['{$dataPoint[0]}', {$dataPoint[1]}, {$dataPoint[2]}, {$dataPoint[3]}]);\n";
            }
            ?>

            var options = {
                title: 'Rendering Benchmark Results',
                vAxis: { title: 'Total Rendering Time (ms)' },
                hAxis: { title: 'Resolution Name' },
                seriesType: 'bars',
                series: { 5: { type: 'line' } }
            };

            var chart = new google.visualization.ComboChart(document.getElementById('chart_div'));
            chart.draw(data, options);
        }
    </script>
</body>
</html>

here's the result
Rendering Benchmark Results[^]

the chart is grouping the series by resolutions, but not by software and engine name
not all the results are shown in the chart(see the difference in the table).

what i want to achieve is a nested and hirerarchic structure like this

{
  "XPS 8900": {
    "Intel(R) Core(TM) i5-6400 CPU @ 2.70GHz": {
      "Blender 4.1": {
        "Eevee": {
          "320x240": [
            {
              "RunNumber": 1,
              "TotalRenderingTime": "00:00:03.4800000"
            },
            {
              "RunNumber": 2,
              "TotalRenderingTime": "00:00:03.0500000"
            },
            {
              "RunNumber": 3,
              "TotalRenderingTime": "00:00:02.8400000"
            }
          ],
          "1k_Square": [
            {
              "RunNumber": 1,
              "TotalRenderingTime": "00:00:03.3100000"
            },
            {
              "RunNumber": 2,
              "TotalRenderingTime": "00:00:03.4500000"
            },
            {
              "RunNumber": 3,
              "TotalRenderingTime": "00:00:03.3600000"
            }
          ],
          "HD_720": [
            {
              "RunNumber": 1,
              "TotalRenderingTime": "00:00:03.3800000"
            },
            {
              "RunNumber": 2,
              "TotalRenderingTime": "00:00:03.9800000"
            },
            {
              "RunNumber": 3,
              "TotalRenderingTime": "00:00:03.4400000"
            }
          ]
        }
      }
    }
  }
}

where there are unique groups, not repeated, nested and hierarchically organized series.
there should be:
-1 group for machineName
-1 group for CPU
-1 group for Software
-1 group for renderEngine
-1 group for resolutionName
-3 bars for the runs showing the totalRenderingTime as value

What I have tried:

I used the help of the AI to get the code correctly done and I'm struggling since weeks because it cannot understand my requirements. it continues giving me solutions where the series groups are repeated (multiple resolutions, multiple software etc.).

Maybe there is a better and easier solution to achieve my goals? I'm currently using Google Charts. I also tried 3D.js and Chart.js, with even worst results, but I noticed that google charts supports queries for DB and CSV or else.

I apologize for it's a lot of code to digest, but I'm really getting crazy about this.

Posted 10-Jul-24 5:56am

Member 15044276

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)