✔ Hilfe zu Mysql query gesucht

C

chans

Mitglied
Guten Abend zusammen,

Ich erstelle derzeit ein kleines Messenger System (im Facebook Stil) - relativ simple mit PHP5 und Mysql. Leider stecke ich gerade ein wenig fest und hoffe, dass mir jemand einen kleinen Denkanschub in die richtige Richtung geben kann.

Zu meinem Projekt:
Die Datenbank samt Tabelle steht bereits, derzeit hänge ich allerdings fest bei der Abfrage aller verfügbaren Nachrichten. Ich nutze eine Tabelle "user_messages", welche die Nachrichten der User enthält.

Die Tabelle hat folgende Felder:
userid, sender, recipient, message, date, archived, status und deleted

Nun möchte ich folgendes erreichen: Tom(sender) schickt eine Nachricht an Laura(recipient) - Laura antwortet daraufhin auf diese Nachricht. Somit entstehen 2 Tabelleneinträge: sender: Tom (user id = 1) und Laura (user id = 2)

Ich möchte erreichen, dass diese Nachrichten in meiner Nachrichten Übersicht angezeigt werden, allerdings nur jeweils ein Ergebnis pro Person mit der man eine Konversation führt (Facebook User wissen sicher was gemeint ist).

In diesem Falle, würde mir (Tom) in meiner jetzigen Übersicht der Name von Laura auftauchen und darunter die letzte Nachricht aus dieser Konversation angezeigt werden. Soweit bin ich bereits, allerdings fehlt mir noch eines und ich komme einfach nicht auf die Lösung.

Beispiel: Ich (Tom) schicke eine Nachricht an Laura. Laura hat aber noch nicht geantwortet - trotzdem möchte ich, das meine Nachricht an Laura in meiner Übersicht erscheint. Dies tut Sie allerdings nicht, sondern erst dann, wenn Laura antwortet.

Hier meine SQL Abfrage:
Code: 
SELECT 
	id,
	sender,
	recipient,
	message,
	UNIX_TIMESTAMP(date) AS date,
	status
FROM 
	(
		select 
			* 
		FROM 
			user_messages  
		ORDER BY 
			id DESC 
	) AS conversation
WHERE 
	archived = '0'
	&& deleted = '0'
	&& recipient = '".mysql_real_escape_string($_SESSION['user'])."'
	|| sender = '".mysql_real_escape_string($_SESSION['user'])."'
GROUP BY 
	recipient,
    sender
ORDER BY 
	id DESC,
	status = '0'

Ich hoffe jemand hat den richtigen Tipp für mich!

Vielen Dank im Voraus!

Chans
 
Zuletzt bearbeitet:
Also wenn du dich an Facebook orientierst, willst du doch die gesamte Koversation abfragen.
[PseudoCode]
Nehme *
Wo (Sender = User
Und nicht SenderGelöscht = 1)
(Oder Empfänger = User
Und nicht EmpfängerGelöscht 1)
Sortiere bei Datum
[Pedeudocode]

Nr, Narchicht, TagZeit, Empfänger, Sender, EmpfängerGelöscht, SenderGelöscht, Gesehen

Weiterhin solltest du nicht den UserNamen sondern die ID zu identifzierung nehmen.
Namen können sich ändern, IDs werden dass nicht tun.
Außerdem kannst du dann vorher die Ses ID in eine Variable packen, wenn diese numerisch ist, dann hast du nicht das Problem mit den escapes.
Auch sollte bei den Messages ID für die Sender und Empfänger genutzt werden.
Du kannst ja dann einfach ein Join auf deine UserTB machen.
 
Hi GelbesKüken,

zuerst einmal bedanke ich mich für deine Antwort.

Richtig, ich möchte die gesamte Konversation abfragen, welche ich aus "sender" und "recipient" zusammenbaue. Diese beiden Felder sind übrigens mit user-ids gefüllt, nicht mit Namen (sorry das hätte ich vorher erwähnen sollen).

Ich erkläre noch einmal kurz die Felder:
id (Nachrichten ID), sender (Absender ID), recipient (Empfänger ID), message (Text), Date (DATETIME), archived (INT), status (INT) und deleted (INT).

Die User-ID's sind natürlich mit der entsprechenden User-Tabelle verlinkt. Des Weiteren nutze ich das Tabellenformat "InnoDB", mit dem ich mit Hilfe von Indexen eine Beziehung zur User-DB herstelle. Selbst wenn dann die user-id sich ändern sollte (was so gut wie nie passieren wird) dann ist diese auch direkt in allen Nachrichten geändert (automatisch).

- - - - - - - - - -

Ich habe meine Abfrage noch einmal verändert mit allerdings mäßigem Erfolg (siehe oben). Jetzt erhalte ich zwar auch Ausgabe von Nachrichten, welche ich abgeschickt habe (ohne eine Antwort zu erhalten) - allerdings in einer separaten Nachricht. Ich kann zwar per Group By die Ausgabe zusammenfassen - aber das funktioniert nicht so wie ich es derzeit habe.

Hast du noch eine Idee Küken oder jemand anderes?
 
Zuletzt bearbeitet:
Wenn du sie groupst verschwinden doch die Einträge (sozusagen).

Code: 
SELECT 
    id,
    sender,
    recipient,
    message,
    UNIX_TIMESTAMP(date) AS date,
    status
FROM    user_messages  

WHERE 
    archived = '0'
    && deleted = '0'
    && recipient = '".mysql_real_escape_string($_SESSION['user'])."'
    || sender = '".mysql_real_escape_string($_SESSION['user'])."'
ORDER BY 
    id DESC

Sollte dir alle Narichten ausgeben.
Testest du es mit phpMyAdmin? Ob der SQL Code überhaupt valide ist.
Nicht dass dein PHP Script fehlerhaft ist.
In dieser Form musst du nämlich per while Schleife ausgeben.

Vielleicht habe ich aber auch nur nicht verstanden was du eig. willst.
 
GelbesKüken hat gesagt.:
Wenn du sie groupst verschwinden doch die Einträge.
Zum Vergrößern anklicken....

Genau das möchte ich erreichen, bzw. nicht das die Einträge komplett verschwinden - sondern eher auf einen User begrenzt werden.

Selbstverständlich habe ich die Query vorher auf der Mysql Konsole geprüft. Der PHP Code funktioniert auch einwandfrei, allerdings erhalte ich nicht das gewünschte Ergebnis.

Es ist schwierig zu erklären, aber wenn du mal im Facebook schaust unter deinen Nachrichten, dann sind dort alle Konversationen aufgeführt. Pro Person allerdings nur eine Ausgabe, in der quasi ein Ausschnitt der letzten Nachricht zu sehen ist.

Wenn es hilft, hier ist die komplette abfrage in PHP (Abfrage ist in einer Funktion - nicht wundern):
PHP: 
$getMessages = $mySQL->DoSelect(" 
		SELECT 
			id,
			sender,
			recipient,
			message,
			UNIX_TIMESTAMP(date) AS date,
			status
		FROM 
			(
				select 
					* 
				FROM 
					user_messages 
				ORDER BY 
					id DESC 
			) AS conversation
		WHERE 
			archived = '0'
			&& deleted = '0'
			&& recipient = '".fctRemoveSqlHTML($uid)."'
			|| sender = '".fctRemoveSqlHTML($uid)."'
		GROUP BY 
			sender,
            recipient
		ORDER BY 
			id DESC,
			status = '0'
	");
	$num = $mySQL->GetNum($getMessages);
	
	if(EMPTY($num))
	{
		$messages = 'Es sind keine Nachrichten vorhanden.';
	}
	else
	{
		$messages = '<ul class="messenger-list">';
		while($msg = $mySQL->GetArray($getMessages)) {
			$name = fctGetContactName($msg['sender']);
			$date = fctRelativeTime($msg['date'], $lang);
			
			$messages .= '<li>';
			$messages .= '<span style="float:right;">'.$date.'</span>';
			$messages .= '<a href="'.URL_HTTPS.'/messages.html?id='.$msg['sender'].'">';
			$messages .= '<h3>'.$name['firstname'].' '.$name['name'].'</h3>';
			$messages .= fctMessagePreview($msg['message'], 80);
			$messages .= '</a>';
			$messages .= '</li>';
		}
		$messages .= '</ul>';
	}
	
	return $messages;

Gruß
Chans
 
Zuletzt bearbeitet:
Das funktioniert, nur leider brauche ich pro Person eine Ausgabe. Leider Limitiert "Limit" die gesamte Abfrage auf 1. Eventuell ist meine Denkweise hier auch falsch, ich dachte man kann es mit einer Tabelle lösen, aber ich komme einfach nicht auf die Lösung :(

Aussehen sollte es so (bei der Ausgabe der while Schleife):

Meine Nachrichten:

Tom:
Text ...

Laura
Text ...

Thomas
Text ...

Wobei bei Text immer ein Ausschnitt aus der letzten Konversation auftauchen soll.
 
Also, ich hae kein Facebookaccount und muss mich darum an dem hier geschriebenen orientieren um dir zu helfen

item: Int-Felder auch als solche vergleichen. Also nicht mit '0' vergleichen sondern mit 0

item: Wazu dient das Feld status?

item: Deine Ausgabe so wie ich sie verstehe ist einfach (Ich sehe in deinem Ausgabebeispiel nicht wie das seine gekapselte Konversation sein soll)

SQL: 
SELECT
	id,
	sender,
	recipient,
	message,
	UNIX_TIMESTAMP(`date`) AS `date`,
	`status`
FROM
	user_messages
WHERE
	IFNULL(".mysql_real_escape_string($_SESSION['user'])." IN (recipient, sender), FALSE)
	AND archived = 0
	AND deleted = 0
ORDER BY
	`date`
 
Zuletzt bearbeitet von einem Moderator:
Hi Yaslaw,

danke für deine Antwort. Intfelder werden ab sofort ohne '' verglichen, danke für den Hinweis!

"Status" sagt aus, ob eine nachricht gelesen oder ungelesen ist, wobei 0 für ungelesen steht. Mit status würde ich dann im Endeffekt gerne erreichen, dass die Ausgabe der Konversationen nach id + "ungelesen" sortiert wird.

In meinem Beispiel versuche ich einfach die Konversation zwischen 2 Personen in eine Ausgabe zusammenzufassen. Demnach Ich (UserID = 1) habe mit Laura (UserID = 2) eine Konversation, welche aus meinetwegen 20 Nachrichten besteht. Ich möchte nicht, dass mir all diese Nachrichten in meiner Inbox angezeigt werden, sondern nur der Name der Person (in dem Fall Laura) und einen Ausschnitt aus der letzten Nachricht, die zwischen den beiden diskutiert wurde.

Hier mal ein Foto aus meiner Facebook-Nachrichtenübersicht, eventuell könnt ihr euch das dann besser vorstellen.
conversations.png

Du siehst, jede Person mit der ich geschrieben habe, taucht nur einmal auf in der Liste. Unter dem Namen der Person mit der ich schreibe, dann ein Auszug aus der letzten Nachricht dieser Konversation (egal ob von mir oder dem Partner).

Gruß
Chans
 
So auf die schnelle und nicht getestet
SQL: 
SELECT
	msg.user_2,
	msg.msg_date,
	msg.message,
	msg.status
FROM
	(
		SELECT
			user_1,
			user_2,
			MAX(msg_date) AS max_msg_date
		FROM
			(	-- Grunddaten
				SELECT
					{$user} AS user_1,
					IF(sender = {$user}, recipient, sender) AS user_2,
					UNIX_TIMESTAMP(`date`) AS msg_date
				FROM
					user_messages
				WHERE
				    IFNULL({$user) IN (recipient, sender), FALSE)
			) AS dat
		GROUP BY
			user_1,
			user_2
	) AS max_dat
	INNER JOIN 	( 	-- Grunddaten
			SELECT
				{$user} AS user_1,
				IF(sender = {$user}, recipient, sender) AS user_2,
				UNIX_TIMESTAMP(`date`) AS msg_date
			FROM
				user_messages
			WHERE
			    IFNULL({$user) IN (recipient, sender), FALSE)
		) AS msg
		ON msg.user_i = max_dat.user_1
		AND msg.user_2 = max_dat.user_2
		AND msg.msg_date = max_dat.max_msg_date
ORDER BY
	msg_date DESC

Erklärbär

Das folgende Subquery setzt immer $user als user_1 und das gegenüber auf $user_2.
SQL: 
-- Grunddaten
SELECT
	{$user} AS user_1,
	IF(sender = {$user}, recipient, sender) AS user_2,
	UNIX_TIMESTAMP(`date`) AS msg_date
FROM
	user_messages
WHERE
    IFNULL({$user) IN (recipient, sender), FALSE)

Mit dieser Grundlage wird per user_1/user_2-Kombination der aktuellste Datensatz ermittelt.
-> MySQL Aktuelle Einträge pro Gruppe auslesen
 
Zuletzt bearbeitet von einem Moderator:
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück