Databases_Project/Query15.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
<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'];

echo "<h2>Number of unique songs</h2>";
//Determine if any input was actually collected
if (empty($item1)) {
    echo "No first 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("SELECT
			    country,count(distinct songId) numUniqueSongs
			    FROM SpotifyChart
			    WHERE( DATE_FORMAT(startDate, '%Y-%m') = ? and DATE_FORMAT(endDate, '%Y-%m') = ?) group by country;")) {
    $stmt->bind_param("ss", $item1, $item1);
    //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> Unique Songs </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>