Databases_Project/Query4.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
<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
$item = $_POST['item'];

echo "<h2>Songs with the given word in the lyrics</h2>";

//Determine if any input was actually collected
if (empty($item)) {
   echo "No word provided <br><br>";

} else {

    echo "Word: ";
   echo $item."<br><br>";
    //$phrase ="'% ".$item." %''"
    //echo $phrase."<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
    TopSongs AS (SELECT DISTINCT songID as songID
                FROM SpotifyChart
                WHERE pos <= 5)
    SELECT songName
    FROM Song JOIN TopSongs ON Song.songID = TopSongs.songID
    WHERE LOWER(lyrics) LIKE CONCAT('% ', ?, ' %');")) {
      //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("s", $item);

      //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> Song 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 top 5 songs found on the charts with the given word";

		 }

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