-
Notifications
You must be signed in to change notification settings - Fork 0
/
query_2_b.php
100 lines (99 loc) · 3.41 KB
/
query_2_b.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
<?php include('dbclass.php'); ?>
<!DOCTYPE html>
<html>
<head>
<title>NBA Statistics</title>
<link rel="stylesheet" href="css/bootstrap.min.css">
<link rel="stylesheet" href="css/style.css">
</head>
<body>
<div class="navbar navbar-fixed-top">
<div class="navbar-inner">
<div class="container">
<a class="btn btn-navbar" data-toggle="collapse" data-target=".nav-collapse">
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</a>
<a class="brand" href="./index.html">NBA Project</a>
<div class="nav-collapse">
<ul class="nav">
<li>
<a href="./index.php">Home</a>
</li>
<li class="">
<a href="search.php">Search for Keyword</a>
</li>
<li class="">
<a href="insert_data.html">Insert elements</a>
</li>
<li class="dropdown">
<a class="dropdown-toggle" data-toggle="dropdown" href="#">Deliverable 2 <b class="caret"></b></a>
<ul class="dropdown-menu">
<li><a href="query_2_a.php">Query a</a></li>
<li class="active"><a href="query_2_b.php">Query b</a></li>
<li><a href="query_2_c.php">Query c</a></li>
<li><a href="query_2_d.php">Query d</a></li>
<li><a href="query_2_e.php">Query e</a></li>
<li><a href="query_2_f.php">Query f</a></li>
</ul>
</li>
</ul>
</div>
</div>
</div>
</div>
<header>
<h1></h1>
</header>
<section class="container content">
<header class="jumbotron subhead" id="overview">
<h1>Query b, Deliverable 2</h1>
<p class="lead">Print the last and first name of those who participated in NBA as both a player and a coach in the same
season.</p>
<pre>
SELECT Distinct p.firstname, p.lastname FROM Player p, RegSeason rs
WHERE p.pid = rs.pid AND
rs.league = 'N' AND
p.pid IN (SELECT cs.cid FROM CoachSeason cs, TeamStat ts
WHERE cs.tid = ts.tid AND
ts.year = rs.year AND
cs.year = ts.year AND
ts.league = 'N')
</pre>
</header>
<?php $connection = new DB_Class(); ?>
<?php $connection->connect() ?>
<?php $query = "
SELECT Distinct p.firstname, p.lastname FROM Player p, RegSeason rs
WHERE p.pid = rs.pid AND
rs.league = 'N' AND
p.pid IN (SELECT cs.cid FROM CoachSeason cs, TeamStat ts
WHERE cs.tid = ts.tid AND
ts.year = rs.year AND
cs.year = ts.year AND
ts.league = 'N') "; ?>
<p><?php echo $connection->count_results($query); ?> results found.</p>
<?php $data = $connection->fetch($query); ?>
<table class="table table-striped">
<thead>
<tr>
<th>Firstname</th>
<th>Lastname</th>
</tr>
</thead>
<tbody>
<?php foreach ($data as $row): ?>
<tr>
<td><?php echo $row[0]; ?></td>
<td><?php echo $row[1]; ?></td>
</tr>
<?php endforeach ?>
</tbody>
</table>
<?php $connection->close_connection(); ?>
</section>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<script src="js/bootstrap.min.js"></script>
</body>
</html>