Databases_Project/Query12.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
136
137
138
139
140
141
142
143
144
145
146
147
148
<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
$this_year = $_POST['this_year'];

echo "<h2>Min number of followers to chart in a country in a year</h2>";
//Determine if any input was actually collected
if ($stmt = $conn->prepare(
" WITH
    ArtistSong AS  (SELECT Artist.artistID, artistName, followers, songID
                    FROM Artist JOIN SongReleases ON Artist.artistID = SongReleases.artistID),
    ArtistOnChart AS (SELECT artistID, followers, ArtistSong.songID, country, YEAR(startDate) as year
                        FROM  ArtistSong JOIN SpotifyChart ON  ArtistSong.songID =  SpotifyChart.songID
                        WHERE YEAR(startDate) = ? and YEAR(endDate) = ?)
    SELECT DISTINCT ArtistOnChart.country, min_artist
    FROM ArtistOnChart  JOIN ( SELECT country, min(followers) AS min_artist
                                FROM ArtistOnChart group by country) AS findmin
     ON (ArtistOnChart.country= findmin.country AND ArtistOnChart.followers = findmin.min_artist);

")) {
       //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
       $stmt->bind_param("ss", $this_year, $this_year);

       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> Minimum Number of Followers </th></tr>";
	while ($row = $result->fetch_row()) {
                array_push($dataVals, array( "label"=> $row[0], "y"=> $row[1]));
                echo "<tr>";
                echo "<td>".$row[0]."</td>";
                echo "<td>".$row[1]."</td>";
                echo "</tr>";
                //echo "<td>".$row[$fname->name]."</td>";
             }

           echo "</table>";

        }       /*
foreach($result as $row){

             // reset the attribute names array
             $flist = $result->fetch_fields();
                 echo "<tr>";
                 foreach($flist as $fname){
                     echo "<td>".$row[$fname->name]."</td>";
             }
                 echo "</tr>";
             }

           echo "</table>";

        }
*/
     else {
        //if ($result->num_rows == 0) {
           //Result contains no rows at all
           echo "Insufficient information from year entered";

            }

        //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: "Minimum number of followers to chart in a country"
		},
      axisX:{
		title: "Countries",
		interval: 1
      },
      axisY:{
         gridColor: "rgba(1,77,101,.1)",
         title: "Number of Followers"
      },
	data: [{
         type: "bar",
         name: "states",
         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>