[MySQL] Plaats in ranglijst (PHP)

[Jeeeroen]

Ik heb een MySQL-tabel met daarin id-nummers van leden en het aantal stemmen dat ze hebben gekregen van andere leden. Hoe kan ik nu (met MySQL en eventueel met PHP) op de beste en efficiëntste manier achterhalen op welke positie van de ranglijst één bepaald lid staat? Ik wil dit vermelden op de persoonlijke pagina van leden.

Voorbeeld MySQL-tabel:

ID	Stemmen
Mark	45
Willem	23
Gijs	87
Bart	55

Het aantal rijen in de tabel (leden) is ca. 1.000.

PHP-versie: 4.3.10
MySQL-versie: 4.0.22-standard

Bedankt!

[ Voor 5% gewijzigd door Jeeeroen op 23-02-2005 15:51 ]

[Soultaker]

Ik ga je het antwoord niet kado doen; eerst wat QuickStart vragen: wat heb je zelf al geprobeerd, wat lukte er niet en waarom niet?

Een hint voor de juiste oplossing:
• Als je de hoogste score van iedereen hebt, sta je op plaats 1.
• Als je de op een-na-hoogste score hebt, sta je op plaats 2. Er is dan dus één persoon met een hogere score.
• Als je de op twee-na-hoogste score hebt, sta je op plaats 3. Er zijn dan dus twee personen met een hogere score.
• Als je op de plaats N staat, zijn er dus N-1 personen met een hogere score.

[ Voor 64% gewijzigd door Soultaker op 23-02-2005 15:46 ]

[Jeeeroen]

Wat ik heb geprobeerd: Ik heb in een cronjob die elke minuut draait de MySQL-tabel gesorteerd en vervolgens ieder lid een opeenlopend nummer gegeven, de positie op de ranglijst dus. Dit nummer kan dan geselecteerd worden op de persoonlijke pagina van leden. Het lijkt me dat dit echter op een efficiëntere manier kan.

Ik ga even kijken of ik iets met je tips kan, bedankt in ieder geval.

[Wolfboy]

Het eerste dat zo eventjes in me op komt, een count doen van alle mensen met een hogere score dan de persoon.

Is dat misschien een mogelijkheid?

[Jeeeroen]

Zou kunnen Wolfboy, met gedeelde plaatsen wordt dan ook automatisch rekening gehouden, bedenk me net.

Bedankt, ik ga het even proberen!

[douweh]

eerst het aantal stemmen van dat lid eruithalen,
en vervolgens een query doen:

code:

$query="SELECT iets FROM tabel WHERE stemmen<stemmenvandatenelid";

Vervolgens t aantal teruggekregen records tellen en de positie van t betreffende lid, is dus 1 meer.

kost je 2 queries per hit, misschien kan t nog makkelijker hoor, maar zou ff niet weten hoe.

[Jeeeroen]

Het aantal stemmen wordt automatisch meegenomen in de query die de informatie voor op de persoonlijke pagina ophaalt, dus dat is geen probleem. Maar het is natuurlijk nog beter als het in één query kan. Ik ben al aan het puzzelen.

[NMe]

Jeeeroen schreef op woensdag 23 februari 2005 @ 15:53:
Zou kunnen Wolfboy, met gedeelde plaatsen wordt dan ook automatisch rekening gehouden, bedenk me net.

Nee hoor, daar wordt niet automatisch rekening mee gehouden. Hooguit als je van tevoren nog eens groepeert op score, maar dat kan niet zomaar in één query in MySQL.

[chem]

Gebruik je MySQL 4.x ?

[Jeeeroen]

-NMe- schreef op woensdag 23 februari 2005 @ 15:57:
[...]

Nee hoor, daar wordt niet automatisch rekening mee gehouden. Hooguit als je van tevoren nog eens groepeert op score, maar dat kan niet zomaar in één query in MySQL.

Wel als je gedeelde plaatsen als volgt rekent, toch?

Pos.	Naam	Stemmen
1.	Jan	87
	Piet	87
3.	Kees	63

[Jeeeroen]

chem schreef op woensdag 23 februari 2005 @ 15:58:
Gebruik je MySQL 4.x ?

Ja, momenteel 4.0.22-standard.

[Soultaker]

-NMe- schreef op woensdag 23 februari 2005 @ 15:57:
Nee hoor, daar wordt niet automatisch rekening mee gehouden. Hooguit als je van tevoren nog eens groepeert op score, maar dat kan niet zomaar in één query in MySQL.

Juist wel! Meestal wil je de plaats delen bij gelijke score, zo dus:

1	Soultaker	1000 stemmen
2	-NMe-	500 stemmen
2	Jeeeroen	500 stemmen
4	chem	2 stemmen

Als je dan telt hoeveel mensen er meer stemmen hebben dan bijvoorbeeld Jeeeroen kom je op 1 uit, net als voor -NME-, en beiden krijgen zo plaats 2 (terwijl de eerstvolgende wel plaats 4 krijgt en niet plaats 3).

edit:
Ik ben traag vandaag.

[ Voor 26% gewijzigd door Soultaker op 23-02-2005 16:04 ]

[Jeeeroen]

Juist, bedankt voor je toelichting Soultaker . Gaan we trouwens de goede kant op met de beste oplossing?

Ik heb zo het idee dat de laatste zin niet helemaal klopt...

[ Voor 40% gewijzigd door Jeeeroen op 23-02-2005 16:03 ]

[Soultaker]

Ik doelde op het tellen van het aantal mensen boven je (wat Wolfboy ook zei). Efficiënter zal het niet worden, of je moet de positie in je database opslaan (en dan moet je de positie elke keer dat er gestemd wordt aanpassen). Maar als er een index is op het aantal stemmen, dan kan de database die COUNT-query razendsnel uitvoeren en zou ik eigenlijk niet moelijk gaan doen.

[ Voor 25% gewijzigd door Soultaker op 23-02-2005 16:05 ]

[Jeeeroen]

Hmja, maar of dat efficiënt is... bedankt in ieder geval! Ik laat later vandaag nog wel van me horen wat het is geworden.

[chem]

Je zou iets kunnen doen als....
[code=mysql]SET @counter = 0[/]
[code=mysql]select (@counter := (@counter + 1)) as counter, username
from votes where id = 4 order by voteCounter asc[/]

zo ff uit de losse pols

Misschien dat een SQL_CALC_FOUND_ROWS erbij nog wat kan uitmaken

eerst ff testen

[ Voor 19% gewijzigd door chem op 23-02-2005 16:11 ]

[Jeeeroen]

Ik ben benieuwd chem .

[P_de_B]

code:

SELECT *, (SELECT COUNT(*) FROM Tabel WHERE Stemmen > T.Stemmen) + 1 as Position
FROM Tabel T
ORDER BY Position

[ Voor 9% gewijzigd door P_de_B op 23-02-2005 16:22 . Reden: code tags ]

[curry684]

Soultaker schreef op woensdag 23 februari 2005 @ 16:04:
Maar als er een index is op het aantal stemmen, dan kan de database die COUNT-query razendsnel uitvoeren en zou ik eigenlijk niet moelijk gaan doen.

Sterker nog, voor een slimme database is dat instantanous omdat het namelijk per definitie de positie binnen de index is. Ergo hij hoeft niet eens iets te tellen als ie goed optimized

* curry684 ziet echter MySQL in de topictitel staan en droomt even verder

[Soultaker]

P_de_B schreef op woensdag 23 februari 2005 @ 16:22:

code:

SELECT *, (SELECT COUNT(*) FROM Tabel WHERE Stemmen > T.Stemmen) + 1 as Position
FROM Tabel T
ORDER BY Position

Dan kun je beter gewoon "SELECT * FROM Tabel ORDER BY Stemmen DESC" doen en zelf de positie erbij verzinnen. (Want ze staan toch gesorteerd op positie.)

[P_de_B]

Soultaker schreef op woensdag 23 februari 2005 @ 16:35:
[...]

Dan kun je beter gewoon "SELECT * FROM Tabel ORDER BY Stemmen DESC" doen en zelf de positie erbij verzinnen. (Want ze staan toch gesorteerd op positie.)

Ja, maar ik dacht dat je 1 bepaald lid moest kunnen opzoeken, dus een WHERE clausule toevoegen en direct terugkrijgen op welke positie het betreffende lid staat. De complete query is dan:

code:

SELECT *, (SELECT COUNT(*) FROM Tabel WHERE Stemmen > T.Stemmen) + 1 as Position
FROM Tabel T
WHERE lid = 'Peter'
ORDER BY Position

ik had iets duidelijker kunnen zijn.

[Soultaker]

Maar die variant was al gesuggereerd, zonder subquery uiteraard, want MySQL 4.0 ondersteunt die niet.

[Jeeeroen]

Edit:
Stomme vraag, er is maar één efficiënte manier genoemd tot nu toe, dus dat is ook automatisch de beste ... MySQL 4.0 ondersteunt natuurlijk inderdaad geen subquery's. Ik ben nog steeds benieuwd naar de query van chem!

En een index op de kolom met het aantal stemmen is dus wel aan te raden?

[ Voor 68% gewijzigd door Jeeeroen op 23-02-2005 17:14 ]

[Wolfboy]

Jeeeroen schreef op woensdag 23 februari 2005 @ 17:07:
En een index op de kolom met het aantal stemmen is dus wel aan te raden?

In het geval van MySQL, ja

[Jeeeroen]

Wolfboy schreef op woensdag 23 februari 2005 @ 18:45:
[...]
In het geval van MySQL, ja

Ok, dankje. Ik twijfel meestal. Het updaten en inserten kost immers 'meer' tijd als een index is ingesteld...

[JHS]

Jeeeroen schreef op woensdag 23 februari 2005 @ 18:59:
[...]Ok, dankje. Ik twijfel meestal. Het updaten en inserten kost immers 'meer' tijd als een index is ingesteld...

Het verschilt dan ook of een index handig is, en is (vnl.) afhankelijk van de verhouding write ten opzicht van read. En de enige manier om uit te vinden wát nu precies sneller is, is het testen timen .

[ Voor 3% gewijzigd door JHS op 23-02-2005 19:12 ]

[Wolfboy]

Jeeeroen schreef op woensdag 23 februari 2005 @ 17:07:
Stomme vraag, er is maar één efficiënte manier genoemd tot nu toe, dus dat is ook automatisch de beste ... MySQL 4.0 ondersteunt natuurlijk inderdaad geen subquery's. Ik ben nog steeds benieuwd naar de query van chem!

Aangezien chem z'n reactie uitblijft geef ik ff een voorbeeldje:

MySQL:

SET @counter = 0;
SELECT (@counter := (@counter + 1)) AS counter, t1.username 
FROM votes AS t1, votes AS t2
WHERE t1.Stemmen > t2.Stemmen AND t2.ID = 'Mark';

Efficient? tja
Het zal waarschijnlijk wel werken

[Jeeeroen]

Bedankt allemaal, ik weet voorlopig voldoende! Ik hoop dat anderen met dezelfde vraag dit topic kunnen vinden .