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
$conn = require_once ('db_connect.php');
function getUserBenchmarkResults($conn, $userID)
{
$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";
$results = $conn->query($query);
if ($results === false) {
die("Error fetching benchmark results: " . $conn->error);
}
$benchmarkResults = [];
while ($row = $results->fetch_assoc()) {
$benchmarkResults[] = $row;
}
$results->free();
return $benchmarkResults;
}
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_once 'getData.php';
$userID = 1;
$userResults = getUserBenchmarkResults($conn, $userID);
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>";
}
$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');
var chartData = [];
<?php
foreach ($userResults as $row) {
$resolutionName = $row['ResolutionName'];
$runNumber = $row['RunNumber'];
$totalRenderingTime = $row['TotalRenderingTime'];
if (!isset($chartData[$resolutionName])) {
$chartData[$resolutionName] = [$resolutionName, null, null, null];
}
$chartData[$resolutionName][$runNumber] = (int) $totalRenderingTime;
}
$chartData = array_values($chartData);
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.
Submit an article or tip