[MYSQL] "Online User" anzeigen... Lösung über Sub-Select?

[FlorianR]

Hallo Zusammen,

ich möchte eine Seite erstellen die mir eine Liste an Benutzern ausgibt, die gerade online sind. Mit Name und Profilbild.

Das Script ist fertig und funktioniert auch.

Ich habe jetzt drei Tabellen die ich für die Ausgabe benötige:

user_online (Hier stehen die Benutzer drin, die online sind... bzw. die ids)
user (Alle Benutzerinfos)
user_bilder (Die Profilbilder der Benutzer)

Mein Problem ist eigentlich "nur", dass er nur das zuletzt hochgeladene Benutzerbild anzeigen soll. Und da weiss ich gerade irgendwie nicht weiter, wie ich das in meinem SQL-Statement unterbringen soll.

Folgender Ansatz:

$sql = "SELECT uo.*, ub.thumb, u.name, u.user_id FROM user_online AS uo".
		$sql = $sql. " LEFT JOIN user AS u".
		$sql = $sql. " ON uo.user_id = u.user_id".
		$sql = $sql. " LEFT JOIN user_bilder AS ub".
		$sql = $sql. " ON ub.user_id =  u.user_id".
		$sql = $sql. " GROUP BY u.user_id".
		$sql = $sql. " ORDER BY uo.zeit DESC";

Dieser Ansatz klappt, mit Ausnahme der Sortierung des Profilbildes (er nimmt glaube ich das erste).

Ich habe dann noch überlegt, ob man das irgendwie mit Sub-Selects lösen könnte. Hier habe ich allerdings keine Erfahrung... es klappt gar nicht ;-)

sql = "SELECT uo.*, ".
		$sql = $sql. " (SELECT u.name, u.user_id FROM user AS u WHERE u.user_id = uo.user_id),".
		$sql = $sql. " (SELECT ub.thumb FROM user_bilder AS ub WHERE ub.user_id = uo.user_id ORDER BY ub.id DESC)".
		$sql = $sql. " FROM user_online AS uo".
		$sql = $sql. " ORDER BY uo.zeit DESC";

Ich kriege dann die Ausgabe "Could not execute SQL query: Operand should contain 1 column(s)".

Habt ihr vielleicht eine Idee, wie man das schön sauber lösen könnte?
Ich freue mich auf eure Antworten.

Gruß,
Florian

 

[Yaslaw]

Ich hatte im März mal eine Wiki-Seite diesem Thema gewidmet
MySQL Aktuelle Einträge pro Gruppe auslesen

 

[FlorianR]

Ich glaube jetzt habe ich es hinbekommen...

Folgender SQL-Code:

$sql = "SELECT uo.*, u.name, u.regdate, ub.thumb FROM user_online AS uo".
		$sql = $sql. " INNER JOIN (SELECT name, user_id, regdate FROM user) AS u ON u.user_id = uo.user_id".
		$sql = $sql. " INNER JOIN (SELECT thumb, user_id FROM user_bilder ORDER BY id DESC LIMIT 0,1) AS ub ON ub.user_id = u.user_id".
		$sql = $sql. " ORDER BY uo.zeit DESC";

Gibt es daran etwas auszusetzen?

 

[Yaslaw]

Jepp, hab ich.

Ich hab dein SQL mal lesbar formtiert.

SELECT 
	uo.*, 
	u.name, 
	u.regdate, 
	ub.thumb 
FROM 
	user_online AS uo
	INNER JOIN (
				SELECT 
					name, 
					user_id, 
					regdate 
				FROM
					user
	) AS u 
		ON u.user_id = uo.user_id
	INNER JOIN (
				SELECT 
					thumb, 
					user_id 
				FROM 
					user_bilder
				ORDER BY
					id DESC 
				LIMIT 0,1
	) AS ub 
		ON ub.user_id = u.user_id
ORDER BY
	uo.zeit DESC

Nun fällt auf
item: von ub nimmst du einfach den letzten Eintrag, ungeachter der user_id. Wenn du Pech hast (und das wird meistens der Fall sein), dann wird der letzte Eintag nicht die user_id haben die du haben willst.
item: ub sollte mit einem LEFT JOIN angehängt werden, falls ein User kein Thumb hat.

SELECT 
	uo.*, 
	u.name, 
	u.regdate, 
	--Im Falle, dass kein Thumb gefunden wird ein Standart ausgeben
	IFNULL(ub.thumb, 'n/a') AS thumb
FROM 
	user_online AS uo
	INNER JOIN (
				SELECT 
					name, 
					user_id, 
					regdate 
				FROM
					user
	) AS u 
		ON u.user_id = uo.user_id
	-- ändern in ein LEFT JOIN
	LEFT JOIN (
				SELECT 
					ub_orig.thumb, 
					ub_orig.user_id 
				FROM 
					user_bilder AS ub_orig,
					-- Den Letzten Eitrag pro user_id herausfinden
					(
						SELECT
							user_id,
							MAX(id) AS selected_id
						FROM
							user_bilder
						GROUP BY
							user_id
					) AS ub_max
				WHERE
					ub_orig.user_id = ub_max.user_id
					AND ub_orig.id = ub_max.selected_id
				ORDER BY
					id DESC
	) AS ub 
		ON ub.user_id = u.user_id
ORDER BY
	uo.zeit DESC;

Wenn du grössere Datenmengen hast, lohnt es sich ev. im ub_max noxh die online-Tabelle anzuhängen um die Daten zu reduzieren

					-- Den Letzten Eitrag pro user_id herausfinden
					-- Falls du viele User hast, hier ebenfalls die user_online-Tabelle anknüpfen
					-- um die Datenmenge zu reduzieren					
					(
						SELECT
							user_bilder.user_id,
							MAX(user_bilder.id) AS selected_id
						FROM
							user_bilder,
							INNER JOIN user_online
								ON user_bilder.user_id = user_online.user_id
						GROUP BY
							user_id
					) AS ub_max

 

[FlorianR]

Stimmt! Fiel mir nicht auf, da ich nur einen Nutzer zum testen habe. Danke für den Denkanstoß.
In Zeile 37 ist ein kleiner Tipp-Fehler (ub_orig_id = ub_orig.id), falls jemand die Abfrage benutzen will.

Vielen Dank soweit Eigentlich Wahnsinn wie mächtig SQL ist.

 

[Yaslaw]

Eigentlich Wahnsinn wie mächtig SQL ist.

Das war noch gar nix. SQL kann noch viel mehr....

Den Tippfehler hab ich korrigiert. Danke.

Nachtrag: Das LIMIT muss man natürlich auch entfernen - ich mach das im obigen Post gleich noch