MySQL - Anzeige und Sortierung

Walerik

Hallo leutz ...

hab volgendes problem:
auf meiner seite haben die user, die möglichkeit bilder anderer user zu
bewerten. Die besten pics wernden in einer top liste angezeigt.
die query sieht so aus:

PHP

$top=mysql_query("SELECT * FROM `foto`, `profil` WHERE profil.id=foto.user_id AND profil.sex=$sex AND foto.status=1 ORDER BY prozent DESC $zahl");

da die user aber die möglichkeit haben mehrere pics hochzuladen,
kommt es schon mal vor das von einem user nicht nur das beste bild
gezeigt wird sondern alle die gut bewertet sind. jetzt ist meine idee jeden user nur einmal
anzeigen zu lassen und zwar mit dem am höchsten bewerteten bild also hab ich die query so geänder:

PHP

$top=mysql_query("SELECT * FROM `foto`, `profil` WHERE profil.id=foto.user_id AND profil.sex=$sex AND foto.status=1 GROUP BY foto.user_id ORDER BY prozent DESC $zahl");

mit group by! aber es kam anschließend nur mist raus der 4 palzierte war dann platz 1 und der, der die ersten drei ränge belegt hat war nicht mehr in den toplist zu finden...

was mach ich da falsch wie kann ich meine idee realisieren?

synaptic

also jetzt nur so als idee.. mach doch ne subquery.. in der holste dir das beste bild des users und das bauste dann in deine hauptquery ein..

Walerik

acha ... und wie stelle ich das an?

synaptic

http://dev.mysql.com/doc/refman/5.1/de/subqueries.html

SinnlosS

Mir fällt spontan eine Lösung ein, die aus Sicht der theoretischen Datenbankmodellierung zwar nicht optimal ist, aber konsequente Normalisierung ist in der Praxis halt bei weitem nicht immer sinnvoll.
Leg dir eine zusätzliche Tabelle an, z.b. `user_toprated_pics`, primary key `user_id`, weitere Spalten `bild_id`, `rating`.
Da kannst du ja auch immer nur die 10 (oder wieviel du brauchst) besten Ergebnisse reinsetzen.

Damit hast du zwar eine zusätzliche Tabelle und ein wenig doppelte Datenhaltung, es lässt sich aber eine wunderbar einfache und schnelle Abfrage bauen.

Walerik

Zitat von synaptic

http://dev.mysql.com/doc/refman/5.1/de/subqueries.html

was subqueries sind weiß ich jetzt
... danke schon mal
... aber ich komm einfach nicht drauf wie es mit den subqueries realisieren soll
... mir qualmt der schedel

SinnlosS

Ungetestet:

SQL

SELECT
	MAX(f.rating) AS max,
	p.id AS pid,
	f.id AS fid,
	...
FROM
	profil p,foto f
WHERE
	p.id=f.user_id
ORDER BY
	MAX(f.rating) DESC
LIMIT 
	0,10

Alles anzeigen

Von Subqueries würde ich abraten, besonders wenn es viele user und fotos werden wird das sehr langsam. Sollte man nur verwenden wenn es sich wirklich nicht vermeiden lässt.

Walerik

danke schon mal werde es morgen mal testen!
aber ist momentan etwas über 5000 userfotos in der db und sollen nich mehr werden!
aber werden maximal in den tops nur 30 bilder angezeigt!

SinnlosS

Sorry, mein query ist blödsinn. SELECT MAX(...) gibt dir natürlich nur eine Reihe zurück, kA was mich da grad geritten hat

Eine weitere Alternative wäre eine neu Spalte in `profil`: `top_foto_rating`

SQL

SELECT
    p.id AS pid,
    f.id AS fid,
    ...
FROM
    profil p,foto f
WHERE
    p.top_foto_rating=f.rating
    AND
    p.id=f.user_id
ORDER BY
    p.top_foto_rating DESC
LIMIT 
    0,30

Alles anzeigen

Walerik

ja und wie soll der wert in der neuen tabelle gebildet werden?

kurz zum vote system was:

es stehen immer zwei fotos gegenüber im zufallsprinzip.
die user entscheiden welches sie aktraktiver finden.
der wert für die toplist wird gebildet aus anzahl der votings
und der anzahl als gewonnen augengangener votings in prozent

SinnlosS

Du speicherst doch bei den Bildern selbst auch das Rating, bzw. bei dir heißt die Spalte `prozent`? Den höchsten Prozentwert den ein Bild eines users hat schreibst du eben in die zusätzliche Spalte in der profiltabelle. So hast du für jeden user gleichzeitig die höchste Bildbewertung.
Beim Voten berechnest du dann einfach die neue prozentzahl von dem Bild das bevotet wurde und führst dann ein sql-update aus:

PHP

$sql = "
UPDATE
    profil
SET 
    top_foto_rating=".$new_prozent."
WHERE
    id=".$foto_besitzer_id."
    AND
    top_foto_rating<".$new_prozent;

Walerik

ich raf den sinn nicht wieso soll ich das doppelt gemoppelt machen?
die zusätzliche spalte ist doch nichts anderes als was bei mir prozent
ist. also hab ich zwei spalten in einer tabele mit selbem wert aber verschiedenen namen

SinnlosS

Du hast in deiner profil-Tabelle schon eine Spalte mit dem besten rating für ein bild der user?
Dann verstehe ich ehrlich gesagt nicht wo dein Problem ist und was du da im Eröffnungspost für Abfragen gebastelt hast?
Und wozu hattest du denn dann die Spalte überhaupt wenn du sie gar nicht für dieses Vorhaben nutzen wolltest?

Walerik

axo ... ne ich ging von der tabele foto und nicht profil aus jetzt verstehe ich auch deine zusätzlich spalte ... sry

Walerik

Zitat von SinnlosS
Du speicherst doch bei den Bildern selbst auch das Rating, bzw. bei dir heißt die Spalte `prozent`? Den höchsten Prozentwert den ein Bild eines users hat schreibst du eben in die zusätzliche Spalte in der profiltabelle. So hast du für jeden user gleichzeitig die höchste Bildbewertung.
Beim Voten berechnest du dann einfach die neue prozentzahl von dem Bild das bevotet wurde und führst dann ein sql-update aus:
PHP
$sql = "
UPDATE
    profil
SET 
    top_foto_rating=".$new_prozent."
WHERE
    id=".$foto_besitzer_id."
    AND
    top_foto_rating<".$new_prozent;

was hier bei jetz nicht ganz verstehe ist ... das ja nur die prozentzahl in die neue spalte geschrieben wird ... aber nicht das beste foto des users!

Tags