PHP从SQL取数生成适配JavaScript图表的JSON格式问题

Hey there! Let's break this down step by step to help you get the perfect data format for your charts.

First off: the format you wrote ({ Player: 3, Game: 'Diablo 3' }, { Player: 1, Game: 'Overwatch' }) isn't strictly valid JSON. JSON has two non-negotiable rules that this violates:

• All object keys must be wrapped in double quotes (no unquoted keys or single quotes)
• String values must also use double quotes
• Plus, multiple objects need to be wrapped in an array ([]) to form a valid JSON structure

The correct, standards-compliant JSON version of your desired format looks like this:

[{"Player": 3, "Game": "Diablo 3"}, {"Player": 1, "Game": "Overwatch"}]

This is the format most JavaScript chart libraries expect (like Chart.js, D3.js, etc.).

That output means your PHP code is creating a simple indexed array that only stores player count values as strings. For example, your code probably looks something like this:

$playerCounts = [];
while ($row = $result->fetch_assoc()) {
    // You're only grabbing the player count, no game name or key mappings
    $playerCounts[] = $row['player_count'];
}
echo json_encode($playerCounts);

json_encode converts this plain indexed array directly into a JSON array of strings.

To get the structured object array your chart needs, you'll need to build an array of associative arrays in PHP first, then encode it to JSON. Here's how:

Step 1: Fetch the right data from your SQL database #

Make sure your query pulls both the game name and player count fields (adjust the column and table names to match your database):

// Assume $conn is your established database connection
$query = "SELECT game_name, player_count FROM your_game_stats_table";
$result = $conn->query($query);

Step 2: Build the structured PHP array #

Loop through your query results and create a nested associative array for each game-player pair:

$chartData = [];
while ($row = $result->fetch_assoc()) {
    $chartData[] = [
        'Player' => (int)$row['player_count'], // Cast to integer to avoid string values
        'Game' => $row['game_name']
    ];
}

Step 3: Encode to valid JSON #

Now pass this structured array to json_encode—it will output the exact format you need:

$jsonData = json_encode($chartData);
echo $jsonData;

The output will be the valid JSON object array we showed earlier, ready to use in your JavaScript chart.

You can either fetch this JSON via AJAX, or directly inject it into your frontend code:

<script>
// Inject the JSON directly into your JS
const chartData = <?php echo $jsonData; ?>;

// Most chart libraries will accept this array directly
// Example with Chart.js:
new Chart(document.getElementById('myChart'), {
    type: 'bar',
    data: {
        labels: chartData.map(item => item.Game),
        datasets: [{
            label: 'Number of Players',
            data: chartData.map(item => item.Player)
        }]
    }
});
</script>

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