Muziek chart database
Pagina: « vorige 1 2 3 volgende »
Code (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
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
<?php
// con == connection
$dw = mysqli_query($con,"
SELECT chart.rank, song.artist, song.title
FROM chart
INNER JOIN song ON chart.songid=song.id
WHERE chart.year='2014' && chart.week='34'
;");
echo "<table id='chart'><thead>
<tr id='row1'>
<th>rank</th>
<th>artist</th>
<th>title</th>
</tr>
</thead><tbody>";
while($row = mysqli_fetch_array($dw)) {
$x++;
$color = ($x%2 == 0)? 'row1': 'row2';
echo "<tr id='$color'>";
echo "<td>" . $row['rank'] . "</td>";
echo "<td>" . $row['artist'] . "</td>";
echo "<td>" . $row['title'] . "</td>";
echo "</tr>";
}
echo "</tbody></table>";
mysqli_close($con);
?>
// con == connection
$dw = mysqli_query($con,"
SELECT chart.rank, song.artist, song.title
FROM chart
INNER JOIN song ON chart.songid=song.id
WHERE chart.year='2014' && chart.week='34'
;");
echo "<table id='chart'><thead>
<tr id='row1'>
<th>rank</th>
<th>artist</th>
<th>title</th>
</tr>
</thead><tbody>";
while($row = mysqli_fetch_array($dw)) {
$x++;
$color = ($x%2 == 0)? 'row1': 'row2';
echo "<tr id='$color'>";
echo "<td>" . $row['rank'] . "</td>";
echo "<td>" . $row['artist'] . "</td>";
echo "<td>" . $row['title'] . "</td>";
echo "</tr>";
}
echo "</tbody></table>";
mysqli_close($con);
?>
Nu hebben we een tabel met daarin de rank (deze week), artiest en song.
Hoe krijg ik de rank van vorige week tussen de rank van deze week en de artiest in?
D.w.z. hoe krijg ik die nieuwe while-lus van where week='33' in de huidige while lus?
Je kunt dat bereiken door de tabel charts te joinen met zichzelf. In jouw query selecteer je in eerste instantie gegevens uit de tabel rank. Door daar een join aan te koppelen op dezelfde tabel rank kun je een ander record uit die tabel halen. Als laatste doen we weer de join met songs zodat we ook info over het liedje zelf kunnen selecteren.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
s.title, w.rank as week33, c.rank as week34
FROM
chart c
LEFT JOIN
chart w
ON
c.song_id=w.song_id AND w.year=2014 AND w.week=33
INNER JOIN
songs s
ON
c.song_id=s.id
WHERE
c.year=2014 AND c.week=34
s.title, w.rank as week33, c.rank as week34
FROM
chart c
LEFT JOIN
chart w
ON
c.song_id=w.song_id AND w.year=2014 AND w.week=33
INNER JOIN
songs s
ON
c.song_id=s.id
WHERE
c.year=2014 AND c.week=34
Door de LEFT join pakt hij ook de records die in week 34 voor de eerste keer genoteerd staan en dus in week 33 nog niet voorkwam.
Gewijzigd op 21/02/2015 16:46:22 door Frank Nietbelangrijk
Een kleine opmerking, gebruik geen bitwise operators (&& en ||) maar AND en OR.
Met name de pipes hebben in bij sommige vendors een totaal andere betekenis (concat)
Ik heb het inmiddels aangepast.
Klaar ben ik nog lang niet, dat zeker niet.
Wanneer een nummer nieuw is in de lijst (geen column met week='33') wil ik graag iets in de zin van 'new' in de tabel (column van vorige week). Een leeg vakje is namelijk best wel lelijk. Hoe krijg ik dat voor mekaar?
Code (php)
1
COALESCE(w.rank, 'new') as week33
Als w.rank NULL is pakt dit new anders de rank
Kan natuurlijk ook in php
Ik zou overigens wel de zaak wat flexibeler maken, nu moet je het weeknr. telkens hard coderen.
Wanneer is de nieuwe lijst bekend, lopen de weeknr's gelijk met de weeknr's van het jaar?
Ik zat ook al te denken aan het probleem wk 52 - wk 1.
De lijst komt elke zondag. In 2014 had ik dus 52 lijsten. Op de data ...7-dec, 14-dec, 21-dec... etc.
Ik werk niet met postdates ofzo.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT c.song_id, s.title, c.rank this_week, p.rank last_week,
COUNT(g.rank) total_weeks
FROM charts c
JOIN songs s
ON c.song_id = s.id
LEFT JOIN charts p
ON c.song_id = p.song_id
AND p.week = WEEK(CURRENT_DATE - INTERVAL 1 WEEK, 2)
AND p.year = YEAR(CURRENT_DATE - INTERVAL (DAYOFWEEK(CURRENT_DATE) - 1) DAY - INTERVAL 1 WEEK)
LEFT JOIN charts g
ON c.song_id = g.song_id
WHERE
c.week = WEEK(CURRENT_DATE, 2)
AND c.year = YEAR(CURRENT_DATE - INTERVAL (DAYOFWEEK(CURRENT_DATE) - 1) DAY)
GROUP BY this_week, c.song_id, s.title, last_week
ORDER BY this_week
COUNT(g.rank) total_weeks
FROM charts c
JOIN songs s
ON c.song_id = s.id
LEFT JOIN charts p
ON c.song_id = p.song_id
AND p.week = WEEK(CURRENT_DATE - INTERVAL 1 WEEK, 2)
AND p.year = YEAR(CURRENT_DATE - INTERVAL (DAYOFWEEK(CURRENT_DATE) - 1) DAY - INTERVAL 1 WEEK)
LEFT JOIN charts g
ON c.song_id = g.song_id
WHERE
c.week = WEEK(CURRENT_DATE, 2)
AND c.year = YEAR(CURRENT_DATE - INTERVAL (DAYOFWEEK(CURRENT_DATE) - 1) DAY)
GROUP BY this_week, c.song_id, s.title, last_week
ORDER BY this_week
In de MySQL docs wordt gesteld dat de ORDER BY niet nodig is, maar ik heb hem voor de zekerheid toch maar even bij gezet.
Gewijzigd op 21/02/2015 18:31:39 door Ger van Steenderen
Aantal weken kan met COUNT(rank)
Hoogste plaats kan met MIN(rank)
Ik krijg alleen beide niet werkend, want je wil nu uit de hele database zonder where clausule kiezen.
Aantal weken en hoogste plaats heb ik er in kunnen verwerken:
Code (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
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
<?php
// con == connection
$dw = mysqli_query($con,"
SELECT c.song_id, s.title, s.artist, c.rank this_week, COALESCE(p.rank, '---') last_week, COUNT(g.rank) total_weeks, MIN(g.rank) peak_position
FROM charts c
JOIN songs s
ON c.song_id = s.id
LEFT JOIN charts p
ON c.song_id = p.song_id AND p.week=33 AND p.year=2014
LEFT JOIN charts g
ON c.song_id = g.song_id
WHERE
c.week=34 AND c.year=2014
GROUP BY this_week, c.song_id, s.title, s.artist, last_week
ORDER BY this_week
;");
echo "<table id='chart'><thead>
<tr id='row1'>
<th>dw</th>
<th>vw</th>
<th>artist</th>
<th>title</th>
<th>pp</th>
<th>tw</th>
</tr>
</thead><tbody>";
while($row = mysqli_fetch_array($dw)) {
$x++;
$color = ($x%2 == 0)? 'row1': 'row2';
echo "<tr id='$color'>";
echo "<td>" . $row['this_week'] . "</td>";
echo "<td>" . $row['last_week'] . "</td>";
echo "<td>" . $row['artist'] . "</td>";
echo "<td>" . $row['title'] . "</td>";
echo "<td>" . $row['peak_position'] . "</td>";
echo "<td>" . $row['total_weeks'] . "</td>";
echo "</tr>";
}
echo "</tbody></table>";
mysqli_close($con);
?>
// con == connection
$dw = mysqli_query($con,"
SELECT c.song_id, s.title, s.artist, c.rank this_week, COALESCE(p.rank, '---') last_week, COUNT(g.rank) total_weeks, MIN(g.rank) peak_position
FROM charts c
JOIN songs s
ON c.song_id = s.id
LEFT JOIN charts p
ON c.song_id = p.song_id AND p.week=33 AND p.year=2014
LEFT JOIN charts g
ON c.song_id = g.song_id
WHERE
c.week=34 AND c.year=2014
GROUP BY this_week, c.song_id, s.title, s.artist, last_week
ORDER BY this_week
;");
echo "<table id='chart'><thead>
<tr id='row1'>
<th>dw</th>
<th>vw</th>
<th>artist</th>
<th>title</th>
<th>pp</th>
<th>tw</th>
</tr>
</thead><tbody>";
while($row = mysqli_fetch_array($dw)) {
$x++;
$color = ($x%2 == 0)? 'row1': 'row2';
echo "<tr id='$color'>";
echo "<td>" . $row['this_week'] . "</td>";
echo "<td>" . $row['last_week'] . "</td>";
echo "<td>" . $row['artist'] . "</td>";
echo "<td>" . $row['title'] . "</td>";
echo "<td>" . $row['peak_position'] . "</td>";
echo "<td>" . $row['total_weeks'] . "</td>";
echo "</tr>";
}
echo "</tbody></table>";
mysqli_close($con);
?>
Toevoeging op 21/02/2015 19:20:43:
Verder wil ik het archief met een simple form kunnen regelen:
Code (php)
1
2
3
4
5
2
3
4
5
<form id="change_content_select1" method="get">
<select id="week1" name="b"><option value="1">1</option><option value="2">2</option><option value="3">3</option><option value="4">4</option><option value="5">5</option><option value="6">6</option><option value="7">7</option><option value="8">8</option><option value="9">9</option><option value="10">10</option><option value="11">11</option><option value="12">12</option><option value="13">13</option><option value="14">14</option><option value="15">15</option><option value="16">16</option><option value="17">17</option><option value="18">18</option><option value="19">19</option><option value="20">20</option><option value="21">21</option><option value="22">22</option><option value="23">23</option><option value="24">24</option><option value="25">25</option><option value="26">26</option><option value="27">27</option><option value="28">28</option><option value="29">29</option><option value="30">30</option><option value="31">31</option><option value="32">32</option><option value="33">33</option><option value="34">34</option><option value="35">35</option><option value="36">36</option><option value="37">37</option><option value="38">38</option><option value="39">39</option><option value="40">40</option><option value="41">41</option><option value="42">42</option><option value="43">43</option><option value="44">44</option><option value="45">45</option><option value="46">46</option><option value="47">47</option><option value="48">48</option><option value="49">49</option><option value="50">50</option><option value="51">51</option><option value="52">52</option></select>
<select id="year1" name="c"><option value="2015">2015</option><option value="2014">2014</option><option value="2013">2013</option></select>
<input id="go_button" type="submit" value="Go"/>
</form>
<select id="week1" name="b"><option value="1">1</option><option value="2">2</option><option value="3">3</option><option value="4">4</option><option value="5">5</option><option value="6">6</option><option value="7">7</option><option value="8">8</option><option value="9">9</option><option value="10">10</option><option value="11">11</option><option value="12">12</option><option value="13">13</option><option value="14">14</option><option value="15">15</option><option value="16">16</option><option value="17">17</option><option value="18">18</option><option value="19">19</option><option value="20">20</option><option value="21">21</option><option value="22">22</option><option value="23">23</option><option value="24">24</option><option value="25">25</option><option value="26">26</option><option value="27">27</option><option value="28">28</option><option value="29">29</option><option value="30">30</option><option value="31">31</option><option value="32">32</option><option value="33">33</option><option value="34">34</option><option value="35">35</option><option value="36">36</option><option value="37">37</option><option value="38">38</option><option value="39">39</option><option value="40">40</option><option value="41">41</option><option value="42">42</option><option value="43">43</option><option value="44">44</option><option value="45">45</option><option value="46">46</option><option value="47">47</option><option value="48">48</option><option value="49">49</option><option value="50">50</option><option value="51">51</option><option value="52">52</option></select>
<select id="year1" name="c"><option value="2015">2015</option><option value="2014">2014</option><option value="2013">2013</option></select>
<input id="go_button" type="submit" value="Go"/>
</form>
Daarvoor moet ik dus het jaartal en de week 34 en week 33 kunnen vervangen door de geselecteerde jaartal en week, en week -1.
Kan ik gewoon php variabelen uit het form in de mysqli-query functie schuiven?
Het probleem met week 52 - week 1 moet er ook nog even uit. Weet iemand daar een oplossing voor?
Gewijzigd op 21/02/2015 19:21:56 door Fabian W
Ik denk wel dat er nog ergens een foutje zit in mijn code, want hij doet het nog niet. Ik kijk er denk ik overheen.
Dit is de code nu:
Code (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
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
<html>
<body>
<form id="change_content_select1" method="POST">
<select id="week1" name="b"><option value="1">1</option><option value="2">2</option><option value="3">3</option><option value="4">4</option><option value="5">5</option><option value="6">6</option><option value="7">7</option><option value="8">8</option><option value="9">9</option><option value="10">10</option><option value="11">11</option><option value="12">12</option><option value="13">13</option><option value="14">14</option><option value="15">15</option><option value="16">16</option><option value="17">17</option><option value="18">18</option><option value="19">19</option><option value="20">20</option><option value="21">21</option><option value="22">22</option><option value="23">23</option><option value="24">24</option><option value="25">25</option><option value="26">26</option><option value="27">27</option><option value="28">28</option><option value="29">29</option><option value="30">30</option><option value="31">31</option><option value="32">32</option><option value="33">33</option><option value="34">34</option><option value="35">35</option><option value="36">36</option><option value="37">37</option><option value="38">38</option><option value="39">39</option><option value="40">40</option><option value="41">41</option><option value="42">42</option><option value="43">43</option><option value="44">44</option><option value="45">45</option><option value="46">46</option><option value="47">47</option><option value="48">48</option><option value="49">49</option><option value="50">50</option><option value="51">51</option><option value="52">52</option></select>
<select id="year1" name="c"><option value="2015">2015</option><option value="2014">2014</option><option value="2013">2013</option></select>
<input id="go_button" type="submit" value="Go"/>
</form>
<?php
// form variables
$this_year = $_POST['year1'];
$this_week = $_POST['week1'];
if ($_POST['week1'] == 1) {
$last_year = $_POST['year1'] - 1;
$last_week = '(SELECT MAX(week) FROM charts WHERE year = ' . $last_year . ')';
}
else {
$last_year = $_POST['year1'];
$last_week = $_POST['week1'] - 1;
}
// sql query
$sql = "SELECT c.song_id, s.title, s.artist, c.rank this_week, COALESCE(p.rank, '---') last_week, COUNT(g.rank) total_weeks, MIN(g.rank) peak_position
FROM charts c
JOIN songs s
ON c.song_id = s.id
LEFT JOIN charts p
ON c.song_id = p.song_id AND p.week=".$last_week." AND p.year=".$last_year."
LEFT JOIN charts g
ON c.song_id = g.song_id
WHERE
c.week= ".$this_week." AND c.year= ".$this_year."
GROUP BY this_week, c.song_id, s.title, s.artist, last_week
ORDER BY this_week";
// Create connection
$con = mysqli_connect("gaat je niks aan");
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
else
echo "<table id='chart'> <thead> <tr id='row1'> <th>dw</th> <th>vw</th> <th>artist</th> <th>title</th> <th>pp</th> <th>tw</th> </tr> </thead> <tbody>";
//$con == connection, $sql = sql query
$mysqli = mysqli_query($con, $sql);
// output the sql table
while($row = mysqli_fetch_array($mysqli)) {
$x++;
$color = ($x%2 == 0)? 'row1': 'row2';
echo "<tr id='$color'>";
echo "<td>" . $row['this_week'] . "</td>";
echo "<td>" . $row['last_week'] . "</td>";
echo "<td>" . $row['artist'] . "</td>";
echo "<td>" . $row['title'] . "</td>";
echo "<td>" . $row['peak_position'] . "</td>";
echo "<td>" . $row['total_weeks'] . "</td>";
echo "</tr>";
}
echo "</tbody></table>";
mysqli_close($con);
?>
</body>
</html>
<body>
<form id="change_content_select1" method="POST">
<select id="week1" name="b"><option value="1">1</option><option value="2">2</option><option value="3">3</option><option value="4">4</option><option value="5">5</option><option value="6">6</option><option value="7">7</option><option value="8">8</option><option value="9">9</option><option value="10">10</option><option value="11">11</option><option value="12">12</option><option value="13">13</option><option value="14">14</option><option value="15">15</option><option value="16">16</option><option value="17">17</option><option value="18">18</option><option value="19">19</option><option value="20">20</option><option value="21">21</option><option value="22">22</option><option value="23">23</option><option value="24">24</option><option value="25">25</option><option value="26">26</option><option value="27">27</option><option value="28">28</option><option value="29">29</option><option value="30">30</option><option value="31">31</option><option value="32">32</option><option value="33">33</option><option value="34">34</option><option value="35">35</option><option value="36">36</option><option value="37">37</option><option value="38">38</option><option value="39">39</option><option value="40">40</option><option value="41">41</option><option value="42">42</option><option value="43">43</option><option value="44">44</option><option value="45">45</option><option value="46">46</option><option value="47">47</option><option value="48">48</option><option value="49">49</option><option value="50">50</option><option value="51">51</option><option value="52">52</option></select>
<select id="year1" name="c"><option value="2015">2015</option><option value="2014">2014</option><option value="2013">2013</option></select>
<input id="go_button" type="submit" value="Go"/>
</form>
<?php
// form variables
$this_year = $_POST['year1'];
$this_week = $_POST['week1'];
if ($_POST['week1'] == 1) {
$last_year = $_POST['year1'] - 1;
$last_week = '(SELECT MAX(week) FROM charts WHERE year = ' . $last_year . ')';
}
else {
$last_year = $_POST['year1'];
$last_week = $_POST['week1'] - 1;
}
// sql query
$sql = "SELECT c.song_id, s.title, s.artist, c.rank this_week, COALESCE(p.rank, '---') last_week, COUNT(g.rank) total_weeks, MIN(g.rank) peak_position
FROM charts c
JOIN songs s
ON c.song_id = s.id
LEFT JOIN charts p
ON c.song_id = p.song_id AND p.week=".$last_week." AND p.year=".$last_year."
LEFT JOIN charts g
ON c.song_id = g.song_id
WHERE
c.week= ".$this_week." AND c.year= ".$this_year."
GROUP BY this_week, c.song_id, s.title, s.artist, last_week
ORDER BY this_week";
// Create connection
$con = mysqli_connect("gaat je niks aan");
// Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
else
echo "<table id='chart'> <thead> <tr id='row1'> <th>dw</th> <th>vw</th> <th>artist</th> <th>title</th> <th>pp</th> <th>tw</th> </tr> </thead> <tbody>";
//$con == connection, $sql = sql query
$mysqli = mysqli_query($con, $sql);
// output the sql table
while($row = mysqli_fetch_array($mysqli)) {
$x++;
$color = ($x%2 == 0)? 'row1': 'row2';
echo "<tr id='$color'>";
echo "<td>" . $row['this_week'] . "</td>";
echo "<td>" . $row['last_week'] . "</td>";
echo "<td>" . $row['artist'] . "</td>";
echo "<td>" . $row['title'] . "</td>";
echo "<td>" . $row['peak_position'] . "</td>";
echo "<td>" . $row['total_weeks'] . "</td>";
echo "</tr>";
}
echo "</tbody></table>";
mysqli_close($con);
?>
</body>
</html>
Hoeveel rijen krijg je terug?
Als ik $last_week laat staan, maar ergens anders $last_week=33 neerzet, doet hij het opeens niet meer.
Kun je omwille van makkelijkere navigatie en debugging niet beter overstappen op method="get"?
En geef, als er niets is ingevuld, $this_week, $this_year, $last_week en $last_year zinvolle standaard waarden.
if($_SERVER['REQUEST_METHOD'] == 'POST')
{
// alle variabelen
}
Lost het probleem niet op.
Gewijzigd op 22/02/2015 16:07:27 door Fabian W
Ik denk toch dat een (fatsoenlijke) initialisatie van je variabelen (ook) kan helpen. Of je moet gaan voor een opzet waarbij je niets afdrukt als er niets is ingesteld (kies een van de twee).
Daarnaast ga je er vanuit dat er altijd resultaten zijn (je begint direct met je while) zonder dat je controleert of er wel een resultaat is (een controle met mysqli_num_rows() of equivalent).
Pagina: « vorige 1 2 3 volgende »