Databases_Project/Query14.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
<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>Time for a song to chart</h2>";
//Determine if any input was actually collected
if (empty($item1)) {
    echo "No song name provided <br><br>";

 } elseif (empty($item2)){
     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  Releases AS  ( Select SongID , artistid, SongName,ArtistName   from SongReleases   Join Song using (SongID)   Join Artist using (ArtistID)  ) Select   Releases.SongID,  Releases.SongName, Releases.ArtistID, ArtistName, Country,count(distinct startDate) weeksinchart  from Releases   Left join SpotifyChart on (Releases.SongID = SpotifyChart.SongID)   Where Releases.SongName =?  and Releases.ArtistName = ?   Group by 1,2,3,4,5;")) {
    $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> Weeks </th></tr>";
	    while ($row = $result->fetch_row()) {
                echo "<tr>";
                echo "<td>".$row[4]."</td>";
                echo "<td>".$row[5]."</td>";
                echo "</tr>";
                //echo "<td>".$row[$fname->name]."</td>";
             }

           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>