Databases_Project/Query10.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
134
135
<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>GDP vs. Percent of top songs with 'money' in each year for each country</h2>";
//Determine if any input was actually collected
if ($stmt = $conn->prepare(
"WITH
TopSongsPerCountry AS (SELECT country, songID, YEAR(startDate) as year
                        FROM SpotifyChart),
NumSongsPerCountry AS (SELECT COUNT(DISTINCT songID) as num_songs, country, year
                        FROM TopSongsPerCountry
                        GROUP BY country, year),
SongsWithMoney AS (SELECT Song.songID, country
                    FROM Song JOIN TopSongsPerCountry ON Song.songID = TopSongsPerCountry.songID
                    WHERE LOWER(lyrics) LIKE '% money %'),
MoneySongsPerCountry AS (
                        SELECT SongsWithMoney.country, COUNT(DISTINCT songID) as num_money_songs, num_songs, year
                        FROM SongsWithMoney JOIN NumSongsPerCountry ON SongsWithMoney.country = NumSongsPerCountry.country
                        GROUP BY country, year)
SELECT M.country, GDP, M.year, ROUND(100 * num_money_songs / num_songs) AS PercentAboutMoney
                    FROM MoneySongsPerCountry as M JOIN CountryHappiness as C
                    ON M.country = C.country and M.year = C.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> GDP </th> <th> Year </th> <th> Percent of Top Songs About Money </th></tr>";
	    while ($row = $result->fetch_row()) {
		array_push($dataVals, array( "label"=> $row[1], "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 "<td>".$row[$fname->name]."</td>";
             }

           echo "</table>";

        }      else {
        //if ($result->num_rows == 0) {
           //Result contains no rows at all
           echo "Insufficient information to determine top songs with 'money' in lyrics";

            }

        //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 "Query 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: "GDP vs. Percent of top songs about money"
		},
      axisX:{
	    title: "GDP",
	    interval: .2
      },
      axisY:{
         interlacedColor: "rgba(1,77,101,.2)",
         gridColor: "rgba(1,77,101,.1)",
         title: "% Songs about Money"
      },
		data: [{
         type: "scatter",
         name: "countries",
         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>