Databases_Project/Query13.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
<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);

//Collect the posted value in a variable called $item
$item1 = $_POST['item1'];
$item2 = $_POST['item2'];

echo "<h2>Top genre in every country</h2>";
//Determine if any input was actually collected
if (empty($item1)) {
    echo "No first date provided <br><br>";

 } elseif (empty($item2)){
     echo "No second date provided <br><br>";
 } else {

    //Prepare a statement that we can later execute. The ?'s are placeholders for
    //parameters whose values we will set before we run the query.
    if ($stmt = $conn->prepare(" WITH
				SongsInDates AS
					(SELECT distinct country, songid
					FROM SpotifyChart WHERE (DATE_FORMAT(startDate, '%Y-%m') >= ? AND DATE_FORMAT(endDate, '%Y-%m') <= ?) ),

				SongGenre AS
				    (SELECT artistID, songID, artistGenre
					FROM SongReleases JOIN Artist using (artistID) ),

			    ArtistGenre AS
				(Select distinct Country,  SongId, ArtistID,  artistGenre
				     from SongsInDates join SongGenre using (songid)
				    Order by Country, SongId, ArtistID, ArtistGenre ),

				TopSongGenre AS
				    (Select Country, artistGenre, count(*) TopGenre
				    from ArtistGenre
				    Group by 1,2
				    Order by Country, topGenre desc)

			    Select Country, ArtistGenre  From
					    (Select Country, ArtistGenre, TopGenre,
						rank() over (partition by Country  Order by Country, topGenre desc) rankGenre
						from TopSongGenre
						Order by Country, topGenre desc) AS TopRanks
						where
						rankGenre = 1;")) {
    $stmt->bind_param("ss", $item1, $item2);
    //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> Top Genre </th></tr>";
             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 {

       //Result contains no rows at all
       echo "Date information incorrect, make sure dates are formatted correctly and start date is before end date";

        }

    //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();
  ?>
  </body>