Hey there, let's figure out why your bar chart isn't showing the sales and purchase values correctly. The issue likely lies in how you're processing your MySQL data, passing it via AJAX, or configuring Chart.js. Let's break this down step by step.

1. Fix Your PHP Data Handling (Critical!) #

First, your current PHP code cuts off the while loops—this means you're probably not properly populating the $totalpurchases and $totalsales arrays. Also, the mysql_* functions are deprecated—switch to mysqli or PDO to avoid runtime errors and security risks. Here's how to properly structure your PHP to return aligned, usable data:

<?php
// Connect to database using mysqli (replace with your credentials)
$conn = mysqli_connect("your_host", "your_user", "your_password", "your_db");

// Fetch purchase data, store by year
$purchaseData = [];
$sql = "SELECT YEAR(date) as year, SUM(amount) AS total FROM purchases GROUP BY YEAR(date)";
$result = mysqli_query($conn, $sql);
while ($row = mysqli_fetch_assoc($result)) {
    $purchaseData[$row['year']] = $row['total'];
}

// Fetch sales data, store by year
$salesData = [];
$sqll = "SELECT YEAR(date) as year, SUM(amount) AS total FROM sales GROUP BY YEAR(date)";
$resultSales = mysqli_query($conn, $sqll);
while ($row = mysqli_fetch_assoc($resultSales)) {
    $salesData[$row['year']] = $row['total'];
}

// Get all unique years from both datasets to align data (fill 0 for missing values)
$allYears = array_unique(array_merge(array_keys($purchaseData), array_keys($salesData)));
sort($allYears);

// Prepare final datasets for Chart.js
$years = [];
$purchaseTotals = [];
$salesTotals = [];
foreach ($allYears as $year) {
    $years[] = $year;
    $purchaseTotals[] = isset($purchaseData[$year]) ? $purchaseData[$year] : 0;
    $salesTotals[] = isset($salesData[$year]) ? $salesData[$year] : 0;
}

// Return valid JSON data (required for AJAX)
header('Content-Type: application/json');
echo json_encode([
    'years' => $years,
    'purchases' => $purchaseTotals,
    'sales' => $salesTotals
]);
?>

2. Correct AJAX Data Retrieval #

Make sure your AJAX request properly fetches and parses the JSON data before passing it to Chart.js. Here's a vanilla JavaScript example (jQuery works too):

// Wait for the page to load before initializing the chart
document.addEventListener('DOMContentLoaded', function() {
    fetch('your_php_file.php') // Replace with your PHP file path
        .then(response => response.json())
        .then(data => {
            initChart(data); // Pass fetched data to chart initialization
        })
        .catch(error => console.error('Error loading data:', error));
});

3. Proper Chart.js Configuration #

Now configure your bar chart to use the fetched data. If you want to display numerical values on top of the bars, you'll need the chartjs-plugin-datalabels plugin (add it via CDN: <script src="https://cdn.jsdelivr.net/npm/chartjs-plugin-datalabels@2.2.0/dist/chartjs-plugin-datalabels.min.js"></script>). Here's the full configuration:

function initChart(data) {
    const ctx = document.getElementById('mybarChart').getContext('2d');
    new Chart(ctx, {
        type: 'bar',
        data: {
            labels: data.years, // X-axis: years
            datasets: [
                {
                    label: 'Total Purchases',
                    data: data.purchases,
                    backgroundColor: 'rgba(255, 99, 132, 0.6)',
                    borderColor: 'rgba(255, 99, 132, 1)',
                    borderWidth: 1
                },
                {
                    label: 'Total Sales',
                    data: data.sales,
                    backgroundColor: 'rgba(54, 162, 235, 0.6)',
                    borderColor: 'rgba(54, 162, 235, 1)',
                    borderWidth: 1
                }
            ]
        },
        options: {
            responsive: true,
            scales: {
                y: {
                    beginAtZero: true,
                    title: {
                        display: true,
                        text: 'Total Amount'
                    }
                },
                x: {
                    title: {
                        display: true,
                        text: 'Year'
                    }
                }
            },
            // Display values on top of bars (requires the datalabels plugin)
            plugins: {
                datalabels: {
                    anchor: 'end',
                    align: 'top',
                    formatter: Math.round,
                    font: {
                        weight: 'bold'
                    }
                }
            }
        }
    });
}

Key Fixes to Note #

• Aligned Years: If some years have only sales or only purchases, we fill in 0 so Chart.js doesn't misalign the datasets.
• Valid JSON Response: Your PHP must return properly formatted JSON with the correct Content-Type header—otherwise AJAX can't parse the data.
• Deprecated MySQL Functions: Switching to mysqli avoids outdated code issues.
• Value Display: The chartjs-plugin-datalabels plugin is the simplest way to show values directly on bars.

If you're still stuck, check your browser's console for errors—they'll usually point to whether the problem is with data fetching, parsing, or Chart.js configuration.

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