チャート.初心者のためのMySQLとPHP

13603 ワード
webdev beginners javascript テキストリンク
あなたがWebdevに新しくて、どのようにチャートとグラフをあなたのウェブページに組み込むかを学びたいならば、この記事はあなたのためです.
Northwindデータベースはこの記事で使用されます.このファイルには、SQL 2466があります.
次のファイルとコードを設定します.以下のコードでMySQLに接続できます.
設定/設定.PHP
<?php
    define('DB_HOST', 'localhost');
    define('DB_USER', 'root');
    define('DB_PASS', 'yourpassword');
    define('DB_NAME', 'northwind');
?>
config/dbPHP
<?php
    // Create Connection
    $conn = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);

    // Check Connection
    if(mysqli_connect_errno()){
        // Connection Failed
        echo 'Failed to connect to MySQL '. mysqli_connect_errno();
    }
?>
設定ディレクトリを設定した後、今あなたのグラフを表示するWebページ上で動作することができます.
チャートNo . 1:円グラフ
repository
ヘッドタグの中にchartjsスクリプトを加えてください.
<head>
<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/3.7.1/chart.min.js"></script>
</head>
あなたがチャートを表示したいキャンバスタグを準備します.
<canvas id="chartShippers"></canvas>
キャンバスタグの下に、我々のチャートに必要なデータを取得するPHPコードを書きましょう.
<?php
require('config/config.php');
require('config/db.php');
$query01 = "SELECT shippers.CompanyName, (Count(*)/(SELECT COUNT(*) FROM northwind.orders) * 100) as Count_Orders FROM northwind.orders, northwind.shippers WHERE shippers.ShipperID=orders.ShipVia GROUP BY ShipVia;";

// Get the result
$result01 = mysqli_query($conn, $query01);

// Put the result in array
if(mysqli_num_rows($result01) > 0){
    // array for data
    $Count_Orders = array();
    // array for labels (x-axis)
    $label_piechart = array();
    while ($row = mysqli_fetch_array($result01)){
        $Count_Orders[] = $row['Count_Orders'];
        $label_piechart[] = $row['CompanyName'];
    }

    // Free result
    mysqli_free_result($result01);
    // Close the connection
    mysqli_close($conn);
}else{
    echo "No records matching your query were found.";
}

?>
PHPコードの下にスクリプトタグを作成します.スクリプト内の3つのブロック、セットアップ、設定、およびレンダリングブロックがあります.
<script>
// <!-- setup block -->
const Count_Orders = <?php echo json_encode($Count_Orders); ?>;
const label_piechart = <?php echo json_encode($label_piechart); ?>;
const data1 = {
    labels: label_piechart,
    datasets: [{
        label: 'My First Dataset',
        data: Count_Orders,
        backgroundColor: [
        'rgb(255, 99, 132)',
        'rgb(54, 162, 235)',
        'rgb(255,165,0)'
        ],
        hoverOffset: 4
    }]
    };
// <!-- config block -->
const config = {
    type: 'pie',
    data: data1,
};

// <!-- render block -->
const chartShippers = new Chart(
    document.getElementById('chartShippers'),
    config
);

</script>
チャートNo . 2ラインチャート

キャンバスタグを準備します.
<canvas id="chartTop3"></canvas>
トップ3の注文された製品を取得するPHPコード.
<?php
    require('config/config.php');
    require('config/db.php');
    $query_top3 = "SELECT ProductName FROM northwind.order_details, northwind.products 
    WHERE products.ProductID=order_details.ProductID 
    GROUP BY products.ProductID order by count(*) desc, products.ProductName limit 3";

    $result_top3 = mysqli_query($conn, $query_top3);
    $products_top3 = array();
    while ($row = mysqli_fetch_array($result_top3))
    {
        $products_top3[] = $row['ProductName'];
    }

    // Free result
    mysqli_free_result($result_top3);

    $Top1_Count = array_fill(0,12,0);
    $Top2_Count = array_fill(0,12,0);
    $Top3_Count = array_fill(0,12,0);

    for ($counter=0; $counter<3; $counter++)
    {
        $query02 = "SELECT EXTRACT(MONTH FROM o.orderdate) as Month_1997, p.ProductName, COUNT(*) as num_order
            FROM northwind.order_details od, northwind.orders o, northwind.products p
            WHERE o.orderid = od.orderid and p.productid = od.ProductID and o.orderdate LIKE '1997%' and 
            p.ProductName = '" . $products_top3[$counter] .
            "' GROUP BY p.ProductName, Month_1997 
            ORDER BY Month_1997, p.ProductName;";

            $result02 = mysqli_query($conn, $query02);

            if(mysqli_num_rows($result02) > 0){

                    while ($row = mysqli_fetch_array($result02)){
                        if ($counter==0){
                            $Top1_Count[$row['Month_1997']] = $row['num_order'];
                        } elseif ($counter==1){
                            $Top2_Count[$row['Month_1997']] = $row['num_order'];
                        } else {
                            $Top3_Count[$row['Month_1997']] = $row['num_order'];
                        }
                    }
            }
    }


?>
行グラフ用スクリプト
<script>
    // <!-- setup block -->

    const Top1_Count = <?php echo json_encode($Top1_Count); ?>;
    const Top2_Count = <?php echo json_encode($Top2_Count); ?>;
    const Top3_Count = <?php echo json_encode($Top3_Count); ?>;
    const label_1 = <?php echo json_encode($products_top3[0]); ?>;
    const label_2 = <?php echo json_encode($products_top3[1]); ?>;
    const label_3 = <?php echo json_encode($products_top3[2]); ?>;
    const data2 = {
    labels: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
    datasets: [{
        label: label_1,
        data: Top1_Count, //[65, 59, 80, 81, 56, 55, 40, 80, 81, 56, 55, 40],
        fill: false,
        backgroundColor: 'rgb(255, 99, 132)',
        borderColor: 'rgb(255, 99, 132)',
        tension: 0.1
        },
        {
        label: label_2,
        data: Top2_Count, //[65, 59, 80, 55, 56, 55, 40, 80, 34, 56, 55, 40],
        fill: false,
        backgroundColor: 'rgb(54, 162, 235)',
        borderColor: 'rgb(54, 162, 235)',
        tension: 0.1
        },
        {
        label: label_3,
        data: Top3_Count, //[65, 45, 80, 81, 90, 55, 23, 80, 81, 56, 55, 40],
        fill: false,
        backgroundColor: 'rgb(255,165,0)',
        borderColor: 'rgb(255,165,0)',
        tension: 0.1
        }]
    };                      
    // <!-- config block -->
    const config2 = {
    type: 'line',
    data: data2,
    };
    // <!-- render block -->
    const chartTop3 = new Chart(
    document.getElementById('chartTop3'),
    config2
);
</script>
チャートNo . 3棒グラフ( 2データセット)

チャートの準備キャンバス.
<canvas id="chartMeatvsSeafood"></canvas>
肉/家禽類と魚介類カテゴリーの下で製品の月につき注文の数を取り戻すPHPコード.
<?php 
    require('config/config.php');
    require('config/db.php');

    $query03 = "SELECT EXTRACT(MONTH FROM o.orderdate) as Month_1997, cat.CategoryName as CategoryName, SUM(od.UnitPrice*od.Quantity*(1-od.Discount)) as sales
        FROM northwind.order_details od, northwind.orders o, northwind.products p, northwind.categories cat
        WHERE o.orderid = od.orderid and p.productid = od.ProductID AND p.CategoryID=cat.CategoryID and 
        cat.CategoryName in('Meat/Poultry','Seafood') and o.orderdate LIKE '1997%'
        GROUP BY cat.CategoryName, Month_1997 
        ORDER BY Month_1997, cat.CategoryName;";

    // Get the result
    $result03 = mysqli_query($conn, $query03);

    // Put the result in array
    if(mysqli_num_rows($result03) > 0){
        $Sales_Meat = array();
        $Sales_Seafood = array();
        while ($row = mysqli_fetch_array($result03)){
            if($row['CategoryName']=='Seafood'){
                $Sales_Seafood[] = $row['sales'];
            }else{
                $Sales_Meat[] = $row['sales'];
            }

        }
            // print_r($Sales_Seafood);
            // print_r($Sales_Meat);
        // Free result
        mysqli_free_result($result03);
        // Close the connection
        mysqli_close($conn);
    }else{
        echo "No records matching your query were found.";
    }

?>
棒グラフ用スクリプト
<script>
// <!-- setup block -->
const Sales_Meat = <?php echo json_encode($Sales_Meat); ?>;
const Sales_Seafood = <?php echo json_encode($Sales_Seafood); ?>;

const data3 ={
    labels: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
        datasets: [{
            label: 'Meat/Poultry',
            data: Sales_Meat,
            backgroundColor: [
                'rgb(255, 99, 132)'

            ],
            borderColor: [
                'rgb(255, 99, 132)'

            ],
            borderWidth: 1
        },
        {
            label: 'SeaFood',
            data: Sales_Seafood,
            backgroundColor: [
                'rgb(54, 162, 235)'
            ],
            borderColor: [
                'rgb(54, 162, 235)'
            ],
            borderWidth: 1
        }]
};

// <!-- config block -->
const config3 = {
    type: 'bar',
    data: data3,
    options: {
        scales: {
            y: {
                beginAtZero: true
            }
        }
    }
};

// <!-- render block -->
const chartMeatvsSeafood = new Chart(
    document.getElementById('chartMeatvsSeafood'),
    config3
);
</script>
チャートNo . 4バーチャート

準備キャンバス.
<canvas id="myChartTopFive"></canvas>
トップ5の注文された製品のために毎月注文の数をフェッチするPHPコード.
<?php
require('config/config.php');
require('config/db.php');
$query04 = "SELECT ProductName, count(*) as order_count FROM northwind.order_details, northwind.products WHERE products.ProductID=order_details.ProductID GROUP BY products.ProductID order by order_count desc, products.Productname limit 5;";

// Get the result
$result04 = mysqli_query($conn, $query04);

// Put the result in array
if(mysqli_num_rows($result04) > 0){
    $order_count = array();
    $label_barchart = array();
    while ($row = mysqli_fetch_array($result04)){
        $order_count[] = $row['order_count'];
        $label_barchart[] = $row['ProductName'];
    }
    // print_r($label_barchart);    
    // Free result
    mysqli_free_result($result04);

    // Close the connection
    mysqli_close($conn);
}else{
    echo "No records matching your query were found.";
}

?>
棒グラフ用スクリプト
<script>
// <!-- setup block -->

const order_count = <?php echo json_encode($order_count); ?>;
const label_barchart = <?php echo json_encode($label_barchart); ?>;

const data4 ={
labels: label_barchart, //['Red', 'Blue', 'Yellow', 'Green', 'Purple'],
        datasets: [{
            label: 'Number of Orders',
            data: order_count,
            backgroundColor: [
                'rgba(255, 99, 132, 0.2)',
                'rgba(54, 162, 235, 0.2)',
                'rgba(255, 206, 86, 0.2)',
                'rgba(75, 192, 192, 0.2)',
                'rgba(153, 102, 255, 0.2)',
                'rgba(255, 159, 64, 0.2)'
            ],
            borderColor: [
                'rgba(255, 99, 132, 1)',
                'rgba(54, 162, 235, 1)',
                'rgba(255, 206, 86, 1)',
                'rgba(75, 192, 192, 1)',
                'rgba(153, 102, 255, 1)',
                'rgba(255, 159, 64, 1)'
            ],
            borderWidth: 1
}]
};

// <!-- config block -->
const config4 = {
    type: 'bar',
    data: data4,
    options: {
        scales: {
            y: {
                beginAtZero: true
            }
        }
    }
};

// <!-- render block -->
const myChartTopFive = new Chart(
    document.getElementById('myChartTopFive'),
    config4
);

</script>
角度応用におけるOpenTelemmetryの実装
Activityで取得したviewの高さと幅が0の理由と解決策