[PHP][SQL] Zapytanie dotyczące wyświetlania wpisów na talicy, coś jak Facebook Opcje

[lukaszk]

Witam, mam problem z zapytaniem
Mam tabelę tablica do której dodają się id dodanych filmów, galerii itd.
Założenie jest takie aby zapytanie wyświetlało to co użytkownik zaznaczył że ma być na tablicy.
Podstawowe treści, linki do fotek i filmów zawierają tabele filmy i galerie
id_dodany w tabeli tablica zawiera id filmu, galerii którą użytkownik chcę umieścić na tablicy.

mam taki problem - kiedy zapytanie dotyczyło tylko jednej tablicy (dla filmy) wszystko było ok
teraz dodałem tablice galerie i dla filmów dalej jest ok z tym że przy jednym rekordzie w tablicy galerie
wyświetla się tyle razy ile mam filmów czyli jeśli mam 7 filmów to mam i 7 razy ten sam wpis.
Proszę o pomoc jak to rozwiązać bo ja już długo się z tym bawię i nic

[PHP] pobierz, plaintext 
SELECT *
					FROM tablica a JOIN filmy b, galerie c
					WHERE a.id_dodany=b.id_film OR a.id_dodany=c.id_galeria ORDER BY id_dodany DESC";
 
[PHP] pobierz, plaintext 

[bostaf]

Przede wszystkim, w zapytaniu brakuje identyfikatora tablicy, i o ile ono faktycznie działa (nie sprawdzałem) to pobiera wszystkie powiązane wpisy z tablic. Druga sprawa - id_dodany jest w Twojej tabeli jednocześni identyfikatorem filmu i identyfikatorem galerii. Jeśli nie masz unikalnych identyfikatorów między tablicami "filmy" i "galerie" (a prawdopodobnie nie masz) to prędzej czy później natrafisz na problem jednakowego identyfikatora filmu i galerii na tablicy. Ale nie w tym główny problem.

Potrzebujesz kilku tablic wiążących id_tablicy użytkownika z id_zasobu (galerii, filmu, fotki, itd): "tablica_film", "tablica_galeria", "tablica_fotka". Doprowadzi to do tzw. normalizacji bazy danych. Jeśli znasz angielski to możesz zacząć naukę tu: http://en.wikipedia.org/wiki/Database_normalization. Jeśli nie, to tu: http://pl.wikipedia.org/wiki/Normalizacja_bazy_danych.

Czyli:

Kod

SELECT tf.id_tablica, tf.id_film, t.nazwa_tablicy, f.tytul_filmu
FROM tablica_film tf
JOIN filmy f ON f.id_film = tf.id_film
JOIN tablice t ON t.id_tablica = tf.id_tablica
WHERE tf.id_tablica = $id_tablicy

i tak samo dla galerii, fotek, itd.
Wyniki poszczególnych zapytań ładujesz do obiektów albo tablic PHP i przetwarzasz wedle życzenia. To jedno z możliwych rozwiązań. Wydaje mi się, że jedno z prostszych.

Ten post edytował bostaf 14.05.2012, 23:02:17

[lukaszk]

Dzięki i też zauważyłem problem z tym że id z tabeli filmy na pewno pokryje się z id galerie.
Dlatego kiedy tworzyłem taką bazę dodałem kolumnę "rodzaj" i zapytanie może wyglądać tak AND rodzaj='film' dla filmów i rodzaj='galeria'


Teraz zrbiłem coś takiego i nie działa nie wiem dlaczego "JOIN galerie t ON t.id_galeria = tf.id_dodany" bez tej linijki działa dla filmów prawidłowo.
Tabele mają tą samą strukturę (te same nazwy kolumn a nawet id się nie powtarza)

[PHP] pobierz, plaintext 
		SELECT *
		FROM tablica tf
		JOIN filmy f ON f.id_film = tf.id_dodany
		JOIN galerie t ON t.id_galeria = tf.id_dodany
		WHERE tf.id_uzytkownik = $id";
[PHP] pobierz, plaintext 

[bostaf]

To że dla samych filmów działa prawidłowo to szczęśliwy zbieg okoliczności W zapytaniu nie podałeś filtra, o którym wspomniałeś: kolumny "rodzaj".
Poza tym nadal zachęcam Cię do znormalizowania bazy, bo już natrafiłeś na pierwszy problem związany z jej brakiem. JOIN zwróci iloczyn (część wspólną) obu zbiorów (tablic). Kolejny JOIN z inną tablicą i wspólnym identyfikatorem jeszcze bardziej zawęzi wynik eliminując nawet poprzednie trafienia. To do niczego nie prowadzi. Rozwiązanie problemu Twoją metodą nie jest niemożliwe. Ale spędzanie czasu nad rozwiązaniem jest jak uczenie się wbijania gwoździ suwmiarką. Też się da. Ale łatwiej i poprawniej jest użyć młotka. Znormalizuj bazę. Jeśli poczytałeś i nadal nie bardzo wiesz jak to pokaz strukturę swoich tabel to podpowiem jak znormalizować.

[lukaszk]

Dziękuję za pomoc oto moje podstawowe tabele. Wczoraj czytałem i będę czytał i przeglądał przykłady ale proszę podaj mi na przykładzie
bo tak najlepiej zrozumieć.
Pozdrawiam

[PHP] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `galerie` (
  `id_galeria` int(3) NOT NULL auto_increment,
  `id_uzytkownik` tinyint(3) default NULL, // kogo jest ta galeria
  `tytul` varchar(200) collate utf8_polish_ci default NULL,
  `status` tinyint(4) NOT NULL,
  `link_miniaturka` varchar(100) collate utf8_polish_ci default NULL,
  `opis` text collate utf8_polish_ci,
 `data_szeroko` varchar(100) collate utf8_polish_ci default NULL,
  `data` date default NULL,
  PRIMARY KEY  (`id_galeria`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=137 ;
[PHP] pobierz, plaintext 

[PHP] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `filmy` (
  `id_film` int(3) NOT NULL auto_increment,
  `id_uzytkownik` tinyint(3) default NULL, // kogo jest ten film
  `tytul` varchar(200) collate utf8_polish_ci default NULL,
`status` tinyint(4) NOT NULL,
  `film` varchar(100) collate utf8_polish_ci default NULL,
  `opis` text collate utf8_polish_ci,
  `data_szeroko` varchar(100) collate utf8_polish_ci default NULL,
`data` date default NULL,
  PRIMARY KEY  (`id_film`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=42 ;
[PHP] pobierz, plaintext 

Poniżej znajduję się tabela tablica czyli chcę osiągnąć efekt coś jak na facebook.
Obecnie moje zapytanie wyświetla tylko moje wpisy a ja chcę jeszcze osiągnąć taki efekt aby moje wpisy widzieli mi znajomi a ja ich wpisy.
Nie wiem czy takie rozwiązanie trzeba planować na etapie budowy bazy czy poprzez odpowiednie zapytanie.

[PHP] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `tablica` (
  `id_tablica` int(11) NOT NULL auto_increment, // id tablicy nie potrzebuję z niego korzystać nigdzie 
  `id_dodany` int(3) default NULL,  // tu dodaje się ID dodanego filmu, galerii
  `id_uzytkownik` int(3) default NULL,         // id uzytkownika u którego mają być wyświetlane wpisy 
  `data` date default NULL,
  `czas` time default NULL,
  `rodzaj` varchar(50) collate utf8_polish_ci default NULL,
  `komentarz` varchar(500) collate utf8_polish_ci default NULL, // krótki komentarz użytkownika
  PRIMARY KEY  (`id_tablica`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=77 ;
[PHP] pobierz, plaintext 

W tej tabeli miałem podobny problem dlatego dla każdego różnego "media" jest oddzielna kolumna.
Do każdego filmu, galerii użytkownik może dodać komentarz.

[PHP] pobierz, plaintext 
 
CREATE TABLE IF NOT EXISTS `comments` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `id_film` int(11) default NULL,
  `id_galeria` int(11) default NULL,
  `dla_id` int(4) default NULL,         // dla kogo jeśli jest to komentarz do profilu
  `id_uzytkownik` int(3) default NULL, // id dodającego
  `koment` text character set utf8 collate utf8_polish_ci NOT NULL,
  `datat` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `status` tinyint(5) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=332 ;
[PHP] pobierz, plaintext 

Tu jeszcze tabela znajomi w której przechowuję zaproszenia.

[PHP] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `znajomi` (
  `id_zaproszenia` int(5) NOT NULL auto_increment,
  `id_user_1` int(5) NOT NULL,
  `id_user_2` int(5) NOT NULL,
  `accepted` int(3) NOT NULL,
  `data` date NOT NULL,
  `data_czas` datetime NOT NULL,
  PRIMARY KEY  (`id_zaproszenia`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=29 ;
[PHP] pobierz, plaintext 

[bostaf]

Zakładam, że że jeden użytkownik może mieć tylko i wyłącznie jedną tablicę i jedna tablica może należeć tylko i wyłącznie do jednego użytkownika. I nigdy się to nie zmieni. To ważne bo jeśli jest szansa, że któryś z tych dwóch warunków się zmieni, to warto już teraz przyjąć inną strukturę danych.
Jeśli chodzi o tabele mediów - "galerie", "filmy", itd. to jest OK.
Jeśli chodzi o tablice użytkowników: zamiast przechowywać w jednej tabeli wszystkie informacje o powiązanych mediach, zrób indywidualne tabele dla każdego: "tablice_galerie", "tablice_filmy", itd. W każdej z tych tabel będziesz przechowywał informację o tym, która galeria/film/itd jest przypięta do której tablicy (do którego użytkownika), np.:

Kod

tabela "tablice_galerie"
id_uzytkownik | id_galeria | data_przypiecia | komentarz

W momencie tworzenia nowego wpisu o galerii w tabeli "galerie" musisz utworzyć też wpis w tabeli "tablice_galerie". W ten sposób, domyślnie, autor wpisu o galerii będzie miał przypiętą galerię do swojej tablicy. Galerię istniejąca w systemie będą mogli dodawać do swoich tablic inni uzytkownicy - będziesz dodawał kolejny wpis z takim samym identyfikatorem galerii i identyfikatorem osoby dodającej. W ten sposób pozwolisz odpiąć galerię od tablicy nawet autorowi wpisu o galerii i będziesz miał możliwość pokazywania galerii znajomym znajomych itd.

Jak pokazać galerie znajomych na swojej tablicy? Przeszukując tabelę "znajomi". Z tą tabelą jest taki problem, że nie wiadomo w której kolumnie jest id aktualnie zalogowanego użytkownika. Ale to tylko mała niedogodność, z którą można sobie łatwo poradzić. Czyli, po pierwsze, potrzebujemy id użytkowników, którzy są znajomymi aktualnie zalogowanego użytkownika id X:

Kod

SELECT id_user_2 AS znajomy FROM znajomi WHERE id_user_1 = X
UNION -- "union" elegancko połączy oba zapytania bez duplikatów
SELECT id_user_1 AS znajomy FROM znajomi WHERE id_user_2 = X

W wyniku dostaniesz kolumnę o nazwie "znajomy" wypełnioną identyfikatorami znajomych aktualnie zalogowanego użytkownika. Wyszukanie galerii obecnych na ich tablicach jest czystą formalnościa:

Kod

SELECT id_galeria
FROM tablice_galerie
WHERE id_uzytkownik IN (
    SELECT id_user_2 AS znajomy FROM znajomi WHERE id_user_1 = X
    UNION
    SELECT id_user_1 AS znajomy FROM znajomi WHERE id_user_2 = X
)

W wyniku dostaniesz identyfikatory galerii przypiętych do tablic wszystkich znajomych aktualnego usera. No OK, ale co jeśli niektóre z tych galerii są też przypięte do tablicy aktualnego usera? Nie chcemy ich dwa razy pokazywać, no nie? Do zapytania wyciągającego identyfikatory użytkowników dodajemy id aktualnego usera:

Kod

SELECT id_galeria
FROM tablice_galerie
WHERE id_uzytkownik IN (
    SELECT id_user_2 AS znajomy FROM znajomi WHERE id_user_1 = X
    UNION
    SELECT id_user_1 AS znajomy FROM znajomi WHERE id_user_2 = X
    UNION
    SELECT X
)

No i mamy identyfikatory wszystkich galerii, które aktualny user powinien zobaczyć na swojej tablicy: zarówno tych przypiętych do swojej tablicy jak i tych przypietych do tablic swoich znajomych; bez duplikatów. Do tego wyniku dodamy dane galerii:

Kod

SELECT
    tg.id_galeria,
    g.id_uzytkownik AS id_autora_galerii, -- "autora" w sensie twórcy wpisu o galerii
    g.data_przypiecia,
    g.komentarz AS komentarz_autora_galerii -- "autora" w sensie twórcy wpisu o galerii
FROM tablice_galerie tg
JOIN galerie g ON g.id_galeria = tg.id_galeria
WHERE tg.id_uzytkownik IN (
    SELECT id_user_2 AS znajomy FROM znajomi WHERE id_user_1 = X
    UNION
    SELECT id_user_1 AS znajomy FROM znajomi WHERE id_user_2 = X
    UNION
    SELECT X
)

W wyniku otrzymasz takie zestawienie:

Kod

   id_galeria | id_autora_galerii |     data_przypiecia | komentarz_autora_galerii
  ------------+-------------------+---------------------+--------------------------
           23 |               483 | 2012-03-12 12:12:32 | Fajowa
          287 |                12 | 2012-01-26 06:30:43 | Polecam
           35 |                12 | 2012-01-29 09:45:01 | Polecam też

To są same galerie. Podobnie robisz z filmami i innymi mediami. Poszczególne wyniki ładujesz do tablicy w PHP i tam sortujesz i obrabiasz.

[lukaszk]

Dzięki jednak muszę poświęcić więcej czasu na naukę sql

A jak powinno wyglądać zapytanie które

tab_filmy // tabela z id filmu, id usera który dodał wpis na tablice
tab_galerie // tabela z id filmu, id usera który dodał wpis na tablice

filmy // tabela zawierająca id filmu, id użytkownika który ją dodał i różne inne dotyczące filmów
galerie // tabela zawierająca id galerii, id użytkownika który ją dodał i różne inne dotyczące galerii

znajomi // tabela zawierająca id powiązanych znajomych.
uzytkownicy // PODSTAWOWA tabela zawierająca informacje o użytkownikach. Id użytkownika jest unikalne i się nie powtórzy

Nie rozumiem "znajomy" czy to alias? nie mam takiej tabeli ani kolumny.

[PHP] pobierz, plaintext 
SELECT id_user_2 AS znajomy FROM znajomi WHERE id_user_1 = X
[PHP] pobierz, plaintext 

Poniższy kod wyświetla prawidłowo wpisy na tablicy (z uwzględnieniem znajomości) tylko jak dołączyć inne tabele takie jak filmy, muzyka?

[PHP] pobierz, plaintext 
SELECT *
FROM galerie
WHERE id_uzytkownik IN (
SELECT id_uzytkownik FROM tab_galerie WHERE id_uzytkownik='$id_user_isset'
UNION
SELECT id_uzytkownik FROM tab_filmy WHERE id_uzytkownik='$id_user_isset'
UNION
SELECT id_user_2 AS znajomi FROM znajomi WHERE id_user_1 ='$id_user_isset'
UNION
SELECT id_user_1 AS znajomi FROM znajomi WHERE id_user_2 ='$id_user_isset'
)
[PHP] pobierz, plaintext 

A czy nie najłatwiej by było gdyby tabela taka jak galerie, filmy miała dodatkową kolumnę tablica a w niej 0 albo 1 jeden oznacza wyświetl na tablicy.
Przypuszczam że zapytanie wówczas będzie krótsze i dodatkowo nie muszę odwoływać się zapytaniem do wielu tabel.

Dodatkowo mogę dodać kolumnę z losowym ciągiem który będzie identyfikatorem łączącym galerie na tablicy z komentarzami do tego.
Standardowym identyfikatorem komentarzy jest id_film, id_galeria

[PHP] pobierz, plaintext 
SELECT * FROM galerie WHERE id_uzytkownik=$id AND tablica = 1 
UNION 
SELECT * FROM filmy WHERE id_user_1 = $id AND tablica = 1
[PHP] pobierz, plaintext 

tylko co się stanie jak trafi się a zapewne trafi się rekord z tym samym id czy można ustawić auto increment tak aby kolejny numer id był sprawdzany w kilku tabelach?

[bostaf]

No panie kolego, całego zadania domowego za Ciebie nie odrobię, nie o to chodzi w nauce Wszystko czego potrzebujesz napisałem w poprzednim poście. Przeczytaj jeszcze raz i zrób tak, jak jest napisane.

Nie rozumiem "znajomy" czy to alias? nie mam takiej tabeli ani kolumny.

Dokładnie tak. To alias. Do definiowania aliasu służy słowo kluczowe AS, np. "SELECT nazwa_tabeli.nazwa_kolumny AS nk". Po zdefiniowaniu aliasu, w kolejnych częściach zapytania można używać nazwy aliasu zamiast nazwy podstawowej.

A czy nie najłatwiej by było gdyby tabela taka jak galerie, filmy miała dodatkową kolumnę tablica a w niej 0 albo 1 jeden oznacza wyświetl na tablicy.
Przypuszczam że zapytanie wówczas będzie krótsze i dodatkowo nie muszę odwoływać się zapytaniem do wielu tabel.

Krótko mówiąc, nie. Przeczytaj dobrze to, o czym pisałem już wcześniej - o normalizacji.

tylko co się stanie jak trafi się a zapewne trafi się rekord z tym samym id czy można ustawić auto increment tak aby kolejny numer id był sprawdzany w kilku tabelach?

Można zrobić to, o czym pisałem wcześniej: nie łączyć różnych mediów w tym samym zapytaniu a zrobić osobne zapytania dla każdego z mediów i obrobić wynik w PHP.