Stworzenie specyficznego zapytania do bazy danych lub filtrowanie w php, Zniwelowanie powielania się danych w wyniku (1 wiersz = 1 pozycja) Opcje

[patrix007]

Witam

Postanowiłem pogłębić nieco moją wiedzę w tej dziedzinie i zabrać się za coś odrobinkę trudniejszego.
Zabawa szła dobrze do pewnego momentu, już wyjaśniam o co mi chodzi.

Mam taką o to bazę danych (a raczej jej potrzebny tutaj fragment):



Stworzyłem sobie takie oto zapytanie:



Które daje taki wynik w phpadminie:




Moje pytanie polega na tym, jak "unormalnić" ten wynik zapytania ?
Normalną sprawą jest, że funkcja w php mysqli_fetch_assoc listuje ten wynik w taki sam sposób j/w co utrudnia mi strasznie zabawę bo nie mam pomysłu jak to przefiltrować w PHP.
A może nie wpadłem na pomysł i da się to sprytnie poprawić w samym zapytaniu zaoszczędzając sobie sporo dłubania w PHP ?

Aby była całkowita jasność mojej wizji podam przykład wyniku jaki właśnie mnie interesuje:



Bawiłem się troszeczkę GROUP_CONCAT ale moimi pomysłami realizuję powyższy plan co najwyżej połowicznie

Proszę o rady/pomoc

[Mchl]

[edit]
Za szybko odpowiedziałem, za wolno przeczytałem

A co konkretnie Ci nie wychodzi z GROUP_CONCAT?

Ten post edytował Mchl 18.07.2010, 20:24:06

[patrix007]

Najlepsze zapytanie jakie wymyśliłem wygląda następująco:



Wynik zapytania następujący:



Niestety nie umiem już ruszyć/dokleić kolumny "aktorzy" aby uzyskać wynik tak jak na poniższym przykładzie:

[Mchl]

Musisz tabelę czlowiek_filmu dołączyć jeszcze raz (pod innym aliasem) z warunkiem rola = 'aktor'

[patrix007]

No i ten moment właśnie nie bardzo rozumiem,
skopiowałem jednego selekta (ten, który miał alias 'rezyseria' ) i nadałem mu alias 'aktorzy' i co dalej ?

[Mchl]

Wiesz co... nie bardzo chce mi się przepisywać to co wkleiłeś jako obrazek, więc dam przykład na prostszej strukturze.

Wyobraź sobie że są dwie tabele.

1. tabela filmy
id, tytul
--------------------
1, Matrix,
2, Matrix Reloaded
3, Matrix Revolutions


2. tabela ludzie
id, filmId, imieNazwisko, rola
1,1, Andy Wachowski, reżyser
2,2, Andy Wachowski, reżyser
3,3, Andy Wachowski, reżyser
4,1, Keanu Reeves, aktor
5,2, Keanu Reeves, aktor
6,3, Keanu Reeves, aktor
7,1, Carrie-Anne Moss, aktor
8,2, Carrie-Anne Moss, aktor
9,3, Carrie-Anne Moss, aktor


troche niezgodnie z zasadami normalizacji ale to tylko przyklad

Kod

SELECT
  f.id, f.tytul
  GROUP_CONCAT(r.imieNazwisko) AS rezyseria,
  GROUP_CONCAT(a.imieNazwisko) AS aktorzy
FROM
  filmy AS f
INNER JOIN
  (SELECT filmId, imieNazwisko FROM ludzie WHERE rola = 'reżyser') AS r
ON f.id = r.filmId
INNER JOIN
  (SELECT filmId, imieNazwisko FROM ludzie WHERE rola = 'aktor') AS a
ON f.id = a.filmId
GROUP BY f.id

[patrix007]

Poczytałem chwilkę o JOIN i stworzyłem takie oto zapytanie:

Kod

SELECT
        film_b.idfilm 'id',
        film_b.tytul_ang,
        film_b.rok_prod 'produkcja',
        GROUP_CONCAT( DISTINCT CONCAT(czlowiek_filmu_b.imie,' ',czlowiek_filmu_b.nazwisko)) 'rezyseria',
        GROUP_CONCAT( DISTINCT CONCAT(a.imie,' ',a.nazwisko)) 'aktorzy',
        GROUP_CONCAT( DISTINCT gatunek.nazwa) 'gatunek'
        
FROM
        film AS film_b,
        rola AS rola_b,
        czlowiek_filmu AS czlowiek_filmu_b,
        czlowiek_filmu_do_film AS czlowiek_filmu_do_film_b,
        gatunek,
        film_do_gatunek
        
INNER JOIN   (
    
SELECT
        film_a.idfilm 'id',
        czlowiek_filmu_a.imie,
        czlowiek_filmu_a.nazwisko
        
FROM
        film AS film_a,
        rola AS rola_a,
        czlowiek_filmu AS czlowiek_filmu_a,
        czlowiek_filmu_do_film AS czlowiek_filmu_do_film_a
        
WHERE    
        rola_a.rola = 'aktor' AND
        czlowiek_filmu_do_film_a.czlowiek_filmu_idczlowiek_filmu = czlowiek_filmu_a.idczlowiek_filmu AND
        czlowiek_filmu_do_film_a.film_idfilm = film_a.idfilm AND
        czlowiek_filmu_do_film_a.rola_idrola = rola_a.idrola ) AS a
        ON a.id = id

WHERE    rola_b.rola = 'rezyser' AND
        czlowiek_filmu_do_film_b.czlowiek_filmu_idczlowiek_filmu = czlowiek_filmu_b.idczlowiek_filmu AND
        czlowiek_filmu_do_film_b.film_idfilm = film_b.idfilm AND
        czlowiek_filmu_do_film_b.rola_idrola = rola_b.idrola AND
        film_do_gatunek.film_idfilm = film_b.idfilm  AND
        film_do_gatunek.gatunek_idgatunek = gatunek.idgatunek GROUP BY film_b.idfilm

Wynik zapytania jest następujący:


Jest już prawie idealnie !

Moje jeszcze jedno pytanie w związku z tym,
dlaczego w kolumnie aktorzy nie powtarza się kolejność wpisów ?

Ten post edytował patrix007 19.07.2010, 00:20:52

[Mchl]

Kod

GROUP_CONCAT( DISTINCT CONCAT(a.imie,' ',a.nazwisko) ORDER BY a.nazwisko, a.imie) 'aktorzy',

i już powinno być ok

Albo możesz w swoim podzapytaniu dodać order by.

[patrix007]

Teraz jest perfekcyjnie ;-)
Teraz mogę się bawić dalej

To jeszcze raz ja, po dodaniu dwóch kolejnych filmów wyszło na jaw, że jednak z moim JOIN jest coś nie tak.
Po zastosowaniu j/w zapytania przy 5 filmach w bazie mam taki oto wynik:



Wpisy w samej bazie są ok ponieważ jak sobie wyłuskam to zapytanie z tego JOIN i dodatkowo pogrupuję to jest wszystko ok...

Wyłuskane zapytanie z JOIN, dodane grupowanie:

Kod

                SELECT
                        film_a.idfilm 'film_id',
                        GROUP_CONCAT( DISTINCT CONCAT(czlowiek_filmu_a.nazwisko,' ',czlowiek_filmu_a.imie)) 'imieNazwisko'
        
                FROM
                        film AS film_a,
                        rola AS rola_a,
                        czlowiek_filmu AS czlowiek_filmu_a,
                        czlowiek_filmu_do_film AS czlowiek_filmu_do_film_a
        
                WHERE    
                        rola_a.rola = 'aktor' AND
                        czlowiek_filmu_do_film_a.czlowiek_filmu_idczlowiek_filmu = czlowiek_filmu_a.idczlowiek_filmu AND
                        czlowiek_filmu_do_film_a.film_idfilm = film_a.idfilm AND
                        czlowiek_filmu_do_film_a.rola_idrola = rola_a.idrola GROUP BY film_id

Wynik tego zapytania jest następujący:



W pierwszych trzech filmach mam naturalnie tych samych aktorów i jest prawidłowo bo tak mam w bazie, kolejne dwa filmy są inne i mają innych aktorów co też sie zgadza z wpisami w bazie.


Po wyniku tego zapytania sądzę, że zepsułem coś przy

Kod

ON a.id = id

Jednak co bym nie pokombinował to mi nie wychodzi

[Mchl]

Spróbuj

Kod

ON a.id = film_b.idfilm

[patrix007]

Błąd:

Kod

#1054 - Unknown column 'film_b.idfilm' in 'on clause'

[Mchl]

Dziwna sprawa, bo to przecież pierwsza kolumna w tym zapytaniu, które wkleiłeś powyżej.

[patrix007]

Mchl, oczywiście miałeś rację, jednak (wbrew logice) za żadne skarby nie chciało mi to działać.
Lekarstwem okazał się google, znalazłem stronę:
http://www.delphifaq.com/faq/databases/mysql/f1110.shtml
A na niej przykład:

Kod

create table t1 (a int);
create table t2 (b int);
create table t3 (c int);

select * from t1, t2 join t3 on t1.a=t3.c;
ERROR 1054 (42S22): Unknown column 't1.a' in 'on clause'

select * from t1, t2 left outer join t3 on t1.a=t3.c;
ERROR 1054 (42S22): Unknown column 't1.a' in 'on clause'

select * from t1 join t2 join t3 on t1.a=t3.c;
//.. this works ..

select * from t1 join t2 left outer join t3 on t1.a=t3.c;
//.. this works ..

Kod

I had the same problem and the fix is very simple.

All you have to do is adding parentheses to the implicit join after FROM

select * from (t1, t2) join t3 on t1.a=t3.c;
select * from (t1, t2) left outer join t3 on t1.a=t3.c;

It should work.

This is because MySQL is now in compliance with some new rules of SQL language. I have seen some other solutions but I believe this is the easier.

Zbawieniem okazało się wstawienie całej zawartości po FROM w nawiasy ()
błąd zniknął, listuje się poprawnie

[Mchl]

No tak. Na co dzień nie używam przecinka tylko INNER JOIN, więc nawet nie wiedziałem, że coś takiego się dzieje.

[patrix007]

Kolejny problem mam.

Tym razem chodzi o to, że jak dodam pod polecenie WHERE:

[SQL] pobierz, plaintext 
aktorzy IN ('Bruce Willis')
[SQL] pobierz, plaintext 

To pokazuje mi się błąd:

Kod

#1247 - Reference 'aktorzy' not supported (reference to group function)

Inna sprawa jak przeprowadzić (w/w zapytaniu) wyszukiwanie po kolumnie np. 'gatunek' gdzie mam zastosowane grupowanie ale tak aby w wynikach dalej istniało to grupowanie ?

Uściślając, filmy mają zazwyczaj po kilka gatunków, które listują się poprawnie, zaś gdy dodam polecenie po WHERE:

[SQL] pobierz, plaintext 
gatunek.nazwa IN ('akcja')
[SQL] pobierz, plaintext 

to w prawdzie poprawnie listuje wszystkie filmy o tym gatunku lecz grupowanie znika i w kolumnie gatunek zostaje tylko 'akcja' mimo, że powinno wylistować dwa gatunki do tej pozycji


OK, dołączyłem tabelę jeszcze raz jako tmp (INNER JOIN) i jest dobrze, na niej wykonałem IN

Ten post edytował patrix007 1.08.2010, 14:25:40

[everth]

Jak masz tak skomplikowane zapytania to zapoznaj się z widokami. Ułatwi ci to użeranie się z SQLem na przyszłość.