Dosyć często spotykam się z pytaniem, jak napisać grupowanie wyników, czyli np. mamy produkty które należą do kategorii i jak wyświetlić wszystkie kategorie wraz z należącymi do nich produktami.
Początkujący programiści albo nie wiedzą jak to zrobić, albo robią to przy użyciu wielu zagnieżdżonych zapytań, co niezbyt dobrze wpływa na wydajność aplikacji.
Rozwiązanie problemu opiszę na dwóch przykładach. Działanie obydwu będzie bardzo podobne: najpierw jednym zapytaniem pobieramy wszystkie dane, następnie w php grupujemy je według potrzeb i na koniec tworzymi stronę wynikową.
Przykład 1
Mamy kategorie. Do każdej kategorii może należeć kilka produktów. Chcemy wyświetlić wszystkie kategorie wraz z produktami jakie do nich należą.
Zaczniemy od utworzenia tabel i wypełnienia ich danymi:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- utworzenie tabel
CREATE TABLE `category` (
`ID` int unsigned NOT NULL auto_increment,
`NAME` varchar(32) NOT NULL ,
PRIMARY KEY (`ID`),
UNIQUE KEY `NAME` (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `product` (
`ID` int unsigned NOT NULL auto_increment,
`FK_CATEGORY` int unsigned NOT NULL ,
`NAME` varchar(32) NOT NULL ,
PRIMARY KEY (`ID`),
UNIQUE KEY `NAME` (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- zasilenie tabel danymi
insert into `category` (`ID`,`NAME`) values
(1,'kategoria 1'),
(2,'kategoria 2'),
(3,'kategoria 3'),
(4,'kategoria bez produktów');
insert into `product` (`ID`,`FK_CATEGORY`,`NAME`) values
(1,1,'Produkt1 z kategori 1'),
(2,1,'Produkt2 z kategori 1'),
(3,2,'Produkt1 z kategori 2'),
(4,3,'Produkt1 z kategori 3'),
(5,3,'Produkt2 z kategori 3'),
(6,3,'Produkt3 z kategori 3');
CREATE TABLE `category` (
`ID` int unsigned NOT NULL auto_increment,
`NAME` varchar(32) NOT NULL ,
PRIMARY KEY (`ID`),
UNIQUE KEY `NAME` (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `product` (
`ID` int unsigned NOT NULL auto_increment,
`FK_CATEGORY` int unsigned NOT NULL ,
`NAME` varchar(32) NOT NULL ,
PRIMARY KEY (`ID`),
UNIQUE KEY `NAME` (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- zasilenie tabel danymi
insert into `category` (`ID`,`NAME`) values
(1,'kategoria 1'),
(2,'kategoria 2'),
(3,'kategoria 3'),
(4,'kategoria bez produktów');
insert into `product` (`ID`,`FK_CATEGORY`,`NAME`) values
(1,1,'Produkt1 z kategori 1'),
(2,1,'Produkt2 z kategori 1'),
(3,2,'Produkt1 z kategori 2'),
(4,3,'Produkt1 z kategori 3'),
(5,3,'Produkt2 z kategori 3'),
(6,3,'Produkt3 z kategori 3');
Teraz skrypt, który pobierze dane z bazy i utworzy odpowiednią tablicę:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql_connect('localhost','root','') or die(mysql_error());
mysql_selectdb('test') or die(mysql_error());
//pobranie wszystkich danych
$sql = 'select p.NAME PNAME,p.ID PID, c.ID CID, c.NAME CNAME from category c '.
'left join product p on p.FK_CATEGORY = c.ID '.
'order by c.NAME asc, p.NAME asc'; //sortujemy po nazwie kategorii i po nazwie produktu
//jeśli chcemy pobrać tylko kategorie z produktami to zmieniamy trochę zapytanie:
//$sql = 'select p.NAME PNAME,p.ID PID, c.ID CID, c.NAME CNAME from product p '.
// 'left join category c on p.FK_CATEGORY = c.ID '.
// 'order by c.NAME asc, p.NAME asc'; //sortujemy po nazwie kategorii i po nazwie produktu
$res = mysql_query($sql) or die(mysql_error());
//najpierw pozyskamy niezbędne dane
$categories = array();
while ($row = mysql_fetch_array($res)){
$cid = $row['CID'];
//jeśli nie było jeszcze danej kategorii, to ją tworzymy
if (!isset($categories[$cid]))
$categories[$cid] = array('name' => $row['CNAME'], 'products' => array());
//dodajemy do kategorii kolejne produkty
if (!empty($row['PNAME'])) //jeśli istnieje produkt
$categories[$cid]['products'][] = array('name' => $row['PNAME'], 'id'=>$row['PID']);
}
//print_r($categories); //do obejrzenia jak wygląda wygenerowana tablica
mysql_selectdb('test') or die(mysql_error());
//pobranie wszystkich danych
$sql = 'select p.NAME PNAME,p.ID PID, c.ID CID, c.NAME CNAME from category c '.
'left join product p on p.FK_CATEGORY = c.ID '.
'order by c.NAME asc, p.NAME asc'; //sortujemy po nazwie kategorii i po nazwie produktu
//jeśli chcemy pobrać tylko kategorie z produktami to zmieniamy trochę zapytanie:
//$sql = 'select p.NAME PNAME,p.ID PID, c.ID CID, c.NAME CNAME from product p '.
// 'left join category c on p.FK_CATEGORY = c.ID '.
// 'order by c.NAME asc, p.NAME asc'; //sortujemy po nazwie kategorii i po nazwie produktu
$res = mysql_query($sql) or die(mysql_error());
//najpierw pozyskamy niezbędne dane
$categories = array();
while ($row = mysql_fetch_array($res)){
$cid = $row['CID'];
//jeśli nie było jeszcze danej kategorii, to ją tworzymy
if (!isset($categories[$cid]))
$categories[$cid] = array('name' => $row['CNAME'], 'products' => array());
//dodajemy do kategorii kolejne produkty
if (!empty($row['PNAME'])) //jeśli istnieje produkt
$categories[$cid]['products'][] = array('name' => $row['PNAME'], 'id'=>$row['PID']);
}
//print_r($categories); //do obejrzenia jak wygląda wygenerowana tablica
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
Array
(
[1] => Array
(
[name] => kategoria 1
[products] => Array
(
[0] => Array
(
[name] => Produkt1 z kategori 1
[id] => 1
)
[1] => Array
(
[name] => Produkt2 z kategori 1
[id] => 2
)
)
)
[2] => Array
(
[name] => kategoria 2
[products] => Array
(
[0] => Array
(
[name] => Produkt1 z kategori 2
[id] => 3
)
)
)
[3] => Array
(
[name] => kategoria 3
[products] => Array
(
[0] => Array
(
[name] => Produkt1 z kategori 3
[id] => 4
)
[1] => Array
(
[name] => Produkt2 z kategori 3
[id] => 5
)
[2] => Array
(
[name] => Produkt3 z kategori 3
[id] => 6
)
)
)
[4] => Array
(
[name] => kategoria bez produktów
[products] => Array
(
)
)
)
(
[1] => Array
(
[name] => kategoria 1
[products] => Array
(
[0] => Array
(
[name] => Produkt1 z kategori 1
[id] => 1
)
[1] => Array
(
[name] => Produkt2 z kategori 1
[id] => 2
)
)
)
[2] => Array
(
[name] => kategoria 2
[products] => Array
(
[0] => Array
(
[name] => Produkt1 z kategori 2
[id] => 3
)
)
)
[3] => Array
(
[name] => kategoria 3
[products] => Array
(
[0] => Array
(
[name] => Produkt1 z kategori 3
[id] => 4
)
[1] => Array
(
[name] => Produkt2 z kategori 3
[id] => 5
)
[2] => Array
(
[name] => Produkt3 z kategori 3
[id] => 6
)
)
)
[4] => Array
(
[name] => kategoria bez produktów
[products] => Array
(
)
)
)
Jedyne co należy teraz zrobić to na podstawie uzyskanej tablicy wygenerować stronę:
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
//a teraz wygenerujemy stronę na podstawie uzyskanych danych
echo '<ul>';
foreach ($categories as $idCat => $category){ //petla, która leci po kategoriach
echo '<li>'.$category['name'].' (liczba produktow: '.count($category['products']).')<ul>';
foreach ($category['products'] as $product){ //pętla, która leci po produktach w kategorii
echo '<li>Produkt o nazwie <b>'.$product['name'].'</b> i id <b>'.$product['id'].'</b></li>';
}
echo '</ul></li>';
}
echo '</ul>';
echo '<ul>';
foreach ($categories as $idCat => $category){ //petla, która leci po kategoriach
echo '<li>'.$category['name'].' (liczba produktow: '.count($category['products']).')<ul>';
foreach ($category['products'] as $product){ //pętla, która leci po produktach w kategorii
echo '<li>Produkt o nazwie <b>'.$product['name'].'</b> i id <b>'.$product['id'].'</b></li>';
}
echo '</ul></li>';
}
echo '</ul>';
- kategoria 1 (liczba produktow: 2)
- Produkt o nazwie Produkt1 z kategori 1 i id 1
- Produkt o nazwie Produkt2 z kategori 1 i id 2
- kategoria 2 (liczba produktow: 1)
- Produkt o nazwie Produkt1 z kategori 2 i id 3
- kategoria 3 (liczba produktow: 3)
- Produkt o nazwie Produkt1 z kategori 3 i id 4
- Produkt o nazwie Produkt2 z kategori 3 i id 5
- Produkt o nazwie Produkt3 z kategori 3 i id 6
- kategoria bez produktów (liczba produktow: 0)
Przykład 2
Mamy listę miast. Chcemy wyświetlić te miasta pogrupowane względem liter, od których się zaczynają.
Przykładowe dane:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- utworzenie tabel
CREATE TABLE `city` (
`ID` int unsigned NOT NULL auto_increment,
`NAME` varchar(32) NOT NULL ,
PRIMARY KEY (`ID`),
UNIQUE KEY `NAME` (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- zasilenie tabel danymi
insert into `city` (`ID`,`NAME`) values
(1,'Białystok'),
(2,'Olsztyn'),
(3,'Białowieża'),
(4,'Augustów'),
(5,'Warszawa'),
(6,'Olecko'),
(7,'Wrocław');
CREATE TABLE `city` (
`ID` int unsigned NOT NULL auto_increment,
`NAME` varchar(32) NOT NULL ,
PRIMARY KEY (`ID`),
UNIQUE KEY `NAME` (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- zasilenie tabel danymi
insert into `city` (`ID`,`NAME`) values
(1,'Białystok'),
(2,'Olsztyn'),
(3,'Białowieża'),
(4,'Augustów'),
(5,'Warszawa'),
(6,'Olecko'),
(7,'Wrocław');
Skrypt, który pobierze wszystkie miasta i utworzy właściwą tablicę:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql_connect('localhost','root','') or die(mysql_error());
mysql_selectdb('test') or die(mysql_error());
//pobranie wszystkich danych
$sql = 'select * from city '.
'order by NAME asc'; //sortujemy po nazwie
$res = mysql_query($sql) or die(mysql_error());
//najpierw pozyskamy niezbędne dane
$cities = array();
while ($row = mysql_fetch_array($res)){
$name = $row['NAME'];
//jeśli będziemy w bazie mieli miasta z nazwami zaczynającymi
//się od polskich liter należy użyć modułu mb_string
$firstLetter = strtoupper(substr($name,0,1));
//jeśli nie było jeszcze danej litery, to ją tworzymy
if (!isset($cities[$firstLetter]))
$cities[$firstLetter] = array();
//dodajemy kolejne miasta do danej litery
$cities[$firstLetter][] = $name;
}
//print_r($cities); //do obejrzenia jak wygląda wygenerowana tablica
mysql_selectdb('test') or die(mysql_error());
//pobranie wszystkich danych
$sql = 'select * from city '.
'order by NAME asc'; //sortujemy po nazwie
$res = mysql_query($sql) or die(mysql_error());
//najpierw pozyskamy niezbędne dane
$cities = array();
while ($row = mysql_fetch_array($res)){
$name = $row['NAME'];
//jeśli będziemy w bazie mieli miasta z nazwami zaczynającymi
//się od polskich liter należy użyć modułu mb_string
$firstLetter = strtoupper(substr($name,0,1));
//jeśli nie było jeszcze danej litery, to ją tworzymy
if (!isset($cities[$firstLetter]))
$cities[$firstLetter] = array();
//dodajemy kolejne miasta do danej litery
$cities[$firstLetter][] = $name;
}
//print_r($cities); //do obejrzenia jak wygląda wygenerowana tablica
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Array
(
[A] => Array
(
[0] => Augustów
)
[B] => Array
(
[0] => Białowieża
[1] => Białystok
)
[O] => Array
(
[0] => Olecko
[1] => Olsztyn
)
[W] => Array
(
[0] => Warszawa
[1] => Wrocław
)
)
(
[A] => Array
(
[0] => Augustów
)
[B] => Array
(
[0] => Białowieża
[1] => Białystok
)
[O] => Array
(
[0] => Olecko
[1] => Olsztyn
)
[W] => Array
(
[0] => Warszawa
[1] => Wrocław
)
)
Teraz wystarczy już tylko dane wyświetlić:
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
//a teraz wygenerujemy stronę na podstawie uzyskanych danych
echo '<ul>';
foreach ($cities as $letter => $_cities){ //petla, która leci po literach
echo '<li>'.$letter.'<ul>';
foreach ($_cities as $city){ //pętla, która leci po miastach w literze
echo '<li>'.$city.'</li>';
}
echo '</ul></li>';
}
echo '</ul>';
echo '<ul>';
foreach ($cities as $letter => $_cities){ //petla, która leci po literach
echo '<li>'.$letter.'<ul>';
foreach ($_cities as $city){ //pętla, która leci po miastach w literze
echo '<li>'.$city.'</li>';
}
echo '</ul></li>';
}
echo '</ul>';
- A
- Augustów
- B
- Białowieża
- Białystok
- O
- Olecko
- Olsztyn
- W
- Warszawa
- Wrocław
Edit
Na uwagę matipl zmodyfikowałem przykład 1 o kod kładący większy nacisk na udział bazy danych w obliczeniach:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
<?php
mysql_connect('localhost','root','') or die(mysql_error());
mysql_selectdb('test') or die(mysql_error());
//pobranie wszystkich danych i ich pogrupowanie
$sql = 'select GROUP_CONCAT(p.NAME order by p.NAME asc SEPARATOR \';;;\') PRODUCTS, '. //łączymy produkty poprzez ;;;. Po tym będziemy później dzielić
'c.ID CID, c.NAME CNAME from category c '.
'left join product p on p.FK_CATEGORY = c.ID '.
'group by c.ID '.
'order by c.NAME asc'; //sortujemy po nazwie kategorii
$res = mysql_query($sql) or die(mysql_error());
//najpierw pozyskamy niezbędne dane
//teraz dostaniemy tylko tyle rekordów ile jest kategorii
$categories = array();
while ($row = mysql_fetch_array($res)){
$cid = $row['CID'];
$categories[$cid] = array('name' => $row['CNAME'], 'products' => array());
if (!empty($row['PRODUCTS'])) { //jesli są produkty dla tej kategorii
//rozbicie tekstu z produktami na tablicę produktów
$products = explode(';;;',$row['PRODUCTS']);
foreach ($products as $product){
$categories[$cid]['products'][] = $product;
}
}
}
//print_r($categories); //do obejrzenia jak wygląda wygenerowana tablica
//a teraz wygenerujemy stronę na podstawie uzyskanych danych
echo '<ul>';
foreach ($categories as $idCat => $category){ //petla, która leci po kategoriach
echo '<li>'.$category['name'].'<ul>';
foreach ($category['products'] as $product){ //pętla, która leci po produktach w kategorii
echo '<li>'.$product.'</li>';
}
echo '</ul></li>';
}
echo '</ul>';?>
mysql_connect('localhost','root','') or die(mysql_error());
mysql_selectdb('test') or die(mysql_error());
//pobranie wszystkich danych i ich pogrupowanie
$sql = 'select GROUP_CONCAT(p.NAME order by p.NAME asc SEPARATOR \';;;\') PRODUCTS, '. //łączymy produkty poprzez ;;;. Po tym będziemy później dzielić
'c.ID CID, c.NAME CNAME from category c '.
'left join product p on p.FK_CATEGORY = c.ID '.
'group by c.ID '.
'order by c.NAME asc'; //sortujemy po nazwie kategorii
$res = mysql_query($sql) or die(mysql_error());
//najpierw pozyskamy niezbędne dane
//teraz dostaniemy tylko tyle rekordów ile jest kategorii
$categories = array();
while ($row = mysql_fetch_array($res)){
$cid = $row['CID'];
$categories[$cid] = array('name' => $row['CNAME'], 'products' => array());
if (!empty($row['PRODUCTS'])) { //jesli są produkty dla tej kategorii
//rozbicie tekstu z produktami na tablicę produktów
$products = explode(';;;',$row['PRODUCTS']);
foreach ($products as $product){
$categories[$cid]['products'][] = $product;
}
}
}
//print_r($categories); //do obejrzenia jak wygląda wygenerowana tablica
//a teraz wygenerujemy stronę na podstawie uzyskanych danych
echo '<ul>';
foreach ($categories as $idCat => $category){ //petla, która leci po kategoriach
echo '<li>'.$category['name'].'<ul>';
foreach ($category['products'] as $product){ //pętla, która leci po produktach w kategorii
echo '<li>'.$product.'</li>';
}
echo '</ul></li>';
}
echo '</ul>';?>
Do złączenia produktów użyłem ;;;. Jeśli jakimś cudem w nazwie waszego produktu pojawi się też ;;; to na wyniku otrzymacie o jeden produkt więcej, powstały z podziału tego "felernego" produktu na dwa. Miejcie to na uwadze ;)
Jako ćwiczenie dla Was pozostawiam zmianę przykładu 2.
LBO
2009-03-21 01:24:19
Na Twoim miejscu przykład zaimplementowałbym przy użyciu PDO. Posiada ono bardzo fajny modyfikator PDO::FETCH_GROUP.
Z jego pomocą byłoby znacznie mniej kodu, a także mógłbyś pokazać przeniesienie części odpowiedzialności na bazę (litery w pierwszej kolumnie).
Pozdrawiam