БЛОГ НА HUSL


Запрос к двум таблицам MYSQL для вывода в yml файл

Автор вопроса: Rus

Есть две таблицы:

CREATE TABLE `properties` (
  `id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED NOT NULL,
  `title_prop` varchar(255) NOT NULL,
  `value_prop` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

И:

CREATE TABLE `product` (
  `id` int(10) UNSIGNED NOT NULL,
  `category_id` int(10) UNSIGNED NOT NULL,
  `brand_id` tinyint(5) UNSIGNED DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `alias` varchar(255) NOT NULL,
  `content` text,
  `price` float NOT NULL DEFAULT '0',
  `stock` int(11) DEFAULT NULL,
  `old_price` float NOT NULL DEFAULT '0',
  `status` enum('no','yes') NOT NULL DEFAULT 'yes',
  `meta_title` varchar(255) DEFAULT NULL,
  `keywords` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `img` varchar(255) NOT NULL DEFAULT 'no_image.jpg',
  `hit` enum('no','yes') NOT NULL DEFAULT 'yes',
  `yml` enum('no','yes') NOT NULL DEFAULT 'yes',
  `articul` varchar(255) NOT NULL,
  `weight` int(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Делаю запрос к ним таким образом:

SELECT `product`.`id`, 
               `product`.`category_id`, 
               `product`.`title` as `product_title`, 
               `product`.`alias`, 
               `product`.`img`, 
               `product`.`content`, 
               `product`.`price`, 
               `brand`.`title` as `band_title`, 
               `properties`.`product_id`, 
               `properties`.`title_prop`, 
               `properties`.`value_prop` 
                FROM `product` JOIN `brand` ON `brand`.`id` = `product`.`brand_id` 
                JOIN `properties` ON `properties`.`product_id` = `product`.`id` 
                WHERE `product`.`status` = 'yes' AND `product`.`yml` = 'yes'

Суть: нужно вывести в файл yml информацию о товаре + его характеристики в тег . Если добавить в запрос GROUP BY, то естественно не получу все параметры товара, а только по одному на каждый товар, а если оставить как есть, то получаю дубли товара но с разными параметрами, типа:

<offer id="12345" type="vendor.model">
<name>Название товара 1</name>
<vendor>Производитель</vendor>
<url>https://test.ru/1</url>
<price>1000</price>
<currencyId>RUR</currencyId>
<categoryId>54321</categoryId>
<delivery>true</delivery>
<delivery-options>
<option cost="500" days="1-3" order-before="18"/>
</delivery-options>
<param name = "Количество в упаковке">4</param>
<picture>https:/test.ru/82529.jpg</picture>
<sales_notes>Онлайн оплата VisaMastercard Наличные Счет</sales_notes>
<store>true</store>
<manufacturer_warranty>true</manufacturer_warranty>
</offer>

<offer id="12345" type="vendor.model">
<name>Название товара 1</name>
<vendor>Производитель</vendor>
<url>https://test.ru/1</url>
<price>1000</price>
<currencyId>RUR</currencyId>
<categoryId>54321</categoryId>
<delivery>true</delivery>
<delivery-options>
<option cost="500" days="1-3" order-before="18"/>
</delivery-options>
<param name = "Бренд">Какой-то бренд</param>
<picture>https:/test.ru/82529.jpg</picture>
<sales_notes>Онлайн оплата VisaMastercard Наличные Счет</sales_notes>
<store>true</store>
<manufacturer_warranty>true</manufacturer_warranty>
</offer>

<offer id="12345" type="vendor.model">
<name>Название товара 1</name>
<vendor>Производитель</vendor>
<url>https://test.ru/1</url>
<price>1000</price>
<currencyId>RUR</currencyId>
<categoryId>54321</categoryId>
<delivery>true</delivery>
<delivery-options>
<option cost="500" days="1-3" order-before="18"/>
</delivery-options>
<param name = "Материал основы">Какой-то материал</param>
<picture>https:/test.ru/82529.jpg</picture>
<sales_notes>Онлайн оплата VisaMastercard Наличные Счет</sales_notes>
<store>true</store>
<manufacturer_warranty>true</manufacturer_warranty>
</offer>

А хотелось бы конечно по нормальному:

<offer id="12345" type="vendor.model">
<name>Название товара 1</name>
<vendor>Производитель</vendor>
<url>https://test.ru/1</url>
<price>1000</price>
<currencyId>RUR</currencyId>
<categoryId>54321</categoryId>
<delivery>true</delivery>
<delivery-options>
<option cost="500" days="1-3" order-before="18"/>
</delivery-options>
<param name = "Количество в упаковке">4</param>
<param name = "Бренд">Какой-то бренд</param>
<param name = "Материал основы">Какой-то материал</param>
<picture>https:/test.ru/82529.jpg</picture>
<sales_notes>Онлайн оплата VisaMastercard Наличные Счет</sales_notes>
<store>true</store>
<manufacturer_warranty>true</manufacturer_warranty>
</offer>

Чуть не забыл, код:

foreach ($product as $row) {
                $out .= '<offer id="'.$row['id'].'" type="vendor.model">'."\r\n";
                // Преобразование в html сущности знаков в названиях
                $row['product_title'] = str_replace('"', "&quot;", $row['product_title']);
                $row['product_title'] = str_replace("&", "&amp;", $row['product_title']);
                $row['product_title'] = str_replace(">", "&gt;", $row['product_title']);
                $row['product_title'] = str_replace("<", "&lt;", $row['product_title']);
                $row['product_title'] = str_replace("'", "&apos;", $row['product_title']);

                $row['band_title'] = str_replace('"', "&quot;", $row['band_title']);
                $row['band_title'] = str_replace("&", "&amp;", $row['band_title']);
                $row['band_title'] = str_replace(">", "&gt;", $row['band_title']);
                $row['band_title'] = str_replace("<", "&lt;", $row['band_title']);
                $row['band_title'] = str_replace("'", "&apos;", $row['band_title']);

                // Название товара
                $out .= '<name>'.$row['product_title'].'</name>'."\r\n";
                // Брэнд
                $out .= '<vendor>'.$row['band_title'].'</vendor>'."\r\n";
                // Модель


                $row['title_prop'] = str_replace('"', "&quot;", $row['title_prop']);
                $row['title_prop'] = str_replace("&", "&amp;", $row['title_prop']);
                $row['title_prop'] = str_replace(">", "&gt;", $row['title_prop']);
                $row['title_prop'] = str_replace("<", "&lt;", $row['title_prop']);
                $row['title_prop'] = str_replace("'", "&apos;", $row['title_prop']);

                if ($row['title_prop'] == 'Модель') {
                    $out .= '<model>'.$row['value_prop'].'</model>'."\r\n";
                }


                // URL страницы товара на сайте магазина
                $out .= '<url>'.PATH.'/product/'.$row['alias'].'</url>'."\r\n";
                // Цена, предполагается что в БД хранится цена и цена со скидкой
                $out .= '<price>'.$row['price'].'</price>'."\r\n";
                // Валюта товара
                $out .= '<currencyId>'.$currency->code.'</currencyId>'."\r\n";
                // ID категории
                $out .= '<categoryId>'.$row['category_id'].'</categoryId>'."\r\n";
                // Курьерская доставка
                $out .= '<delivery>'.$delivery.'</delivery>'."\r\n";
                $out .= '<delivery-options>'."\r\n";
                $out .= '<option cost="'.$price_delivery.'" days="'.$interval_day_delivery.'" order-before="'.$time_delivery.'"/>'."\r\n";
                $out .= '</delivery-options>'."\r\n";
                // Параметры товара


                $row['value_prop'] = str_replace('"', "&quot;", $row['value_prop']);
                $row['value_prop'] = str_replace("&", "&amp;", $row['value_prop']);
                $row['value_prop'] = str_replace(">", "&gt;", $row['value_prop']);
                $row['value_prop'] = str_replace("<", "&lt;", $row['value_prop']);
                $row['value_prop'] = str_replace("'", "&apos;", $row['value_prop']);


                $out .= '<param name = "'.$row['title_prop'].'">'.$row['value_prop'].'</param>'."\r\n";



                // Изображения товара, до 10 ссылок
                $out .= '<picture>'.PATH.'images/'.$row['img'].'</picture>'."\r\n";
                // Описание товара, максимум 3000 символов
                if(!empty($row['content'])){
                    $out .= '<description><![CDATA['.stripslashes($row['content']).']]></description>'."\r\n";
                }
                $out .= '<sales_notes>'.$type_pay.'</sales_notes>'."\r\n";
                $out .= '<store>'.$buy_order.'</store>'."\r\n";
                $out .= '<manufacturer_warranty>'.$manufacturer_warranty.'</manufacturer_warranty>'."\r\n";
                $out .= '</offer>'."\r\n";
            }

Результирующий массив:

Array
(
    [0] => Array
        (
            [id] => 54532
            [category_id] => 4438
            [product_title] => Название товара 1
            [alias] => url-address
            [img] => 82402.jpg
            [content] => 
            [price] => 6050
            [band_title] => Название бренда
            [product_id] => 54532
            [title_prop] => Количество в упаковке
            [value_prop] => 4
        )

    [1] => Array
        (
            [id] => 54532
            [category_id] => 4438
            [product_title] => Название товара 1
            [alias] => url-address
            [img] => 82402.jpg
            [content] => 
            [price] => 6050
            [band_title] => Название бренда
            [product_id] => 54532
            [title_prop] => Материал основы
            [value_prop] => Флизелиновая
        )

    [2] => Array
        (
            [id] => 54532
            [category_id] => 4438
            [product_title] => Название товара 1
            [alias] => url-address
            [img] => 82402.jpg
            [content] => 
            [price] => 6050
            [band_title] => Название бренда
            [product_id] => 54532
            [title_prop] => Страна
            [value_prop] => Италия
        )
)

Подскажите, что лучше - скорректировать правильный один запрос с объединением (и как он при этом должен выглядеть), или лучше в два запроса, но тогда как в php их обрабатывать? В два foreach - пробовал, все-равно создает только с одним для каждого товара...


Источник

Ответы (2 шт):

Автор решения: Vladimir Ignatenko

Можно попробовать использовать функцию JSON_ARRAYAGG если конечно ваша версия MySQL это позволяет. Запрос будет вида

SELECT `product`.`id`, 
               `product`.`category_id`, 
               `product`.`title` as `product_title`, 
               `product`.`alias`, 
               `product`.`img`, 
               `product`.`content`, 
               `product`.`price`, 
               `brand`.`title` as `band_title`, 
                JSON_ARRAYAGG(JSON_ARRAY(`properties`.`title_prop`, properties`.`value_prop` )) AS `properties`
                FROM `product` JOIN `brand` ON `brand`.`id` = `product`.`brand_id` 
                JOIN `properties` ON `properties`.`product_id` = `product`.`id` 
                WHERE `product`.`status` = 'yes' AND `product`.`yml` = 'yes'
                GROUP BY `product`.`id`

Здесь функция JSON_ARRAY собирает массив вида [name, title] из двух полей из одной записи свойства, а JSON_ARRAYAGG собирает массив для всех свойств одного продукта.

По идее это упростит вам задачу.

Насчет кода PHP тоже есть замечания. str_replace можно вызывать, указывая в первом и втором аргументе массивы. В первом указываются строки что искать, а во втором строки на что менять - в соответствии с индексами в этих масивов. Таким образом один такой вызов заменяет несколько вызов подряд.

Но это так, для общей информации. В вашем случае правильнее использовать функцию html_entity_decode

→ Ссылка
Автор решения: ValNik

Можете попробовать применить GROUP_CONCAT. Эта функция применима в версии 5.5

Форматирование выходного результата - по Вашим потребностям.

Пример:

create table product (id int,category_id int ,title varchar(10));
insert into product values
 (21,10,'Product 1')
,(22,10,'Product 2')
,(23,11,'Product 3')
;
CREATE TABLE properties (
  id int(10) UNSIGNED NOT NULL,
  product_id int(10) UNSIGNED NOT NULL,
  title_prop varchar(255) NOT NULL,
  value_prop text NOT NULL
);
insert into properties values
 (101,21,'Размер','43')
,(102,21,'Вес','12')
,(103,22,'Длина','1м')
,(104,23,'Ширина','2')
,(105,23,'Высота','0.2')
,(105,23,'Вес','10кг')
,(105,23,'Цвет','зеленый')
;

select p.id,p.title
  ,concat('('
    , group_concat(concat('[',title_prop,']','=>"',o.value_prop,'"') 
        order by o.id separator ' \\n')
  ,')') props
from product p
left  join properties o on o.product_id=p.id
group by p.id

Получится

id title props
21 Product 1 ([Размер]=>"43" \n[Вес]=>"12")
22 Product 2 ([Длина]=>"1м")
23 Product 3 ([Ширина]=>"2" \n[Высота]=>"0.2" \n[Вес]=>"10кг" \n[Цвет]=>"зеленый")
→ Ссылка

licensed under cc by-sa 3.0 with attribution.