[PHP][MYSQL] złączenie tabel Opcje

[kleszczoscisk]

Witam

W bazie są następujące tabele

• categories (category_id level sort) category_id - numer kategorii, level - kategoria nadrzędna(rodzic), sort - kolejność wyświetlania
• cat_translations ( cid cat_name ) cid - numer kategorii, cat_name - nazwa kategorii
• products (product_id category_id category2 category3) product_id - id produktu, category_id - id kategorii, category2 -druga kategoria category3 - trzecia kategoria
• prod_translations (pid product_name ... i inne pola opisujące) pid - id produktu

Jak sformułować zapytanie aby otrzymać listę produktów w określonej kategorii ze zmiennej GET np. $_GET['kat'] = 5
... i jeśli jest to kategoria główna-rodzic to razem z produktami w jej podkategoriach ?

Próbuję tak, ale efekty beznadziejne:

[PHP] pobierz, plaintext 
<?php
$rekord_kategoria = mysql_query(&#092;" SELECT  * 
FROM `products`  
 LEFT JOIN `categories` ON (categories.level = products.category_id  OR categories.level = products.category2 OR categories.level = products.category3)
 LEFT JOIN  `prod_translations` ON  (`prod_translations`.`pid` = `products`.`product_id` ) 
 LEFT JOIN  `gfx` ON  (prod_translations.pid = gfx.prod_id ) 
WHERE  
categories.level = '$kat' AND
prod_translations.lang = 'pl'  AND 
prod_translations.active = 1 
ORDER BY products.add_date DESC  LIMIT &#092;".($pagz*$ilez).\", $ilez\") or die(mysql_error());
?>
[PHP] pobierz, plaintext 

[SQL] pobierz, plaintext 
--
-- Struktura tabeli dla  `categories`
--
 
CREATE TABLE `categories` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `level` varchar(255) NOT NULL,
  `sort` smallint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY  (`category_id`),
  KEY `level_2` (`level`),
  KEY `sort` (`sort`),
  FULLTEXT KEY `level` (`level`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 PACK_KEYS=0 AUTO_INCREMENT=115 ;
 
 
--
-- Struktura tabeli dla  `cat_translations`
--
 
CREATE TABLE `cat_translations` (
  `trans_cid` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `cid` int(11) NOT NULL DEFAULT '0',
  `cat_name` varchar(99) NOT NULL,
  `title` varchar(99) NOT NULL DEFAULT '',
  `description` mediumtext NOT NULL,
  `active` tinyint(1) NOT NULL,
  `pres_id` int(11) UNSIGNED DEFAULT NULL,
  `lang` char(3) NOT NULL DEFAULT '',
  `products` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `isdefault` tinyint(1) NOT NULL,
  PRIMARY KEY  (`trans_cid`),
  KEY `cid` (`cid`),
  KEY `pres_id` (`pres_id`),
  KEY `cat_name` (`cat_name`,`lang`),
  KEY `active` (`active`,`lang`),
  KEY `lang` (`lang`),
  KEY `isdefault` (`isdefault`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=119 ;
 
 
 
--
-- Struktura tabeli dla  `products`
--
 
CREATE TABLE `products` (
  `product_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` int(11) UNSIGNED NOT NULL DEFAULT '0',
  `producer_id` int(11) UNSIGNED DEFAULT NULL,
  `rate` float DEFAULT NULL,
  `votes` int(6) DEFAULT NULL,
  `vat` varchar(8) NOT NULL DEFAULT '0',
  `in_stock` int(11) NOT NULL DEFAULT '1',
  `gfx` varchar(99) DEFAULT NULL,
  `gfx_small` varchar(30) DEFAULT NULL,
  `gfx_enlarge` varchar(30) DEFAULT NULL,
  `file` varchar(255) DEFAULT NULL,
  `add_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `edit_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `promo` char(1) NOT NULL DEFAULT '',
  `warranty` int(11) NOT NULL DEFAULT '0',
  `weight` varchar(12) NOT NULL DEFAULT '',
  `views` int(7) NOT NULL DEFAULT '0',
  `category2` int(11) UNSIGNED NOT NULL DEFAULT '0',
  `category3` int(11) UNSIGNED NOT NULL DEFAULT '0',
  `sort` int(11) NOT NULL DEFAULT '0',
  `main_page` tinyint(1) NOT NULL,
  `main_page_sort` int(11) NOT NULL DEFAULT '0',
  `products_related` mediumtext NOT NULL,
  `other_price` float NOT NULL DEFAULT '0',
  `product_code` varchar(255) NOT NULL DEFAULT '',
  `pkwiu` varchar(255) DEFAULT NULL,
  PRIMARY KEY  (`product_id`),
  UNIQUE KEY `product_code_2` (`product_code`),
  KEY `producer_id` (`producer_id`),
  KEY `add_date` (`add_date`),
  KEY `sort` (`sort`),
  KEY `categories` (`category_id`,`category2`,`category3`),
  KEY `main_page` (`main_page`),
  KEY `main_page_sort` (`main_page_sort`),
  KEY `price` (`price`),
  KEY `in_stock` (`in_stock`),
  KEY `promo` (`promo`),
  KEY `category2` (`category2`),
  KEY `category3` (`category3`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 PACK_KEYS=0 AUTO_INCREMENT=2021 ;
 
 
 
--
-- Struktura tabeli dla  `prod_translations`
--
 
CREATE TABLE `prod_translations` (
  `id_transl` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL DEFAULT '0',
  `product_name` varchar(255) character SET utf8 collate utf8_polish_ci NOT NULL,
  `description` mediumtext character SET utf8 collate utf8_polish_ci NOT NULL,
  `params` mediumtext NOT NULL,
  `jm` varchar(32) NOT NULL DEFAULT '',
  `options` mediumtext NOT NULL,
  `active` tinyint(1) NOT NULL,
  `lang` char(3) NOT NULL DEFAULT '',
  `isdefault` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id_transl`),
  UNIQUE KEY `pid` (`pid`,`lang`),
  KEY `lang_active` (`active`,`lang`),
  KEY `product_name_2` (`product_name`),
  KEY `pid_2` (`pid`),
  KEY `product_name_3` (`product_name`,`lang`),
  KEY `lang` (`lang`),
  KEY `isdefault` (`isdefault`),
  FULLTEXT KEY `product_name` (`product_name`),
  FULLTEXT KEY `search` (`product_name`,`description`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3904 ;
[SQL] pobierz, plaintext 

Proszę o pomoc.

Zrobiłem tak, ale jesli produkt jest dodany do więcej niż jednej kategorii to wyswietla się wielokrotnie...

[PHP] pobierz, plaintext 
<?php
$rekord_kategoria = mysql_query(&#092;" SELECT  * 
FROM `cat_translations` 
 LEFT JOIN  `categories` ON cat_translations.cid  = categories.category_id 
 LEFT JOIN `products` ON  (`products`.`category_id` = `cat_translations`.`cid`  OR `products`.`category2` = `cat_translations`.`cid` OR `products`.`category3` = `cat_translations`.`cid`) 
 LEFT JOIN `prod_translations` ON products.product_id = prod_translations.pid 
 JOIN  `gfx` ON  products.product_id = gfx.prod_id  
 
WHERE   
categories.level = '$kat' OR categories.category_id = '$kat' 
AND gfx.gfx_sort = 1  AND gfx.main_gfx = 1
AND prod_translations.lang = 'pl'   
AND cat_translations.lang = 'pl'   
 
ORDER BY cat_translations.cid DESC  LIMIT &#092;".($pagz*$ilez).\", $ilez\") or die(mysql_error());
?>
[PHP] pobierz, plaintext 

Proszę o jakieś wskazówki....

Ten post edytował kleszczoscisk 25.03.2009, 17:50:46