· 6 years ago · Jun 19, 2019, 06:08 PM
1ps_product_lang
2
3ps_order_detail
4
5and ps_image
6
7=== ps_product_lang ===
8
9
10CREATE TABLE IF NOT EXISTS `ps_product_lang` (
11 `id_product` int(10) unsigned NOT NULL,
12 `id_shop` int(11) unsigned NOT NULL DEFAULT '1',
13 `id_lang` int(10) unsigned NOT NULL,
14 `description` text,
15 `description_short` text,
16 `name` varchar(128) NOT NULL,
17 PRIMARY KEY (`id_product`,`id_shop`,`id_lang`),
18 KEY `id_lang` (`id_lang`),
19 KEY `name` (`name`)
20) ENGINE=InnoDB DEFAULT CHARSET=utf8;
21
22--
23-- Dumping data for table `ps_product_lang`
24--
25
26INSERT INTO `ps_product_lang` (`id_product`, `id_shop`, `id_lang`, `description`, `description_short`, `name`) VALUES
27(1, 1, 1, '<p>this is dummy</p>', '<p>dummy</p>', 'dummy product')
28);
29
30
31===== ps_order_detail===
32 CREATE TABLE IF NOT EXISTS `ps_order_detail` (
33 `id_order_detail` int(10) unsigned NOT NULL AUTO_INCREMENT,
34 `id_order` int(10) unsigned NOT NULL,
35 `product_id` int(10) unsigned NOT NULL,
36 PRIMARY KEY (`id_order_detail`),
37 KEY `product_id` (`product_id`)
38 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=16 ;
39
40 --
41 -- Dumping data for table `ps_order_detail`
42 --
43
44 INSERT INTO `ps_order_detail` (`id_order_detail`, `id_order`, `product_id`) VALUES
45 (1, 1, 2);
46
47========= ps_image =========
48CREATE TABLE IF NOT EXISTS `ps_image` (
49 `id_image` int(10) unsigned NOT NULL AUTO_INCREMENT,
50 `id_product` int(10) unsigned NOT NULL,
51 `position` smallint(2) unsigned NOT NULL DEFAULT '0',
52 `cover` tinyint(1) unsigned NOT NULL DEFAULT '0',
53 PRIMARY KEY (`id_image`),
54 UNIQUE KEY `idx_product_image` (`id_image`,`id_product`,`cover`),
55 KEY `image_product` (`id_product`),
56 KEY `id_product_cover` (`id_product`,`cover`)
57) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=24 ;
58
59--
60-- Dumping data for table `ps_image`
61--
62
63INSERT INTO `ps_image` (`id_image`, `id_product`, `position`, `cover`) VALUES
64(1, 1, 1, 1),
65(2, 1, 2, 0),
66(3, 1, 3, 0),
67(4, 1, 4, 0),
68(5, 2, 1, 0),
69(6, 2, 2, 0),
70(7, 2, 3, 1),
71(8, 3, 1, 1),
72(9, 3, 2, 0),
73(10, 4, 1, 1),
74(11, 4, 2, 0),
75(12, 5, 1, 1),
76(13, 5, 2, 0),
77(14, 5, 3, 0),
78(15, 5, 4, 0),
79(16, 6, 1, 1),
80(17, 6, 2, 0),
81(18, 6, 3, 0),
82(19, 6, 4, 0),
83(20, 7, 1, 1),
84(21, 7, 2, 0),
85(22, 7, 3, 0),
86(23, 7, 4, 0);
87
88id_order_detail id_product name id_image(from ps_image table where cover = 1)
89
90SELECT DISTINCT (a.id_order_detail),(b.id_product),(b.name)
91 FROM ps_order_detail a join
92 ps_product_lang b on a.product_id=b.id_product GROUP BY(name)
93
94SELECT DISTINCT (a.id_order_detail),(b.id_product),(b.name), img.id_image
95 FROM ps_order_detail a join
96 ps_product_lang b on a.product_id=b.id_product
97 join ps_image img on img.id_product = b.id_product
98where img.cover = 1
99GROUP BY(name)