Databases_Project/Query11.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
<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
$this_song = $_POST['this_song'];
$this_artist = $_POST['this_artist'];

echo "<h2>Time for a song to chart</h2>";
//Determine if any input was actually collected
if (empty($this_song)) {
    echo "No song name provided <br><br>";

 } elseif (empty($this_artist)){
     echo "No artist name provided <br><br>";
 } else {
   //  echo $item."<br><br>";

    //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
     ArtistSongs AS (SELECT Song.songID, artistName, songName, Artist.artistID
                 FROM Artist JOIN SongReleases JOIN Song
                     ON Artist.artistID = SongReleases.artistID and SongReleases.songID = Song.songID),
     ThisSong AS (
         SELECT songName, songID, artistName, artistID
                 FROM ArtistSongs
                 WHERE songName = ? and artistName = ?),
     ThisSongAlbum AS (
         SELECT ThisSong.songID, AlbumReleases.albumID, releaseDate
                     FROM AlbumTracks JOIN AlbumReleases JOIN ThisSong
                     ON ThisSong.songID = AlbumTracks.songID and AlbumTracks.albumID = AlbumReleases.albumID),
     ChartDate AS (
         SELECT MIN(startDate) as earliestonchart, SpotifyChart.songID, releaseDate
                     FROM SpotifyChart JOIN ThisSongAlbum ON SpotifyChart.songID = ThisSongAlbum.songID
                         WHERE startDate >= releaseDate)
     SELECT DATEDIFF(earliestonchart, releaseDate) AS TimeToChart
             FROM ChartDate;")) {
       //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".
       $stmt->bind_param("ss", $this_song, $this_artist);
       //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> Time to Chart</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 "No info found for this song and/or artist";

        }

    //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>