Muziek chart database

Door Fabian W op 27-08-2014 18:39 gewijzigd op 27-08-2014 18:41

6.253 views

Ik probeer een database te maken van een muziekchart die ik bijhoud.

mijn huidige tabellen:

pchart201434
rank id
1 Hardwell - Arcadia
2 Sander van Doorn - Gold Skies
3 Lorde - Tennis Court
4 Arty - Up All Night
5 Katy Perry - This Is How We Do

pchart201433
rank id
1 Hardwell - Arcadia
2 Arty - Up All Night
3 Sander van Doorn - Gold Skies
4 Lorde - Tennis Court
5 Train - Angel In Blue Jeans

songs:
id artiest song afb
Hardwell - Arcadia Hardwell & Joey Dale ft. Luciana Arcadia Arcadia.jpg
Sander van Doorn - Gold Skies Sander van Doorn, Martin Garrix & Dvbbs ft. Aleesia Gold Skies Gold Skies.jpg
Lorde - Tennis Court Lorde Tennis Court Tennis Court.jpg
Arty - Up All Night Arty ft. Angel Taylor Up All Night Up All Night.jpg
Katy Perry - This Is How We Do Katy Perry This Is How We Do This Is How We Do.jpg
Train - Angel In Blue Jeans Train Angel In Blue Jeans Angel In Blue Jeans.jpg

pchart
id aw hp
Hardwell - Arcadia NULL NULL
Sander van Doorn - Gold Skies NULL NULL
Lorde - Tennis Court NULL NULL
Arty - Up All Night NULL NULL
Katy Perry - This Is How We Do NULL NULL
Train - Angel In Blue Jeans NULL NULL

Hieruit stel ik een tabel op die deze info gezamenlijk weergeeft:

SELECT pchart201434.rank, pchart201433.rank, songs.afb, songs.artiest, songs.song, pchart.aw, pchart.hp
FROM pchart201434
LEFT JOIN pchart201433 ON pchart201434.id = pchart201433.id
LEFT JOIN songs ON pchart201434.id = songs.id
LEFT JOIN pchart ON pchart201434.id = pchart.id
ORDER BY pchart201434.rank

Dit geeft een tabel, waar pchart201433.rank voor de positie in de chart van vorige week staat
nu wil ik dat pchart.aw het aantal weken van een bepaalde plaat aangeeft en
pchart.hp de hoogst behaalde positie.

Zo heeft in dit voorbeeld Gold Skies als aantal weken 2 en als hoogste positie 2.
Het aantal weken kan natuurlijk flink oplopen.

Ik zoek nu dus een manier om te tellen in hoeveel van de tabellen pchart201430, pchart201431, pchart201432 etc een bepaald id voorkomt. Ik moet hier denk ik COUNT voor gebruiken maar ik weet niet hoe.

Daarnaast zoek ik een manier om de maximum rank van een bepaald id te bepalen. Dit moet denk ik met MAX() maar ik weet ook niet hoe ik dat in deze context moet gebruiken.

(de eerste kolom van een tabel is steeds de primary key)

Zou iemand me hierbij kunnen helpen?

Fabian W

27-08-2014 19:44

oke dan zit ik dus goed

Fabian W

13-10-2014 22:17

Verdergaand met de database opzet die we vorige keer besproken hebben probeer ik een tabel in php te maken:


<?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);
?>

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?

Fabian W

21-02-2015 15:44

Mag ik een reminder sturen? ;-)

Frank Nietbelangrijk

21-02-2015 16:38 gewijzigd op 21-02-2015 16:46

Hoi Fabian,

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.


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

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.

Ger van Steenderen

21-02-2015 16:44

Frank is me net voor :-)

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)

Frank Nietbelangrijk

21-02-2015 16:47

Dank je Ger, ik wist dat je er iets over op te merken had :-)

Ik heb het inmiddels aangepast.

Fabian W

21-02-2015 17:07

Thanks guys dat werkt allemaal.

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?

Ger van Steenderen

21-02-2015 17:21


COALESCE(w.rank, 'new') as week33

Als w.rank NULL is pakt dit new anders de rank

Kan natuurlijk ook in php

<?php
echo (empty($row['week33']) ? 'new' : $row['week33'];
?>

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?

Fabian W

21-02-2015 17:25

Thanks!

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.

Ger van Steenderen

21-02-2015 18:29 gewijzigd op 21-02-2015 18:31

Ik heb al eens eerder zoiets voorhanden gehad, maar kan het niet zo snel terug vinden dus heb ik het maar opnieuw in elkaar geflanst (compleet met aantal weken in de chart)


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

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.

Reageren

Inloggen om te reageren