Databases_Project/Query9.php at main · tkarani1/Databases_Project · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
<head><title></title></head>
<body>

<?php
include 'open.php';

//Override the PHP configuration file to display all errors
//This is useful during development but generally disabled before release
ini_set('error_reporting', E_ALL);
ini_set('display_errors', true);

$dataVals = array();

//Collect the posted value in a variable called $item

echo "<h2>Happiness Score and Danceability in each Country</h2>";
//Determine if any input was actually collected
if ($stmt = $conn->prepare(
    "WITH
    Top10SongPerCountry AS (SELECT DISTINCT country, YEAR(startDate) as year, songID
                        FROM SpotifyChart
                        WHERE pos <= 25),
    TopSongDance AS (SELECT danceability, year, country, songName, Song.songID
                    FROM Top10SongPerCountry JOIN Song ON Top10SongPerCountry.songID = Song.songID),
    AvgDancePerCountry AS (SELECT  country, year, ROUND(avg(danceability), 2) as AverageDanceability
                        FROM TopSongDance
                        GROUP BY country, year)
    SELECT CH.country, CH.year, happinessScore, AverageDanceability
    FROM CountryHappiness as CH JOIN AvgDancePerCountry as DC
    ON CH.country = DC.country and CH.year = DC.year; ")) {
       //Attach the ? in prepared statements to variables (even if those variables
       //don't hold the values we want yet).  First parameter is a list of types of
       //the variables that follow: 's' means string, 'i' means integer, 'd' means
       //double. E.g., for a statment with 3 ?'s, where middle parameter is an integer
       //and the other two are strings, the first argument included should be "sis".

       //Run the actual query
       if ($stmt->execute()) {

        //Store result set generated by the prepared statement
        $result = $stmt->get_result();

        if (($result) && ($result->num_rows != 0)) {

           //Create table to display results
           echo "<table border=\"1px solid black\">";
           echo "<tr><th> Country </th> <th> Year </th> <th> Happiness Score </th> <th> Danceability </th></tr>";
            while ($row = $result->fetch_row()) {
                array_push($dataVals, array( "label"=> $row[2], "y"=> $row[3]));
                echo "<tr>";
                echo "<td>".$row[0]."</td>";
                echo "<td>".$row[1]."</td>";
                echo "<td>".$row[2]."</td>";
                echo "<td>".$row[3]."</td>";
                echo "</tr>";
             }


           echo "</table>";

        }      else {
        //if ($result->num_rows == 0) {
           //Result contains no rows at all
           echo "Insufficient information to determine how danceability is affected by happiness score";

            }

        //We are done with the result set returned above, so free it
        $result->free_result();

     } else {

        //Call to execute failed, e.g. because server is no longer reachable,
        //or because supplied values are of the wrong type
        echo "Execute failed.<br>";
     }

     //Close down the prepared statement
     $stmt->close();

  } else {

      //A problem occurred when preparing the statement; check for syntax errors
      //and misspelled attribute names in the statement string.
     echo "Prepare failed.<br>";
     $error = $conn->errno . ' ' . $conn->error;
     echo $error;
  }


//Close the connection created in open.php
$conn->close();
?>

<html>
<head>
<script>
window.onload = function () {
        var chart = new CanvasJS.Chart("chartContainer", {
                animationEnabled: true,
                exportEnabled: true,
                theme: "light2", // "light1", "light2", "dark1", "dark2"
                title:{
                        text: "Happiness Score vs. Danceability"
                },
      axisX:{
            title: "Happiness Score",
            interval: .2
      },
      axisY:{
         interlacedColor: "rgba(1,77,101,.2)",
         gridColor: "rgba(1,77,101,.1)",
         title: "Danceability"
      },
                data: [{
         type: "scatter",
         name: "state",
         axisYType: "primary",
         color: "#014D65",
                        dataPoints: <?php echo json_encode($dataVals, JSON_NUMERIC_CHECK); ?>
                }]
        });
        chart.render();
}
</script>
</head>
<body>
   <br><br>
        <div id="chartContainer" style="height: 400px; width: 100%;"></div>
        <script src="https://canvasjs.com/assets/script/canvasjs.min.js"></script>
</body>
</html>