如何将JSON编码转为数组字符串并实现SQL驱动的Chart.js图表

Let's break down how to solve both your issues step by step:

1. Fixing the Invalid JSON Array Format #

Your current code echoes individual JSON arrays (like ["11:00"]["09:00"]) which is invalid. Instead, collect all your time labels into a single PHP array first, then encode the entire array once to get a valid JSON string.

2. Dynamically Populating Chart.js with SQL Server Data #

We'll collect the labels and data points from your SQL query, then inject them directly into the Chart.js configuration.

Modified stats.php Code #

Here's the updated code that addresses both problems:

// Build the chart container HTML (keep your existing code here)
$out .= '<div class="col-lg-5 col-md-5 col-sm-5 col-lg-offset-1 col-md-offset-1 col-sm-offset-1" style="height: 300px">';
$out .= '<div class="chartjs-size-monitor">';
$out .= '<div class="chartjs-size-monitor-expand">';
$out .= '<div class=""></div>';
$out .= '</div>';
$out .= '<div class="chartjs-size-monitor-shrink">';
$out .= '<div class=""></div>';
$out .= '</div>';
$out .= '</div>';
$out .= '<canvas id="myChart2" class="canpas chartjs-render-monitor" style="display: block; width: 454px; height: 300px;" width="454" height="300"></canvas>';
$out .= '</div>';

// Initialize arrays to hold chart data
$chartLabels = [];
$maxUserValues = [];
$avgUserValues = [];

// Run your SQL query
$graph_query = mssql_query("SELECT TOP 100 Convert(nvarchar,dtDate,108) AS Date, serial, nAverageUser, nMaxUser FROM tbl_ServerUser_Log ORDER BY serial DESC");

while ($graph = mssql_fetch_array($graph_query)) {
    // Apply your percentage increment calculation
    $graph['nMaxUser'] = round($graph['nMaxUser'] * $percent_inc);
    $graph['nAverageUser'] = round($graph['nAverageUser'] * $percent_inc);
    
    // Format the time label
    $timeLabel = date('H:i', strtotime($graph['Date']));
    
    // Add data to our arrays
    $chartLabels[] = $timeLabel;
    $maxUserValues[] = $graph['nMaxUser'];
    $avgUserValues[] = $graph['nAverageUser'];
    
    // Keep your existing serie1/serie2 code if you need it for other charts
    $serie1->addPoint(new Point($graph['Date'], $graph['nMaxUser']));
    $serie2->addPoint(new Point($graph['Date'], $graph['nAverageUser']));
}

// Reverse arrays to show chronological order (oldest to newest)
// Remove this if you prefer latest entries first
$chartLabels = array_reverse($chartLabels);
$maxUserValues = array_reverse($maxUserValues);
$avgUserValues = array_reverse($avgUserValues);

// Add the dynamic Chart.js script to your output
$out .= '<script type="text/javascript">';
$out .= 'var ctx = document.getElementById(\'myChart2\').getContext(\'2d\');';
$out .= 'var myChart2 = new Chart(ctx, {';
$out .= '    type: \'line\',';
$out .= '    data: {';
$out .= '        labels: ' . json_encode($chartLabels) . ','; // Dynamic labels
$out .= '        datasets: [{';
$out .= '            backgroundColor: \'rgba(232,72,163,0.2)\',';
$out .= '            borderColor: \'#e848a3\',';
$out .= '            label: \'Max Users\',';
$out .= '            data: ' . json_encode($maxUserValues) . ','; // Dynamic max user data
$out .= '            fill: true,';
$out .= '            pointRadius: 5,';
$out .= '            pointHoverRadius: 10,';
$out .= '            showLine: true';
$out .= '        }, {';
// Optional: Add average users dataset
$out .= '            backgroundColor: \'rgba(72,180,232,0.2)\',';
$out .= '            borderColor: \'#48b4e8\',';
$out .= '            label: \'Average Users\',';
$out .= '            data: ' . json_encode($avgUserValues) . ','; // Dynamic average user data
$out .= '            fill: true,';
$out .= '            pointRadius: 5,';
$out .= '            pointHoverRadius: 10,';
$out .= '            showLine: true';
$out .= '        }]';
$out .= '    },';
$out .= '    options: {';
$out .= '        responsive: true,';
$out .= '        maintainAspectRatio: false,';
$out .= '        title: {';
$out .= '            display: true,';
$out .= '            text: \'Total Online Yesterday\',';
$out .= '            position: \'top\',';
$out .= '            fontSize: 15,';
$out .= '            fontStyle: \'bold\'';
$out .= '        },';
$out .= '        legend: {';
$out .= '            display: true'; // Enable legend if using two datasets
$out .= '        },';
$out .= '        elements: {';
$out .= '            point: {';
$out .= '                pointStyle: \'circle\'';
$out .= '            }';
$out .= '        },';
$out .= '        scales: {';
$out .= '            xAxes: [{';
$out .= '                display: true,';
$out .= '                scaleLabel: {';
$out .= '                    display: true,';
$out .= '                    labelString: \'TIME\'';
$out .= '                },';
$out .= '                ticks: {';
$out .= '                    major: {';
$out .= '                        fontStyle: \'bold\',';
$out .= '                        fontColor: \'#FF0000\'';
$out .= '                    }';
$out .= '                }';
$out .= '            }],';
$out .= '            yAxes: [{';
$out .= '                display: true,';
$out .= '                scaleLabel: {';
$out .= '                    display: true,';
$out .= '                    labelString: \'TOTAL ONLINE\'';
$out .= '                }';
$out .= '            }]';
$out .= '        },';
$out .= '        tooltips:{';
$out .= '            callbacks: {';
$out .= '                title: function(tooltipItem, data) {';
$out .= '                    return \'User Stats \'+data[\'labels\'][tooltipItem[0][\'index\']];';
$out .= '                },';
$out .= '                label: function(tooltipItem, data) {';
$out .= '                    return data[\'datasets\'][tooltipItem[\'datasetIndex\']][\'label\']+\' : \'+data[\'datasets\'][tooltipItem[\'datasetIndex\']][\'data\'][tooltipItem[\'index\']];';
$out .= '                },';
$out .= '            },';
$out .= '            titleFontSize: 15,';
$out .= '            bodyFontSize: 15,';
$out .= '            displayColors: true';
$out .= '        }';
$out .= '    }';
$out .= '});';
$out .= '</script>';

// Don't forget to output the $out variable somewhere
echo $out;

Key Changes Explained: #

• JSON Format Fix: We collect all time labels into $chartLabels array, then use json_encode($chartLabels) to generate a single valid JSON array (like ["11:00","09:00","01:00"]).
• Dynamic Data: We collect max and average user values into $maxUserValues and $avgUserValues, then inject them into the Chart.js data property using json_encode.
• Chronological Order: The array_reverse calls ensure the chart displays data from oldest to newest (remove them if you want latest entries first).
• Optional Dual Dataset: Added a second dataset for average users (you can remove this if you only want to show max users).

Notes: #

• Make sure $percent_inc is defined somewhere in your code (it's used in your original calculation).
• If you want the chart title to show the actual date from your data, you can modify the query to include the date and set it dynamically in the title.text property.

内容的提问来源于stack exchange，提问作者Alexsandro Siregar