top of page
Sep 3, 2023

Part 4 JS Chart : JS Charts with PHP Database

Below are the codes used in this tutorial :


Connecttodb.php

<?php
$servername = "localhost";
$username = "root";
$password = "XXXXX";
$dbname = "testingdatabase";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

#echo "Connected to SQL successfully";

?>

Barchart


<!DOCTYPE html>
<html>
<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.5.0/Chart.min.js"></script>
<body>

<canvas id="myChart" style="width:100%;max-width:600px"></canvas>

<?php

  include 'connecttodb.php';
  $sql = "SELECT Year as monthname, Sales as amount FROM operation;";
  $query = $conn->query($sql);
  
  $minval = $conn->query("SELECT MIN(Sales) FROM operation;");
  $minval = mysqli_fetch_array($minval);
  print_r($minval[0]-100);
  
  foreach($query as $data)
  {
    $month[] = $data['monthname'];
    $amount[] = $data['amount'];
  }

  $numberofmonths = count($month);
  $numberofamounts = count($amount);
  
  //for($y=0;$y<$numberofmonths;$y++){
    //echo json_encode($month[$y]).",";
    //}

    //for($x=0;$x<$numberofamounts;$x++){
    //    echo json_encode($amount[$x]).",";
    //}

?>

<script>
//var xValues = [<?php echo json_encode($month[0]) ?>,<?php echo json_encode($month[1]) ?>,<?php echo json_encode($month[2]) ?>,<?php echo json_encode($month[3]) ?>];
//var yValues = [<?php echo json_encode($amount[0]) ?>,<?php echo json_encode($amount[1]) ?>,<?php echo json_encode($amount[2]) ?>,<?php echo json_encode($amount[3]) ?>,800];

var xValues = [<?php for($y=0;$y<$numberofmonths;$y++){echo json_encode($month[$y]).",";} ?>];
var yValues = [<?php for($x=0;$x<$numberofamounts;$x++){echo json_encode($amount[$x]).",";}?>, <?php echo json_encode($minval[0]-100) ?>];

var barColors = ["red", "green","blue","orange","brown"];

new Chart("myChart", {
  type: "bar",
  data: {
    labels: xValues,
    datasets: [{
      
      backgroundColor: ["rgba(37,150,190,0.5)","rgba(255,0,0,0.5)","rgba(0,255,0,0.5)","rgba(0,0,255,0.5)","rgba(0,255,255,0.5)"],
      hoverBackgroundColor: ["rgba(37,150,190,0.7)","rgba(255,0,0,0.7)","rgba(0,255,0,0.7)","rgba(0,0,255,0.7)","rgba(0,255,255,0.7)"],

      borderColor : ["rgba(37,150,190,0.8)","rgba(255,0,0,0.8)","rgba(0,255,0,0.8)","rgba(0,0,255,0.8)","rgba(0,255,255,0.8)"],
      hoverBorderColor : ["rgb(0,0,0)","rgb(0,0,0)","rgb(0,0,0)","rgb(0,0,0)","rgb(0,0,0)"],

      borderWidth : 2,
      hoverBorderWidth : 0.5,

      data: yValues
    }]
  },
  options: {
    legend: {display: false},
    title: {
      display: true,
      text: "World Wine Production 2018"
    }
  }
});
</script>

</body>
</html>

LineChart


<!DOCTYPE html>
<html lang="en">
<head>
  <title>JS Line Chart</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/css/bootstrap.min.css">
  <script src="https://cdn.jsdelivr.net/npm/jquery@3.6.0/dist/jquery.slim.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/js/bootstrap.bundle.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.5.0/Chart.min.js"></script>
  <link rel="stylesheet" type="text/css" href="customtable2.css">
  <!--
    
  -->

  <div class="container-fluid p-3 mt-3 bg-dark bg-gradient text-white">
    <h1>JS Charts</h1>
  </div>
  
</head>

<body>

<div class="row">

  <div class="container col-6 mt-3">
  <canvas id="mychart3"></canvas>
        <?php
        include 'connecttodb.php';
        $sql1 = "SELECT Year as monthname, Sales as amount FROM operation;";
        $query1 = $conn->query($sql1);
        
        $minval1 = $conn->query("SELECT MIN(Sales) FROM operation;");
        $minval1 = mysqli_fetch_array($minval1);
        print_r($minval1[0]-100);
        
        foreach($query1 as $data1)
        {
            $month2[] = $data1['monthname'];
            $amount2[] = $data1['amount'];
        }

        $numberofmonths2 = count($month2);
        $numberofamounts2 = count($amount2);

        ?>

        <script>
          
        var xValues = [<?php for($y=0;$y<$numberofmonths2;$y++){echo json_encode($month2[$y]).",";} ?>];
        var yValues = [<?php for($x=0;$x<$numberofamounts2;$x++){echo json_encode($amount2[$x]).",";}?>, <?php echo json_encode($minval1[0]-100) ?>];

        new Chart("mychart3", {
        type: "line",
        data: {
            labels: xValues,
            datasets: [{

            //pointStyle : 'crossRot',
            //pointStyle : 'circle',
            pointStyle : 'triangle',

            backgroundColor: "rgba(0,0,255,0.1)",
            borderColor : "rgba(0,0,255,0.3)",

            pointBorderColor : "rgba(0,0,255)",
            pointHoverBorderColor : "rgba(255,255,0)",
            
            pointBackgroundColor : "rgba(0,0,100)",
            //pointBorderWidth : "5",
            pointHoverBorderWidth : "2",

            pointHoverRadius : "6",
            pointRadius : "4",
            

            data: yValues
            }]
        },
        options: {
            legend: {display: false},
            title: {
            display: true,
            text: "World Wine Production 2018"
            }
        }

        });
        </script>    
  
    </div>

  <div class="container col-6 mt-3">
      <canvas id="mychart4"></canvas>
            <?php
            include 'connecttodb.php';
            $sql1 = "SELECT Year as monthname, Sales as amount, Expense FROM operation;";
            $query1 = $conn->query($sql1);

            $minval1 = $conn->query("SELECT MIN(Sales) FROM operation;");
            $minval1 = mysqli_fetch_array($minval1);
            print_r($minval1[0]-100);
            
            foreach($query1 as $data1)
            {
                $month3[] = $data1['monthname'];
                $amount3[] = $data1['amount'];
                $expense1[] = $data1['Expense'];
            }

            $numberofmonths3 = count($month3);
            $numberofamounts3 = count($amount3);
            $numberofexpense = count($expense1);

            ?>

            <script>
              
            var xValues = [<?php for($y=0;$y<$numberofmonths3;$y++){echo json_encode($month3[$y]).",";} ?>];
            var yValues = [<?php for($x=0;$x<$numberofamounts3;$x++){echo json_encode($amount3[$x]).",";}?>, <?php echo json_encode($minval1[0]-100) ?>];
            var zValues = [<?php for($z=0;$z<$numberofexpense;$z++){echo json_encode($expense1[$z]).",";}?>, <?php echo json_encode($minval1[0]-100) ?>];

            new Chart("mychart4", {
            type: "line",
            data: {
                labels: xValues,
                datasets: [{

                //pointStyle : 'crossRot',
                //pointStyle : 'circle',
                pointStyle : 'triangle',

                backgroundColor: "rgba(0,0,255,0.1)",
                borderColor : "rgba(0,0,255,0.3)",

                pointBorderColor : "rgba(0,0,255)",
                pointHoverBorderColor : "rgba(255,255,0)",
                
                pointBackgroundColor : "rgba(0,0,100)",
                //pointBorderWidth : "5",
                pointHoverBorderWidth : "2",

                pointHoverRadius : "6",
                pointRadius : "4",
                

                data: yValues},
                {
                  data: zValues,
                  borderColor: "green",
                  backgroundColor: "rgba(0,255,0,0.1)",
                  
                },
                {
                  data: [1200,900,1000,800],
                  borderColor: "red",
                  backgroundColor: "rgba(255,0,0,0.1)",
                },
              ]
            },
            options: {
                legend: {display: false},
                title: {
                display: true,
                text: "World Wine Production 2018"
                }
            }

            });
            </script> 
  </div>

</div>

</body>
</html>

Pie and Doughnut Chart

<!DOCTYPE html>
<html lang="en">
<head>
  <title>JS Line Chart</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/css/bootstrap.min.css">
  <script src="https://cdn.jsdelivr.net/npm/jquery@3.6.0/dist/jquery.slim.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js"></script>
  <script src="https://cdn.jsdelivr.net/npm/bootstrap@4.6.1/dist/js/bootstrap.bundle.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.5.0/Chart.min.js"></script>
  <!--
    
  -->

  <div class="container-fluid p-3 mt-3 bg-dark bg-gradient text-white">
    <h1>JS Charts</h1>
  </div>
  
</head>

<body>

<div class="row">

  <div class="container col-6 mt-3">
  <canvas id="mychart3"></canvas>
        <?php
        include 'connecttodb.php';
        $sql1 = "SELECT Year as monthname, Sales as amount FROM operation;";
        $query1 = $conn->query($sql1);
        
        $minval1 = $conn->query("SELECT MIN(Sales) FROM operation;");
        $minval1 = mysqli_fetch_array($minval1);
        print_r($minval1[0]-100);
        
        foreach($query1 as $data1)
        {
            $month2[] = $data1['monthname'];
            $amount2[] = $data1['amount'];
        }

        $numberofmonths2 = count($month2);
        $numberofamounts2 = count($amount2);

        ?>

        <script>
          
        var xValues = [<?php for($y=0;$y<$numberofmonths2;$y++){echo json_encode($month2[$y]).",";} ?>];
        var yValues = [<?php for($x=0;$x<$numberofamounts2;$x++){echo json_encode($amount2[$x]).",";}?>];

        new Chart("mychart3", {
        type: "pie",
        data: {
            labels: xValues,
            datasets: [{

            backgroundColor: ["rgba(120,0,0,0.5)","rgba(0,120,0,0.5)","rgba(0,0,120,0.5)","rgba(120,120,0,0.5)"],

            hoverBackgroundColor: ["rgba(120,0,0,0.8)","rgba(0,120,0,0.8)","rgba(0,0,120,0.8)","rgba(120,120,0,0.8)"],

            hoverBorderColor: ["rgba(120,0,0,1)","rgba(0,120,0,1)","rgba(0,0,120,1)","rgba(120,120,0,1)"],

            hoverBorderWidth:'5',

            borderColor : "#FFFFFF",
            borderWidth : '2',

            data: yValues
            }]
        },
        options: {
            title: {
            display: true,
            text: "World Wine Production 2018"
            }
        }

        });
        </script>    
  
    </div>

  <div class="container col-6 mt-3">
      <canvas id="mychart4"></canvas>
            <?php
            include 'connecttodb.php';
            $sql1 = "SELECT Year as monthname, Sales as amount, Expense FROM operation;";
            $query1 = $conn->query($sql1);

            $minval1 = $conn->query("SELECT MIN(Sales) FROM operation;");
            $minval1 = mysqli_fetch_array($minval1);
            print_r($minval1[0]-100);
            
            foreach($query1 as $data1)
            {
                $month3[] = $data1['monthname'];
                $amount3[] = $data1['amount'];
                $expense1[] = $data1['Expense'];
            }

            $numberofmonths3 = count($month3);
            $numberofamounts3 = count($amount3);
            $numberofexpense = count($expense1);

            ?>

            <script>
              
            var xValues = [<?php for($y=0;$y<$numberofmonths3;$y++){echo json_encode($month3[$y]).",";} ?>];
            var yValues = [<?php for($x=0;$x<$numberofamounts3;$x++){echo json_encode($amount3[$x]).",";}?>];
            var barColors = ["#b91d47","#00aba9","#2b5797","#e8c3b9","#1e7145"];

            new Chart("mychart4", {
            type: "doughnut",
            data: {
                labels: xValues,
                datasets: [{

                backgroundColor: ["rgba(120,0,0,0.5)","rgba(0,120,0,0.5)","rgba(0,0,120,0.5)","rgba(120,120,0,0.5)"],

                hoverBackgroundColor: ["rgba(120,0,0,0.8)","rgba(0,120,0,0.8)","rgba(0,0,120,0.8)","rgba(120,120,0,0.8)"],

                hoverBorderColor: ["rgba(120,0,0,1)","rgba(0,120,0,1)","rgba(0,0,120,1)","rgba(120,120,0,1)"],

                hoverBorderWidth:'5',

                borderColor : "#FFFFFF",
                borderWidth : '2',

                data: yValues},
              ]
            },
            options: {
                title: {
                display: true,
                text: "World Wine Production 2018"
                }
            }

            });
            </script> 
  </div>

</div>

<div class="row">

</div>

</body>
</html>

For more detailed instruction, feel free to check out my YouTube video :


Comments

bottom of page