[MySQL][PHP] Pobranie nieprzeczytanych wiadomości użytkownika Opcje

[gogomania]

Witam,
próbuję stworzyć zapytanie, które pobierze wiadomości obecnie zalogowanego użytkownika.

Struktura tabel:

[SQL] pobierz, plaintext 
CREATE TABLE `konwersacje` (
  `konwersacja_id` int(8) NOT NULL,
  `konwersacja_temat` varchar(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `konwersacje`
  ADD PRIMARY KEY (`konwersacja_id`);
ALTER TABLE `konwersacje`
  MODIFY `konwersacja_id` int(8) NOT NULL AUTO_INCREMENT;
 
CREATE TABLE `konwersacje_czlonkowie` (
  `id` int(11) NOT NULL,
  `konwersacja_id` int(8) NOT NULL,
  `uzytkownik_id` int(11) NOT NULL,
  `konwersacja_ostatnio_ogladana` int(10) NOT NULL,
  `konwersacja_usunieta` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `konwersacje_czlonkowie`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unikalny` (`konwersacja_id`,`uzytkownik_id`),
  ADD KEY `FK_uzytkownicy_konwersacje_czlonkowie1` (`uzytkownik_id`);
ALTER TABLE `konwersacje_czlonkowie`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `konwersacje_czlonkowie`
  ADD CONSTRAINT `FK_uzytkownicy_konwersacje_czlonkowie1` FOREIGN KEY (`uzytkownik_id`) REFERENCES `uzytkownicy` (`uzytkownik_id`) ON UPDATE CASCADE;
 
CREATE TABLE `konwersacje_wiadomosci` (
  `wiadomosc_id` int(10) NOT NULL,
  `konwersacja_id` int(8) NOT NULL,
  `uzytkownik_id` int(11) NOT NULL,
  `data_wyslania` int(10) NOT NULL,
  `wiadomosc_ogladana` int(1) NOT NULL DEFAULT '0',
  `wiadomosc_tresc` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `konwersacje_wiadomosci`
  ADD PRIMARY KEY (`wiadomosc_id`),
  ADD KEY `konwersacja_id` (`konwersacja_id`),
  ADD KEY `uzytkownik_id` (`uzytkownik_id`);
ALTER TABLE `konwersacje_wiadomosci`
  MODIFY `wiadomosc_id` int(10) NOT NULL AUTO_INCREMENT;
ALTER TABLE `konwersacje_wiadomosci`
  ADD CONSTRAINT `FK_uzytkownicy_konwersacje_wiadomosci` FOREIGN KEY (`uzytkownik_id`) REFERENCES `uzytkownicy` (`uzytkownik_id`) ON UPDATE CASCADE;
 
CREATE TABLE `uzytkownicy` (
  `uzytkownik_id` int(11) NOT NULL,
  `login` varchar(32) NOT NULL,
  `haslo` varchar(255) NOT NULL,
  `imie` varchar(55) NOT NULL,
  `nazwisko` varchar(80) NOT NULL,
  `tel_komorkowy` varchar(25) NOT NULL DEFAULT '',
  `tel_stacjonarny` varchar(25) NOT NULL DEFAULT '',
  `email` varchar(200) NOT NULL,
  `firma_id` int(1) NOT NULL DEFAULT '0',
  `pozwol_email` int(1) NOT NULL DEFAULT '0',
  `uprawnienia` int(1) NOT NULL DEFAULT '0',
  `ostatnie_logowanie` datetime DEFAULT NULL,
  `zdjecie` varchar(150) DEFAULT NULL,
  `typ` enum('Normalny','Pracownik','Administrator','Super-Administrator') NOT NULL DEFAULT 'Normalny',
  `aktywny` int(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `uzytkownicy`
  ADD PRIMARY KEY (`uzytkownik_id`),
  ADD UNIQUE KEY `login` (`login`),
  ADD KEY `firma_id` (`firma_id`);
ALTER TABLE `uzytkownicy`
  MODIFY `uzytkownik_id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `uzytkownicy`
  ADD CONSTRAINT `FK_firmy_uzytkownicy` FOREIGN KEY (`firma_id`) REFERENCES `firmy` (`firma_id`) ON UPDATE CASCADE;
[SQL] pobierz, plaintext 

I przyznam, że się pogubiłem. Czy ktoś może mi pomóc/nakierować?
funkcja nad którą pracuję:

[PHP] pobierz, plaintext 
function zlicz_wiadomosci_nieprzeczytane($uzytkownik_id) {
	global $connect;
 
	$uzytkownik_id = (int)$uzytkownik_id;
 
	$sql = "SELECT
				COUNT(konwersacje_wiadomosci.wiadomosc_ogladana) AS nieczytana
			FROM konwersacje_wiadomosci, konwersacje_czlonkowie
			WHERE konwersacje_wiadomosci.uzytkownik_id = {$_SESSION['uzytkownik_id']} 
			AND konwersacje_wiadomosci.wiadomosc_ogladana = 0
			AND konwersacje_czlonkowie.konwersacja_ostatnio_ogladana = 0
	";
 
	$wynik = mysqli_query($connect, $sql);
 
	if($wynik === FALSE) { 
		die(mysqli_error($connect));
	} else {
		if($row = mysqli_fetch_row($wynik)) {
			return $row[0];
		}
	}
 
}
[PHP] pobierz, plaintext 

[Niree]

[PHP] pobierz, plaintext 
$uzytkownik_id = (int)$uzytkownik_id;
 
	$sql = "SELECT
				COUNT(konwersacje_wiadomosci.wiadomosc_ogladana) AS nieczytana
			FROM konwersacje_wiadomosci, konwersacje_czlonkowie
			WHERE konwersacje_wiadomosci.uzytkownik_id = {$_SESSION['uzytkownik_id']} 
			AND konwersacje_wiadomosci.wiadomosc_ogladana = 0
			AND konwersacje_czlonkowie.konwersacja_ostatnio_ogladana = 0
	";
[PHP] pobierz, plaintext 

Dlaczego w pytaniu do bazy dajesz $_SESSION zamiast użyć argumentu z funkcji czyli $uzytkownik_id?

Poza tym daj sobie:

[PHP] pobierz, plaintext 
echo <<< html
SELECT
				COUNT(konwersacje_wiadomosci.wiadomosc_ogladana) AS nieczytana
			FROM konwersacje_wiadomosci, konwersacje_czlonkowie
			WHERE konwersacje_wiadomosci.uzytkownik_id = {$_SESSION['uzytkownik_id']} 
			AND konwersacje_wiadomosci.wiadomosc_ogladana = 0
			AND konwersacje_czlonkowie.konwersacja_ostatnio_ogladana = 0
html;
[PHP] pobierz, plaintext 

i zobacz co Ci w ogóle wyskoczy, czy zapytanie jest prawidłowe.

Przy $wynik = mysqli_query($connect, $sql); daj

[PHP] pobierz, plaintext 
$wynik = mysqli_query($connect, $sql) or die(mysqli_error($connect));
[PHP] pobierz, plaintext 

[gogomania]

Dlaczego w pytaniu do bazy dajesz $_SESSION zamiast użyć argumentu z funkcji czyli $uzytkownik_id?

Na chwilę obecną to moja robocza wersja, ale to nic nie zmienia - zapytanie wykonuje się bez błędów... Ale jest nieprawidłowe, właśnie przy nim potrzebuję pomocy.

By podejrzeć prawidłowe zapytanie wstawiam

[PHP] pobierz, plaintext 
echo $sql;
[PHP] pobierz, plaintext 

w funkcji następnie sprawdzam w MySQL i błędów nie ma.

PS.

[PHP] pobierz, plaintext 
 $wynik = mysqli_query($connect, $sql) or die(mysqli_error($connect));
[PHP] pobierz, plaintext 

mam użyte w funkcji - pierwszy post linia 17.

Problemem jest z ułożenie właściwego zapytania - nie potrafię tego ogarnąć.

Ten post edytował gogomania 5.05.2017, 08:28:01

[bostaf]

Nieprzeczytane wiadomości dla danego użytkownika tak?

Mógłbyś wyjaśnić strukturę tabeli "konwersacje_wiadomosci"? Nie jestem pewien, czy dobrze ją rozumiem.

[gogomania]

Nieprzeczytane wiadomości dla danego użytkownika tak?

Mógłbyś wyjaśnić strukturę tabeli "konwersacje_wiadomosci"? Nie jestem pewien, czy dobrze ją rozumiem.

Dokładnie - nieprzeczytane wiadomości ze wszystkich konwersacji dla zalogowanego użytkownika.
np. Ja jestem zalogowany jako 'admin' i mam trzy rozpoczęte konwersacje i w pierwszej mam 2 nowe wiadomości w drugiej mam 3 a w trzeciej bez zmian, co w sumie daje 5 nowych wiadomości.

`konwersacje_wiadomosci`
wiadomosc_id (int 10 oraz AI)
konwersacja_id (int 8) - id konwersacji, który jest też użyty w pozostałych tabelach
uzytkownik_id (int 11) - rejestrowany nadawca wiadomości (jego np. $_SESSION['uzytkownik_id'])
data_wyslania (int 10) - data wysłanej wiadomości
wiadomosc_oglada (int 1) - tą kolumnę dodałem by rejestrować 0 / 1 dla nieodczytana/odczytana ale nie wiem czy jest sens (może jest możliwe wyciągnięcie tego samego z dat?)
wiadomosc_tresc (text) - wiadomo

Głównym zadaniem tabeli jest rejestrowanie pojedynczej wysłanej wiadomości użytkownika. Np. 'admin' wysyła wiadomość do 'test_1' i 'test_2' wówczas w tabeli konwersacje_wiadomosci zostanie zarejestrowana wiadomość tylko raz, a w tabeli konwersacje_czlonkowie dodane zostaną 3 nowe wiersze z użytkownikami tej konwersacji oraz id konwersacji.

Mam nadzieję, że w pełni odpowiedziałem na pytanie i szybko uzyskam pomoc.

[bostaf]

Hmm w takiej postaci nie ma możliwości ustalenia, kto jaką wiadomość przeczytał. Brakuje jednoznacznej informacji wskazującej, że dany użytkownik przeczytał daną wiadomość.

Masz "konwersacja_ostatnio_ogladana" w tabeli "konwersacje_czlonkowie", ale to tylko wskazuje kiedy dana konwersacja została otwarta w widoku do odczytu.

Masz "wiadomosc_ogladana" w "konwersacje_wiadomosci", ale to też nie wskazuje KTO oglądał daną wiadomość. Tylko tyle, że ktoś oglądał i już nie jest globalnie nieprzeczytana.

Jedyne co można z takimi danymi zrobić, to pokazać pierwszemu zalogowanemu uczestnikowi danej konwersacji, że ma N nieprzeczytanych wiadomości. W momencie jak ten użytkownik otworzy konwersację, to znaczniki się ustawią na 1 i kolejny użytkownik już będzie miał status "wszystko przeczytane".

Musiałbyś dodać jakąś tabelę, która powiąże id_wiadomosci, id_uzytkownika polem "przeczytana" (np.: "wiadomosci_przeczytane_przez"), i dopisywać tam rekordy w momencie otwarcia wiadomości do odczytu. Dodatkowo, autor wiadomości też tam powinien być dopisany w momencie wysyłania wiadomości.
Wtedy określenie ilości nieprzeczytanych wiadomości sprowadzałoby się do obliczenia różnicy a - b, gdzie:
a: ilość wszystkich wiadomości we wszystkich konwersacjach dla danego użytkownika
b: ilość wszystkich rekordów w tabeli "wiadomosci_przeczytane_przez" policzona dla id_wiadomosci z puli liczonej w "a"

To zadziała pod warunkiem, że rozumiesz że:

Np. 'admin' wysyła wiadomość do 'test_1' i 'test_2' [...] a w tabeli konwersacje_czlonkowie dodane zostaną 3 nowe wiersze z użytkownikami tej konwersacji oraz id konwersacji.

ta logika MOŻE spowodować pojawienie się duplikatów, i że programistycznie nie dopuszczasz do pojawienia się takich duplikatów.

Tak mi się wydaje.
Ale ogólnie na twoim miejscu mocno przemyślałbym ten model danych. Nie jestem pewien czy "konwersacja" jest tu w ogóle potrzebna, bo konwersację można ustalić dynamicznie na podstawie analizy łańcucha wiadomości:
1. Jedna wiadomość to logicznie już konwersacja. Bez odpowiedzi, ale konwersacja.
2. Dwie i więcej wiadomości to już porządna konwersacja, której początek, ciąg i koniec można ustalić dodając pole "w_odpowiedzi_na" przechowujące id_wiadomosci poprzedniej, ale jakiejkolwiek innej....

[gogomania]

W pierwszej kolejności dziękuję za poświęcony czas na rozwiązanie problemu.

Wrócę do początku tego wątku bo wydaje mi się, że nie wyraziłem się jasno stąd powstałe nieścisłości.


Działanie przepływu wiadomości dla konwersacji od użytkownika do użytkownika/ów mam już opracowane w pełni, w zasadzie wszystko mam już zrobione. Pozostał tylko problem a w zasadzie szczegół ze zliczeniem wiadomości nieprzeczytanych.

Nie chodzi mi tutaj o odczytanie pojedynczej wiadomości bo to by kłóciło się z tym co już mam.
Wywnioskowałem, że piszesz o stworzeniu mechanizmu gdyż jak się domyślam, założyłeś, że takiego nie mam. Otóż jest wszystko i ładnie działa. Jedynie pozostaje ta nieszczęsna kwestia zapytania SQL by pobrać wiadomości nieprzeczytane dla zalogowanego obecnie użytkownika ze wszystkich konwersacji w funkcji którą podałem w pierwszym poście.

[bostaf]

W takim razie zacytuję sedno tego, co napisałem w poprzednim poście:

Hmm w takiej postaci nie ma możliwości ustalenia, kto jaką wiadomość przeczytał. Brakuje jednoznacznej informacji wskazującej, że dany użytkownik przeczytał daną wiadomość.

Miałem tu na myśli, że w bazie danych nie masz w tej chwili informacji o tym kto jaką wiadomość przeczytał lub nie. Czyli nie skonstruujesz zapytania SQL które odpowie na Twoje pytanie.
Reszta tamtego posta to szybka analiza tego co masz dostępne i propozycja (jedna z kilku opcji) w jaki sposób taką funkcjonalność wdrożyć. Liczba nieprzeczytanych to przykład; taką samą techniką można te wiadomości pobrać.

[gogomania]

W takim razie zacytuję sedno tego, co napisałem w poprzednim poście:

Miałem tu na myśli, że w bazie danych nie masz w tej chwili informacji o tym kto jaką wiadomość przeczytał lub nie. Czyli nie skonstruujesz zapytania SQL które odpowie na Twoje pytanie.
Reszta tamtego posta to szybka analiza tego co masz dostępne i propozycja (jedna z kilku opcji) w jaki sposób taką funkcjonalność wdrożyć. Liczba nieprzeczytanych to przykład; taką samą techniką można te wiadomości pobrać.

Przepraszam, może to nie niemożliwe, ale mam tą funkcjonalność tj. mam tą informację która wiadomość została przeczytana:

[PHP] pobierz, plaintext 
function pobierz_sume_konwersacji() {
	global $connect;
	$sql = "SELECT
				  k.konwersacja_id
				, k.konwersacja_temat
				, MAX(kw.data_wyslania) AS konwersacja_ostatnia_odpowiedz
				, MAX(kw.data_wyslania) > (kc.konwersacja_ostatnio_ogladana) AS konwersacja_nieprzeczytana
			FROM konwersacje AS k
			LEFT JOIN konwersacje_wiadomosci AS kw ON k.konwersacja_id = kw.konwersacja_id
			INNER JOIN konwersacje_czlonkowie AS kc ON k.konwersacja_id = kc.konwersacja_id
			WHERE kc.uzytkownik_id = {$_SESSION['uzytkownik_id']} 
			AND kc.konwersacja_usunieta = 0 
			GROUP BY k.konwersacja_id 
			ORDER BY konwersacja_ostatnia_odpowiedz DESC
			";
	$wynik = mysqli_query($connect, $sql);
	$konwersacje = array();
	while ($wiersz = $wynik->fetch_assoc()) {
		$konwersacje[] = array(
			'id' 						=> $wiersz['konwersacja_id'],
			'temat' 					=> $wiersz['konwersacja_temat'],
			'ostatnia_odpowiedz' 		=> $wiersz['konwersacja_ostatnia_odpowiedz'],
			'wiadomosci_nieprzeczytane' => ($wiersz['konwersacja_nieprzeczytana'] == 1)
		);
	}
	return $konwersacje;
}
[PHP] pobierz, plaintext 

W tej funkcji pobieram 'konwersacja_nieprzeczytana' i wyświetlam na liście konwersacji informację np. "Nowe" co jest jednoznaczne z tym, że są nowe wiadomości w konwersacji.

[PHP] pobierz, plaintext 
function pobierz_wiadomosci_konwersacji($konwersacja_id) {
	global $connect;
	$konwersacja_id = (int)$konwersacja_id;
	$sql = "SELECT
				  kw.data_wyslania
				, kw.data_wyslania > kc.konwersacja_ostatnio_ogladana AS konwersacja_nieprzeczytana
				, kw.wiadomosc_tresc
				, CONCAT(u1.imie,' ',u1.nazwisko) AS nadawca
				, u1.uzytkownik_id AS nadawca_id
				, u1.zdjecie
			FROM konwersacje_wiadomosci AS kw
			INNER JOIN konwersacje_czlonkowie AS kc ON kw.konwersacja_id = kc.konwersacja_id
			INNER JOIN uzytkownicy AS u1 ON u1.uzytkownik_id = kw.uzytkownik_id
			WHERE kw.konwersacja_id = {$konwersacja_id}
			AND kc.uzytkownik_id = {$_SESSION['uzytkownik_id']}
			ORDER BY kw.data_wyslania DESC";
	$wynik = mysqli_query($connect, $sql);
	$wiadomosci = array();
	while ($wiersz = $wynik->fetch_assoc()) {
		$wiadomosci[] = array(
			'data' 				=> $wiersz['data_wyslania'],
			'nadawca' 			=> $wiersz['nadawca'],
			'nadawca_id' 		=> $wiersz['nadawca_id'],
			'zdjecie'			=> $wiersz['zdjecie'],
			'nieprzeczytana' 	=> $wiersz['konwersacja_nieprzeczytana'],
			'tresc' 			=> $wiersz['wiadomosc_tresc'],
		);
	}
	return $wiadomosci;
}
[PHP] pobierz, plaintext 

Tutaj między innymi pobieram ile jest wiadomosci nieprzeczytanych dla danej konwersacji...

Ale brakuje mi jak wspomniałem funckji która wyświetli mi wszystkie nieprzeczytane wiadomości we wszystkich konwersacjach użytkownika. Tzn. zapytania SQL. Jakieś pomysły jak powinno składać wyglądać te zapytanie?

[pmir13]

Zakładając że wiadomość nieprzeczytana to taka, która ma datę dodania większą (późniejszą) niż ostatnia data oglądania konwersacji przez danego użytkownika i znajduje się w tej konwersacji to można:

[SQL] pobierz, plaintext 
SELECT count(*) AS nieprzeczytane
FROM konwersacje_czlonkowie kc JOIN konwersacje_wiadomosci kw 
ON kc.konwersacja_id = kw.konwersacja_id
AND kc.konwersacja_ostatnio_ogladana < kw.data_wyslania
WHERE kc.uzytkownik_id = ?
[SQL] pobierz, plaintext 

Pole wiadomosc_ogladana w tabeli konwersacje_wiadomosci jest do usunięcia, nie ma tam sensu.
Nie sprawdzasz też nigdzie czy konwersacja została usunięta, ale to już musisz sam przemyśleć jak to zrobić, bo być może to pole powinno być w tabeli konwersacje albo też użytkownik może chcieć się wypisać z danej konwersacji i tylko dla niego jest ona niewidoczna (usunięta) podczas gdy inni mogą dalej w niej pisać i wtedy to pole jest w dobrym miejscu, choć dziwnie nazwane.

[gogomania]

Zakładając że wiadomość nieprzeczytana to taka, która ma datę dodania większą (późniejszą) niż ostatnia data oglądania konwersacji przez danego użytkownika i znajduje się w tej konwersacji to można:

[SQL] pobierz, plaintext 
SELECT count(*) AS nieprzeczytane
FROM konwersacje_czlonkowie kc JOIN konwersacje_wiadomosci kw 
ON kc.konwersacja_id = kw.konwersacja_id
AND kc.konwersacja_ostatnio_ogladana < kw.data_wyslania
WHERE kc.uzytkownik_id = ?
[SQL] pobierz, plaintext 

Pole wiadomosc_ogladana w tabeli konwersacje_wiadomosci jest do usunięcia, nie ma tam sensu.
Nie sprawdzasz też nigdzie czy konwersacja została usunięta, ale to już musisz sam przemyśleć jak to zrobić, bo być może to pole powinno być w tabeli konwersacje albo też użytkownik może chcieć się wypisać z danej konwersacji i tylko dla niego jest ona niewidoczna (usunięta) podczas gdy inni mogą dalej w niej pisać i wtedy to pole jest w dobrym miejscu, choć dziwnie nazwane.

Jesteś mega! O to mi właśnie chodziło. Niby takie proste zapytanie a ja kombinowałem zupełnie w innym kierunku... Dziękuję Ci bardzo.

Btw. Sprawdzam czy konwersacja została usunięta w innej funcji, tutaj (cała funkcja we wcześniejszym poście):

[PHP] pobierz, plaintext 
function pobierz_sume_konwersacji() {
...
AND kc.konwersacja_usunieta = 0 
...
}
[PHP] pobierz, plaintext 

Jeżeli jest `konwersacja_usunieta` = 1 wówczas nie pokazuję jej na liście konwersacji zalogowanego użytkownika.

Ogólnie mam już wszystko - dzięki temu zapytaniu, które napisałeś!

Dziękuję wszystkim za udzielone wsparcie.