如何将MySQL数据库数据导入散点图？附折线图代码示例

Hey there! I see you've got a working line chart pulling data from MySQL, and now you want to switch it to a scatter plot. The good news is it's mostly about adjusting how you structure your data and tweaking the Chart.js configuration—let's break this down step by step.

Key Difference Between Line and Scatter Charts in Chart.js #

Line charts use separate labels (for the x-axis) and a flat data array (for y-values). Scatter charts, however, require each data point to be an object with x and y properties, since they plot independent numerical values on both axes.

Step 1: Update Your PHP Data Fetching #

Instead of creating two separate arrays ($json for timestamps and $json2 for F1M values), we'll build a single array of objects where each object holds the x (timestamp) and y (F1M) values for a scatter point.

Here's the modified PHP section:

<?php
$dbhost='localhost';
$dbname='graph_plot';
$dbuser='root';
$dbpass='';

try{
    $dbcon= new PDO("mysql:host={$dbhost};dbname={$dbname}",$dbuser,$dbpass);
    $dbcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $ex){
    die($ex->getMessage());
}

$stmt=$dbcon->prepare("SELECT * FROM graph_plot");
$stmt->execute();

// Create a single array of {x, y} objects for scatter points
$scatterData = [];
while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
    // Convert datetime to Unix timestamp (numeric x-value) for easier time scale handling
    $scatterData[] = [
        'x' => strtotime($row['timestamp']),
        'y' => (float)$row['F1M']
    ];
}
?>

Note: If your timestamp column is already a numeric Unix timestamp, you can skip the strtotime() conversion. If you want to keep it as a readable string, adjust the x-axis to use a category scale instead of time.

Step 2: Update the Chart.js Configuration #

Now we'll adjust the HTML/JS part to use the scatter chart type, remove the labels array, and configure the axes to handle time values properly.

Here's the modified HTML/JS section:

<!DOCTYPE html>
<html>
<head>
    <title>Scatter Chart with MySQL Data</title>
</head>
<body>
    <form action="http://localhost/graph_plot/index.php">
        <button type="submit">Check for F1F</button>
    </form>
    <form action="http://localhost/graph_plot/drop.html">
        <button type="submit">Home</button>
    </form>
    <canvas id="myChart"></canvas>

    <script src="https://cdn.jsdelivr.net/npm/chart.js@2.8.0"></script>
    <script type="text/javascript">
        var ctx = document.getElementById('myChart').getContext('2d');
        var chart = new Chart(ctx, {
            type: 'scatter', // Switch to scatter chart type
            data: {
                datasets: [{
                    label: 'F1M',
                    backgroundColor: 'rgb(255, 99, 132)', // Fill color for scatter points
                    borderColor: 'rgb(255, 99, 132)',
                    pointRadius: 5, // Adjust point size to your preference
                    data: <?php echo json_encode($scatterData);?> // Pass our combined x/y data array
                }]
            },
            options: {
                scales: {
                    xAxes: [{
                        type: 'time', // Use time scale for datetime x-axis
                        time: {
                            unit: 'second', // Match your timestamp granularity (minute, hour, day, etc.)
                            displayFormats: {
                                second: 'HH:mm:ss' // Human-readable x-axis label format
                            }
                        },
                        scaleLabel: {
                            display: true,
                            labelString: 'Timestamp'
                        }
                    }],
                    yAxes: [{
                        scaleLabel: {
                            display: true,
                            labelString: 'F1M Value'
                        }
                    }]
                }
            }
        });
    </script>
</body>
</html>

What Changed? #

• Chart Type: Swapped type: 'line' to type: 'scatter'
• Data Structure: Removed the labels array and passed our $scatterData (array of {x,y} objects) directly to the dataset's data property
• Axis Configuration: Added a time scale for the x-axis to properly display and format timestamp values (tweak unit and displayFormats to match your data's time granularity)
• Point Styling: Added pointRadius to control scatter point size—feel free to add other styles like pointHoverRadius for interactivity

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