Multi-shop. Article for beginner web programmers

Many interesting technical solutions arise as a result of solving interesting problems. And who invent or create such problems for engineers? The answer is of course the users. This article is just about one such interesting problem and its solution.

So. In general, the problem, according to the user, looks like the need to create one main online store and several Additional. The main store has a full database of goods. The goods of Additional stores are formed by requesting a list of goods from the Main store. The main store, having received its identifier when requesting from the Additional Store, returns the desired item.

Now consider the problem through the eyes of an engineer, given that the base of goods of the Main and Additional Store are on the same server. We do not know the number of Additional Stores, but it is obvious that the load on the Main Store will be large. A large number of different goods does not allow us to accurately form the structure of the tables with the goods. Product properties vary greatly depending on its category. Summarizing the data, the task for the designer of the Main Store is:
')
Design the Main Store database so that the product sampling rate is very fast, and the product properties, its attributes, are dynamic.

Divide and rule!

From "given", there are two entities. This is the Main Store and Optional. Let's call them so

Primary - PrimaryShop
Additional - SecondaryShop

It is also obvious that from the inside of the Additional Store, it will be necessary to refer both to the internal data and to the external data, to the data of the Main Store. For convenience, we will write the following two functions:

/* *    ,            . * @shopId -   * @return false | object PrimaryGoods */ function PrimaryShop($shop = null) { global $primaryshop, $shopId; /* *  ,          *  ,    ,   *   shopId     *  ,      ,    */ if (!isset($shop) && !isset($shopId)) { die("  shopId  "); } else { $shopId = isset($shop) ? $shop : $shopId; } if (Database::Setup(Config::Get("primaryDbHost"), Config::Get("primaryDbName"), Config::Get("primaryDbUser"), Config::Get("primaryDbPass"))->Connect()) { return isset($primaryshop) ? $primaryshop : new PrimaryGoods($shopId); } else { die("      "); } } /* *    ,            * @return false | object SecondaryGoods */ function SecondaryShop() { if (Database::Setup(Config::Get("secondaryDbHost"), Config::Get("secondaryDbName"), Config::Get("secondaryDbUser"), Config::Get("secondaryDbPass"))->Connect()) { return new SecondaryGoods(); } else { die("      "); } } 

Accordingly, SecondaryGoods and PrimaryGoods classes that implement the methods of working with the goods of the respective stores.

And it's all? Of course not. These are the wrapper functions. They do not bring us to the ultimate goal. Let's try to speculate further. As a rule, the online store shows the product to the user in two modes. List view mode and product card view mode. The frequency of receiving data for the list view mode is obviously very high, much higher than the frequency of receiving data for viewing the item card. It follows a simple conclusion. If there is a special table in the Main database, a table with prepared data for displaying them in the product list view mode, the speed of data acquisition will be greater than generating this data by combining several tables into one. The data for viewing the goods in the card mode are formed in a standard way by combining several tables into one.

An example of a query to the main database, by combining several tables into one. Such a query will a priori be slower than a simple query, without connections.

 --    SELECT * FROM `data_goods` AS `a` LEFT JOIN `data_goods_price` AS `b` ON `b`.`id` = `a`.`priceId` LEFT JOIN `data_goods_images` AS `c` ON `c`.`id` = `a`.`imagesId` LEFT JOIN `data_goods_attr` AS `d` ON `d`.`id` = `a`.`attrId` --  ,   SELECT * FROM `data_goods_short` 

To summarize Appeal to a specific store is carried out through the functions of the wrapper. To build a page with a list of goods we create a special table specially designed for this purpose.

Multi-property

How to make product A have the following properties

• Small picture
• Big picture
• Dimensions
• Material
• Wholesale price
• Retail price

Product B has the following properties.

• Average picture
• Big picture
• Retail price
• Retail price, special

There is no possibility in advance to lay all possible properties of the product. Well, in fact, neither do the same table with empty columns: Col1, Col2, ... ColN.

The first thing that comes to mind is to make an additional table reference book. This table will store a set of possible attributes of the product, depending on the product group. The process of obtaining a product card is obtained in two stages. Step one: we get a list of possible attributes. Stage two: in accordance with this list, we turn to the necessary tables and collect information. You understand that the process is not fast. Is he good? The answer is no.

If you look at the contents of the product card in more detail, you can group the properties of the product in two tables. Let table one, data_goods_images contain a list of all product images, and data_goods_attr table contain additional attributes.

 CREATE TABLE IF NOT EXISTS `data_goods_full` ( `id` int(11) NOT NULL AUTO_INCREMENT, `goodsId` int(11) NOT NULL, `categoryId` int(11) NOT NULL, `shopId` int(11) NOT NULL, `article` varchar(150) NOT NULL, `intro` varchar(255) NOT NULL, `name` varchar(150) NOT NULL, `description` text NOT NULL, PRIMARY KEY (`id`), KEY `categoryId` (`categoryId`), KEY `goodsId` (`goodsId`), KEY `shopId` (`shopId`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=501 ; CREATE TABLE IF NOT EXISTS `data_goods_images` ( `id` int(11) NOT NULL AUTO_INCREMENT, `goodsId` int(11) NOT NULL, `shopId` int(11) NOT NULL, `type` enum('','','') NOT NULL, `value` varchar(150) NOT NULL, PRIMARY KEY (`id`), KEY `goodsId` (`goodsId`), KEY `shopId` (`shopId`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `data_goods_attr` ( `id` int(11) NOT NULL AUTO_INCREMENT, `goodsId` int(11) NOT NULL, `type` enum('','','','') DEFAULT NULL, `value` varchar(150) NOT NULL, PRIMARY KEY (`id`), KEY `goodsId` (`goodsId`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

Notice the type field in the data_goods_images table and data_goods_attr. This is an ENUM field. This type can take a value from the list. Now, having made a request and connecting these three tables in it, we will get all the characteristics of the product.

 SELECT * FROM `data_goods_full` AS `a` LEFT JOIN `data_goods_images` AS `b` ON `b`.`goodsId` = `a`.`goodsId` LEFT JOIN `data_goods_attr` AS `c` ON `c`.`goodsId` = `a`.`goodsId` 

We can, also, obtain any specific properties, separately, without producing connections. For example, I want to receive only Small pictures, product B. Product identifier, let it be equal to 100

 SELECT * FROM `data_goods_images` WHERE `type` = '' AND `goodsId` = '100' 

Now let's go back to the question that was voiced at the beginning of the chapter. How to make sure that product A has some properties, and product B has others. In our case, you need to add additional types of product properties to the data_goods_attr table, such as: retail price, wholesale price and retail price, special price. You can do this with the following command:

 ALTER TABLE `data_goods_attr` CHANGE `type` `type` ENUM('','','','',' ',' ',' , ') 

Now add to the database product A and product B (goodsId 100 and goodsId 101)

 --     INSERT INTO `data_goods_full` (`id`, `goodsId`, `categoryId`, `shopId`, `article`, `intro`, `name`, `description`) VALUES (null, '100', '1', '1', '-100', ' ', ' ', ''); --   INSERT INTO `data_goods_images` (`id`, `goodsId`, `shopId`, `type`, `value`) VALUES (null, '100', '1', '1', '', 'small_a.png'), (null, '100', '1', '1', '', 'big_a.png'); --   INSERT INTO `data_goods_attr` (`id`, `goodsId`, `shopId`, `type`, `value`) VALUES (null, '100', '1', '1', '', 'XL'), (null, '100', '1', '1', '', ''), (null, '100', '1', '1', ' ', '100 .'), (null, '100', '1', '1', ' ', '125 .'); --     ,     --     INSERT INTO `data_goods_full` (`id`, `goodsId`, `categoryId`, `shopId`, `article`, `intro`, `name`, `description`) VALUES (null, '101', '1', '1', '-101', ' ', ' ', ''); --   INSERT INTO `data_goods_images` (`id`, `goodsId`, `shopId`, `type`, `value`) VALUES (null, '101', '1', '1', '', 'midle_b.png'), (null, '101', '1', '1', '', 'big_b.png'); --   INSERT INTO `data_goods_attr` (`id`, `goodsId`, `shopId`, `type`, `value`) VALUES (null, '101', '1', '1', ' ', '125 .'), (null, '101', '1', '1', ' , ', '120 .'); 

Well, now, let's read, all that we brought to the database. After small
manipulating the result of the query, we get the following:

 Array ( [property] => stdClass Object ( [id] => 1 [categoryId] => 1 [article] => -100 [intro] =>  [name] =>   [text] => ) [images] => Array ( [0] => stdClass Object ( [imageType] =>  [imageValue] => small_a.png ) [1] => stdClass Object ( [imageType] =>  [imageValue] => big_a.png ) ) [attributes] => Array ( [0] => stdClass Object ( [attrType] =>  [attrValue] => XL ) [1] => stdClass Object ( [attrType] =>  [attrValue] =>  ) [2] => stdClass Object ( [attrType] =>   [attrValue] => 100 . ) [3] => stdClass Object ( [attrType] =>   [attrValue] => 125 . ) ) ) Array ( [property] => stdClass Object ( [id] => 2 [categoryId] => 1 [article] => -101 [intro] =>  [name] =>   [text] => ) [images] => Array ( [0] => stdClass Object ( [imageType] =>  [imageValue] => midle_b.png ) [1] => stdClass Object ( [imageType] =>  [imageValue] => big_b.png ) ) [attributes] => Array ( [0] => stdClass Object ( [attrType] =>   [attrValue] => 125 . ) [1] => stdClass Object ( [attrType] =>  ,  [attrValue] => 120 . ) ) ) 

Conclusion

In order to work with fields of enum type, delete and add properties, you need to be able to get a list of the values ​​of these fields. You can get this list using the SQL DESCRIBE command. Below, I will give an example of such a function:

  /* *    ,    *  type   images */ function GetImagesSet() { $query = "\n DESCRIBE `data_goods_images` `type`"; if ($result = Database::Exec($query)->Read(0, "Type")) { $result = str_replace(array("enum", "(", ")", "'"), array("", "", "", ""), $result); return $result ? explode(",", $result) : $result; } } /* *       . * , ..  .. */ function AddImagesSet($type = null) { if (isset($type)) { $result = $this->GetImagesSet(); if (!empty($result)) { foreach ($result as $item) { $enum[] = "'".$item."'"; } $enum = implode(",", $enum).","; } else { $enum = ""; } $enum = $enum.str_replace(" ", "", $type); $query = "\n ALTER TABLE `d ata_goods_images` CHANGE `type` `type` ENUM(".$enum.")"; return Database::Exec($query)->Read(); } else { return false; } } /* *    . * @example: '', '', '' DelImagesSet('') => '', '' * @return true | flase */ function DelImagesSet($type = null) { if (isset($type)) { foreach(self::GetImagesSet() as $value) { if ($type != $value) { $enum[] = "'".$value."'"; } } $query = "\n ALTER TABLE `data_goods_images` CHANGE `type` `type` ENUM(".implode(",", $enum).")"; return Database::Exec($query)->Read(); } else { return false; } } 

You can also download all in one file