ne verujem da ti je struktura te dve tabele top secret :D .. al ajde ...
no .. i dalje ne kapiram tacno sta ti treba ...
primer:
Code:
mysql> create table `kat` (kat_id int auto_increment primary key, kat_name char(10)) engine=myisam;
Query OK, 0 rows affected (0.06 sec)
mysql> insert into `kat` (kat_name) values ('voce'), ('povrce'), ('meso');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from kat;
+--------+----------+
| kat_id | kat_name |
+--------+----------+
| 1 | voce |
| 2 | povrce |
| 3 | meso |
+--------+----------+
3 rows in set (0.00 sec)
mysql> create table `art` (art_id int auto_increment primary key, art_name char(10), kat_id int) engine=myisam;
Query OK, 0 rows affected (0.07 sec)
mysql> insert into `art` (art_name, kat_id) values ('jabuka', 1), ('kruska', 1), ('jagoda', 1), ('lubenica', 2), ('paprika', 2), ('svinja', 3), ('kokoska', 3), ('patka', 3);
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> select * from art;
+--------+----------+--------+
| art_id | art_name | kat_id |
+--------+----------+--------+
| 1 | jabuka | 1 |
| 2 | kruska | 1 |
| 3 | jagoda | 1 |
| 4 | lubenica | 2 |
| 5 | paprika | 2 |
| 6 | svinja | 3 |
| 7 | kokoska | 3 |
| 8 | patka | 3 |
+--------+----------+--------+
8 rows in set (0.00 sec)
ok .. sada imamo primer .. da li primer odgovara tvojim tabelama ?
oces spisak svih artikala sa podacima ...
Code:
mysql> select kat_id as kategorija, art_id as arikl from art order by kat_id, rand();
+------------+-------+
| kategorija | arikl |
+------------+-------+
| 1 | 3 |
| 1 | 2 |
| 1 | 1 |
| 2 | 5 |
| 2 | 4 |
| 3 | 7 |
| 3 | 8 |
| 3 | 6 |
+------------+-------+
8 rows in set (0.01 sec)
dakle - ono sto sam ti vec napisao dvaput ...
oces to isto - samo sa imenima ... onda ti treba join posto su imena malo tamo malo vamo ..
Code:
mysql> select kat.kat_name as kategorija, art.art_name as artikl from kat join art using (kat_id) order by kat_id;
+------------+----------+
| kategorija | artikl |
+------------+----------+
| voce | jabuka |
| voce | kruska |
| voce | jagoda |
| povrce | lubenica |
| povrce | paprika |
| meso | svinja |
| meso | kokoska |
| meso | patka |
+------------+----------+
8 rows in set (0.00 sec)
oces i id-ove?
Code:
mysql> select kat.kat_name as kategorija, kat.kat_id, art.art_name as artikl, art.art_id from kat join art using (kat_id) order by kat_id;
+------------+--------+----------+--------+
| kategorija | kat_id | artikl | art_id |
+------------+--------+----------+--------+
| voce | 1 | jabuka | 1 |
| voce | 1 | kruska | 2 |
| voce | 1 | jagoda | 3 |
| povrce | 2 | lubenica | 4 |
| povrce | 2 | paprika | 5 |
| meso | 3 | svinja | 6 |
| meso | 3 | kokoska | 7 |
| meso | 3 | patka | 8 |
+------------+--------+----------+--------+
8 rows in set (0.00 sec)
oces potpuno beskoristan izlaz koji lici na ono sto si napisao u prethodnoj poruci:
Code:
mysql> select kat.kat_name as kategorija, group_concat(art.art_name) as artikli from kat join art using (kat_id) group by kat_id;
+------------+----------------------+
| kategorija | artikli |
+------------+----------------------+
| voce | jabuka,kruska,jagoda |
| povrce | lubenica,paprika |
| meso | svinja,kokoska,patka |
+------------+----------------------+
3 rows in set (0.00 sec)
mysql> select kat.kat_name as kategorija, group_concat(art.art_id) as artidovi from kat join art using (kat_id) group by kat_id;
+------------+----------+
| kategorija | artidovi |
+------------+----------+
| voce | 1,2,3 |
| povrce | 4,5 |
| meso | 6,7,8 |
+------------+----------+
3 rows in set (0.01 sec)
eko ti treba nesto drugo, aj napravi izlaz koji oces da dobijes