-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathget_latest_data.php
102 lines (94 loc) · 2.37 KB
/
get_latest_data.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
<?php
$user = 'root';
$password = 'root';
$db = 'tuneocracy';
$host = 'localhost';
$port = 3306;
$link = mysqli_init();
$success = mysqli_real_connect(
$link,
$host,
$user,
$password,
$db,
$port
);
$query = "SELECT sum(COALESCE(votes.`vote_value`,0)) as total_votes, currentPlaylist.`song_id`, currentPlaylist.`name`, currentPlaylist.`artist`, currentPlaylist.`playback_timestamp`
FROM `currentPlaylist`
LEFT JOIN `votes` on currentPlaylist.`song_id` = votes.`song_id`
WHERE `playback_timestamp` is not NULL
GROUP BY `song_id`
ORDER BY `playback_timestamp` ASC";
$results = mysqli_query($link, $query);
?>
Previous Songs:
<table id="playedSongTable">
<thead>
<tr>
<th>Vote Total</th><th>Title</th><th>Artist</th><th>Playback Time</th>
</tr>
</thead>
<tbody>
<?php
$last_song_num = $results->num_rows;
$i = 1;
while($row = mysqli_fetch_array($results))
{
echo "<tr><td>";
if ($i == $last_song_num)
{
echo "Now Playing";
}
else
{
echo $row['total_votes'];
}
echo "</td><td>";
echo $row['name'];
echo "</td><td>";
echo $row['artist'];
echo "</td><td>";
echo $row['playback_timestamp'];
echo "</td></tr>\n";
$i++;
}
?>
<tbody>
</table>
<br>
Vote on Upcoming Songs:
<?php
$query = "SELECT sum(COALESCE(votes.`vote_value`,0)) as total_votes, currentPlaylist.`song_id`, currentPlaylist.`name`, currentPlaylist.`artist`, currentPlaylist.`playback_timestamp`
FROM `currentPlaylist`
LEFT JOIN `votes` on currentPlaylist.`song_id` = votes.`song_id`
WHERE `playback_timestamp` is NULL
GROUP BY `song_id`
ORDER BY `total_votes` DESC";
$results = mysqli_query($link, $query);
$song_count = $results->num_rows;
?>
<table id="unplayedSongTable">
<thead>
<tr>
<th>Vote Total</th><th>Title</th><th>Artist</th>
</tr>
</thead>
<tbody>
<?php
//display all data from db in table form
while($row = mysqli_fetch_array($results))
{
$upvote_id = "upvote_" . $row['song_id'];
$downvote_id = "downvote_" . $row['song_id'];
echo "<tr><td><div class='center'><img src='images/upvote_sm.png' alt='upvote' height='24' width='24' class='votebutton' id='$upvote_id'><br>";
echo $row['total_votes'];
echo "<br><img src='images/downvote_sm.png' alt='downvote' height='24' width='24' class='votebutton' id='$downvote_id'></div></td><td>";
echo $row['name'];
echo "</td><td>";
echo $row['artist'];
echo "</td></tr>\n";
}
$link->close();
?>
<tbody>
</table>