Databases_Project/Query7.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
<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

echo "<h2>Artists with collaborations on the charts</h2>";
//Determine if any input was actually collected
if ($stmt = $conn->prepare(
    "WITH
    Collabs AS (SELECT * FROM SongReleases WHERE numArtists > 1),
    ChartCollabs AS (SELECT DISTINCT artistID
                    FROM Collabs JOIN SpotifyChart ON Collabs.songID = SpotifyChart.songID)

    SELECT artistName
    FROM Artist JOIN ChartCollabs ON Artist.artistID = ChartCollabs.artistID
    ORDER BY artistName ASC;")) {
       //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> Artist Name</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 {
        //if ($result->num_rows == 0) {
           //Result contains no rows at all
           echo "No artists found with collaborations on the charts";

            }

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