-
Notifications
You must be signed in to change notification settings - Fork 0
/
movies.php
178 lines (167 loc) · 7.21 KB
/
movies.php
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
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
<!--Here is some styling HTML you don't need to pay attention to-->
<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" />
<style>
.container {margin: auto; align-content: center;}
.table-fix {box-shadow: 0px 0px 5px 1px; display: table; }
</style>
</head>
<body>
<div class="container">
<div class="page-header">
<nav class="navbar navbar-expand-sm bg-dark navbar-dark">
<a class="navbar-brand" href="movies.php">Movies</a>
<ul class="navbar-nav">
<li class="nav-item">
<a class="nav-link" href="createmovies.php">Add movie</a>
</li>
<li class="nav-item">
<a class="nav-link" href="users.php">Users</a> <!--Insert your own php-file here -->
</li>
<li class="nav-item">
<a class="nav-link" href="watchlist.php">Watchlist</a> <!--Insert your own php-file here -->
</li>
</ul>
</nav>
</div>
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>" method="post">
<table class='table table-hover table-responsive table-bordered'>
<tr>
<td>Search</td>
<td><input type='text' name='keyword' class='form-control' />
<input type="submit">
</td>
<td>Name<input type='checkbox' name='namesearch' class='form-control' value = "media.name" checked/></td>
<td>Actors<input type='checkbox' name='actorsearch' class='form-control' value = "actor.aname"/></td>
<td>Year<input type='checkbox' name = 'useyear' class="form-control" value = "CAST(media.year AS VARCHAR)"></td>
<td>Country<input type='checkbox' name='countrysearch' class='form-control' value = "nationality.nname"/></td>
<td>Director<input type='checkbox' name='directorsearch' class='form-control' value = "director.dname"/></td>
<td>Genres<input type='checkbox' name='genresearch' class='form-control' value = "mgenre.gname"/></td>
<td>Length<input type='checkbox' name='lengthsearch' class='form-control' value = "CAST(media.length AS VARCHAR)"/>
From<input type ='number' name='length_from' class='form-control' min=0>To
<input type ='number' name='length_to' class='form-control' min = 0></td>
<td>Rating<input type='checkbox' name='ratingsearch' class='form-control' value = "CAST(ratings.avg AS VARCHAR)"/>
From<input type ='number' name='rating_from' class='form-control' min=0 max = 10>To
<input type ='number' name='rating_to' class='form-control' min = 0 max = 10></td>
<td><select name="search_type" id="search_type" selected="ANY">
<option value="ANY">Any</option>
<option value="ALL">All</option>
</select></td>
</tr>
</table>
</form>
<!--Styling HTML ends and the real work begins below-->
<?php
include 'connection.php'; //Init a connection
$nsearch = 0;
$sep = "OR";
$namesearch = isset($_POST['namesearch']) ? $_POST['namesearch'] : '';
$actorsearch = isset($_POST['actorsearch']) ? $_POST['actorsearch'] : '';
$useyear = isset($_POST['useyear']) ? $_POST['useyear'] : '';
$countrysearch = isset($_POST['countrysearch']) ? $_POST['countrysearch'] : '';
$genresearch = isset($_POST['genresearch']) ? $_POST['genresearch'] : '';
$lengthsearch = isset($_POST['lengthsearch']) ? $_POST['lengthsearch'] : '';
$directorsearch = isset($_POST['directorsearch']) ? $_POST['directorsearch'] : '';
$ratingsearch = isset($_POST['ratingsearch']) ? $_POST['ratingsearch'] : '';
$length_from = isset($_POST['length_from']) ? $_POST['length_from'] : '';
$length_to = isset($_POST['length_to']) ? $_POST['length_to'] : '';
$rating_from = isset($_POST['rating_from']) ? $_POST['rating_from'] : '';
$rating_to = isset($_POST['rating_to']) ? $_POST['rating_to'] : '';
$search_type = isset($_POST['search_type']) ? $_POST['search_type'] : '';
if($search_type == "ANY"){
$sep = "OR";
}
else{
$sep = "AND";
}
$tables = ["media"];
$groupby = [""];
$tablesstr = "";
if (isset($_POST['actorsearch'])) {array_push($tables, " NATURAL JOIN Acting NATURAL JOIN Actor");
}
if (isset($_POST['countrysearch'])) {array_push($tables, " NATURAL JOIN Nationality");
}
if (isset($_POST['genresearch'])) {array_push($tables, " NATURAL JOIN mgenre");
}
if (isset($_POST['directorsearch'])) {array_push($tables, " NATURAL JOIN direction NATURAL JOIN director");
}
if (isset($_POST['ratingsearch'])) {array_push($tables, " NATURAL JOIN ratings");
}
foreach($tables as &$table){
$tablesstr = $tablesstr.$table;
}
$qarr = array($namesearch, $actorsearch, $useyear, $countrysearch, $genresearch, $directorsearch);
$qarr2 = [];
$conds = "";
foreach(array_slice($qarr, 1) as &$value){
if(!empty($value)){
array_push($qarr2, $sep." LOWER(".$value.") LIKE LOWER(:keyword)");
}
}
if(isset($_POST['lengthsearch'])){
if(!empty($length_from) && !empty($length_to)){
array_push($qarr2, " AND length >= $length_from AND length <= $length_to");
}
elseif(!empty($length_from)){
array_push($qarr2, " AND length >= $length_from");
}
elseif(!empty($length_to)){
array_push($qarr2, " AND length <= $length_to");
}
}
if(isset($_POST['ratingsearch'])){
if(!empty($rating_from) && !empty($rating_to)){
array_push($qarr2, " AND avg >= $rating_from AND avg <= $rating_to");
}
elseif(!empty($rating_from)){
array_push($qarr2, " AND avg >= $rating_from");
}
elseif(!empty($rating_to)){
array_push($qarr2, " AND avg <= $rating_to");
}
}
foreach($qarr2 as &$string){
$conds = $conds.$string;
}
$query = "SELECT mID, name FROM (SELECT * FROM $tablesstr WHERE LOWER(media.name) LIKE LOWER(:keyword) $conds) a GROUP BY mID, name ORDER BY mID";
$stmt = $con->prepare($query);
$keyword= isset($_POST['keyword']) ? $_POST['keyword'] : ''; //Is there any data sent from the form?
$keyword = "%".$keyword."%";
$stmt->bindParam(':keyword', $keyword);
$stmt->execute();
$num = $stmt->rowCount(); //Aquire number of rows
//print_r($stmt->debugDumpParams());
if($num>0){ //Is there any data/rows?
echo "<table class='table table-responsive table-fix table-bordered'><thead class='thead-light'>";
echo "<tr>";
echo "<th>namn</th>"; // Rename, add or remove columns as you like.
echo "<th>mID</th>";
echo "<th>buttons</th>";
echo "</tr>";
while ($rad = $stmt->fetch(PDO::FETCH_ASSOC)){ //Fetches data
extract($rad);
echo "<tr>";
//print_r(get_defined_vars());
// Here is the data added to the table
echo "<td>{$name}</td>"; //Rename, add or remove columns as you like
echo "<td>{$mid}</td>";
//Here are the buttons for update, delete and read.
echo "<td><a href='readMovies.php?name={$mid}'class='btn btn-info m-r-1em'>Read</a>"; // Replace with ID-variable, to make the buttons work
echo "<a href='updateMovies.php?name={$mid}' class='btn btn-primary m-r-1em'>Update</a>";// Replace with ID-variable, to make the buttons work
echo "<a href='deleteMovies.php?mid={$mid}' class='btn btn-danger'>Delete</a>";// Replace with ID-variable, to make the buttons work
echo "<a href='addtowatchlist.php?mid={$mid}' class='btn btn-danger'>Add to watchlist</a></td>";
echo "</td>";
echo "</tr>";
}
echo "</table>";
}
else{
echo "<h1> Search gave no result </h1>";
print_r($stmt->errorInfo());
}
?>
</div>
</body>
</html>