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

[Indeo]

W mysql istnieje szybsza i czytelniejsza składnia definiowania JOIN'ów - z uzyciem słowa "using" - można je stostować jesli obie tabele łączysz jednym kluczem o tej samej nazwie.
Zamiast:

[SQL] pobierz, plaintext 
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
[SQL] pobierz, plaintext 

może być:

[SQL] pobierz, plaintext 
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 USING(id_article)
LEFT JOIN category USING(id_category)
GROUP BY a.id_article
[SQL] pobierz, plaintext 

Pozdrawiam (IMG:style_emoticons/default/wink.gif)

Twój drugi sposób z joinem jest lepszy, bo przy łączeniu wiekszej liczby tabel łatwiej sie połapać.
Left join używasz kiedy chcesz żeby zapytanie zwróciło również artykuły bez przypisanej kategorii (zamiast kategorii zwróci null).
Jeśli z góry wiesz, że każdemu artykułowi musi być przypisana kategoria to uzyjesz INNER JOIN.
Trzeci sposób to wykorzystanie podzapytania. Ale z tego co wiem joiny działają szybciej.