MySQL Relacje - jak sformułować zapytanie? Opcje

[rthwh]

Witam!

Ucząc się PHP i baz danych MySQL postanowiłem napisać aplikację zarządzania aktualnościami na stornie. Problem polega na tym, że nie wiem jak uzyskać dane z bazy danych najlepiej w 1 zapytaniu albo w jaki kolwiek sposób.

3 tabele relacja wiele do wielu, (kazdy article moze miec kilka kategori, każda kategoria może mieć kilka article). Nie wiem jak wyświetlić liste newsów (article) oraz kategorie do których należą bez powtarzania w liście tego samego Newsa z tym samym id
np w takiej formie

1. artykul 1, kategoria: kat_A, kat_B
2. artykul 2, kategoria: kat_A

Schemat bazy

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `article` (
  `id_article` smallint(6) NOT NULL AUTO_INCREMENT,
  `title` text COLLATE utf8_polish_ci,
  `content` text COLLATE utf8_polish_ci,
  `author` tinyint(4) DEFAULT NULL,
  `permission` tinyint(4) NOT NULL,
  `date` date DEFAULT NULL,
  PRIMARY KEY (`id_article`),
  KEY `author` (`author`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=17 ;
 
CREATE TABLE IF NOT EXISTS `article_category` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `id_article` smallint(6) NOT NULL,
  `id_category` smallint(6) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_article` (`id_article`),
  KEY `id_category` (`id_category`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=13 ;
ALTER TABLE `article_category`
ADD CONSTRAINT `article_category_ibfk_1` FOREIGN KEY (`id_article`) REFERENCES `article` (`id_article`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `article_category_ibfk_2` FOREIGN KEY (`id_category`) REFERENCES `category` (`id_category`) ON DELETE CASCADE ON UPDATE CASCADE;
 
 
CREATE TABLE IF NOT EXISTS `category` (
  `id_category` smallint(6) NOT NULL AUTO_INCREMENT,
  `name` text COLLATE utf8_polish_ci NOT NULL,
  PRIMARY KEY (`id_category`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=3 ;
 
[SQL] pobierz, plaintext 

Metoda odpowiedzialna za pobieranie danych z MySQL - nie wiem jak sformuować zapytanie do mySQL

[PHP] pobierz, plaintext 
    public function showAllNews(){
        try
        {
            $stmt = $this->pdo->prepare("SELECT u.name AS name_user, a.title, a.id_article, a.permission, a.date,
                                         (SELECT GROUP_CONCAT(c.name) FROM category c, article_category ac 
                                         WHERE c.id_category = ac.id_category
                                         AND a.id_article = ac.id_article) AS name_category
                                         FROM users u, category c, article a, article_category ac 
                                         WHERE u.id_users = a.author 
                                         AND ac.id_article = a.id_article
                                         AND ac.id_category = c.id_category");
 
            $stmt->execute();
        }catch(PDOException $e){
            echo 'MYSQLDB ERROR CODE: LIST NEWS!!!';
            echo $e->getMessage();
            die();
        }
        if($stmt->rowCount() > 0){
 
        $this->_news = $stmt->fetchAll(PDO::FETCH_ASSOC);
        //print_r($this->_news);
        }else{
            $this->_news = array();
        }
        return $this->_news;
    }
[PHP] pobierz, plaintext 

oraz plik template odpowiedzialny za wyświetlenie listy:

[PHP] pobierz, plaintext 
 <?php
            $lp = 1;
            if(!empty($this->_data)){
            foreach($this->_data as $row){
                echo $lp.' Tytuł: '.$row['title'].' kategoria: '.$row['name_category'].' Autor: '.$row['name_user'].' Data: '.$row['date'].' <a href="?action=news&mod=edit&id='.$row['id_article'].'"> EDYTUJ</a><br />' ;
                $lp++;
            }
            }else{
                echo 'Brak aktualnosci!';
            }
        ?>
[PHP] pobierz, plaintext 

Obecnie wynik wyglada nastepująco:
1 Tytuł: test kategoria: robocza Autor: Administrator Data: 2013-04-01 EDYTUJ
2 Tytuł: teswt 2 kategoria: testowa,robocza Autor: Administrator Data: 2013-04-01 EDYTUJ
3 Tytuł: teswt 2 kategoria: testowa,robocza Autor: Administrator Data: 2013-04-01 EDYTUJ

jak widać pozycja nr 2,3 jest to ten sam artykul...

Z góry dziękuję za odpowiedz, pozdrawiam.

Witam. Udało mi się samemu rozwiązać mój problem, nawet na 2 sposoby. Chce żebyście powiedzieli mi. który jest lepszy (poprawny) bo oba działają.

1 sposób

[PHP] pobierz, plaintext 
$stmt = $this->pdo->prepare("SELECT a.title, a.id_article, a.permission, a.date, GROUP_CONCAT( c.name
                                        SEPARATOR ',' ) AS `name_category`
                                        FROM article a, category c, article_category ac WHERE ac.id_article = a.id_article
                                        AND ac.id_category = c.id_category
                                        GROUP BY a.id_article");
[PHP] pobierz, plaintext 

2 sposób

[PHP] pobierz, plaintext 
$stmt = $this->pdo->prepare("SELECT a.title, a.id_article, a.permission, a.date, GROUP_CONCAT( category.name
                                        SEPARATOR ',' ) AS `name_category`
                                        FROM article a LEFT JOIN article_category ON article_category.id_article = a.id_article
                                        LEFT JOIN category ON category.id_category = article_category.id_category
                                        GROUP BY a.id_article");
[PHP] pobierz, plaintext 

Nie wiem po prostu kiedy użyć JOIN LEFT a kiedy je pominąć jak w 1 sposobie. Definicję JOIN (LEFT RIGHT) oczywiście znam ale jescze nie potrafię wykorzystać tego w praktyce.

Pozdrawiam, rthwh

Ten post edytował rthwh 3.04.2013, 11:50:35

[rthwh]

Okej wszystko już działa dzięki za odpowiedz.
Teraz mam kolejny problem.
Jak wczesniej wspomniałem istnieje tabela article_category relacje miedzy article i category. Problem polega na tym kiedy użytkownik edytuje jakis artykuł i chce zrobic:
a)pozostawić bez zmian kategorie
b)zmienić kategorie
c)odjąć kategorie
d)dodać kategorie

w każdym z tych 4 przypadków robię to następująco:
1 - usuwam wszystkie wpisy w tabeli article_category gdzie id_article = id_article (przekazane przez POST)
2 - tworze od nowa relacje arrtykułu z kategoriami

Nie dokońca jestem przekonany, że to jest dobre roziwązanie (usuwanie potem dodawanie) zamiast edytowac przez UPDATE.
Problem z UPDATE jest wtedy gdy użytkownik edytując post usunie jakies kategorie. Zostaną zaktualizowane wpisy w tabeli ale zostana stare ... krótko mówiąc nie potrafie i nie wiem jak sformuować zapytanie do MySQL.

Pozdrawiam, rthwh.