Создание сайтов evrodom-h.com и svit-comfort.com

База данных: all_video Таблица: video_news

Ищем 3000 записей:

SELECT * 
FROM `video_news` 
WHERE `id` <=104000
AND `site` LIKE CONVERT( _utf8 'stroyka'
USING utf8mb4 ) 
COLLATE utf8mb4_general_ci

Они пойдут для сайта evrodom-h.com, поэтому производим замену:

UPDATE `all_video`.`video_news` SET `site` = 'evrodom-h.com' WHERE `id` <=104000
AND `site` LIKE CONVERT( _utf8 'stroyka'
USING utf8mb4 ) 
COLLATE utf8mb4_general_ci;

Получили 3000 страниц на сайте (1+2999):

Теперь копируем папку /var/www/led/data/www/odsk.kiev.ua/video в папку /var/www/led/data/www/evrodom-h.com/*.*

Теперь в файл .htaccess добавляем такие строки:

RewriteEngine On
RewriteCond %{HTTP_HOST} ^www.evrodom-h.com
RewriteRule (.*) http://evrodom-h.com/$1 [R=301,L]
#video
RewriteRule ^video/([0-9]+)-(.*)/?$ /video/index.php?id=$1&url=$2 [L,QSA]
RewriteRule ^video/(.*)/([0-9]+)-(.*)/?$ /video/index.php?id=$2&url=$3 [L,QSA]
RewriteRule ^index.php?id=([0-9]+)  /video/index.php?id=11000$2 [L,QSA]
RewriteRule ^(.*)/([0-9]+)-(.*)/?$ /video/index.php?id=$2&url=$3 [L,QSA]
RewriteRule ^(.*)/(.*)/([0-9]+)-(.*)/?$ /video/index.php?id=$3&url=$4 [L,QSA]
#category
RewriteRule ^(.*)/$ /video/index_cat.php?url=$1 [L,QSA]
RewriteRule ^([a-zA-Z0-9_-]+)$ /video/index_cat.php?url=$1 [L,QSA]

Опубликуем 10 материалов

UPDATE  `all_video`.`video_news` SET  `published` =  '1' WHERE  `video_news`.`id` >=101000 AND  `video_news`.`id` < 101011

После этого в админке появятся записи:

Добавляем сайт в консоли гул: https://console.developers.google.com/project/radiant-rookery-857/apiui/...

Теперь подбираем материалы для категорий по такому принципу:

1. «Баня»

SELECT * 
FROM `video_news` 
WHERE `site` LIKE CONVERT( _utf8 'evrodom-h.com'
USING utf8mb4 ) 
COLLATE utf8mb4_general_ci
AND `keyword` LIKE CONVERT( _utf8 '%баня%'
USING utf8mb4 ) 
COLLATE utf8mb4_general_ci

Если нас все устраивает, то присваиваем категорию так:

UPDATE `all_video`.`video_news` SET `path` = '/banya/' WHERE `site` LIKE CONVERT( _utf8 'evrodom-h.com'
USING utf8mb4 ) 
COLLATE utf8mb4_general_ci
AND `keyword` LIKE CONVERT( _utf8 '%баня%'
USING utf8mb4 ) 
COLLATE utf8mb4_general_ci



2. «Покраска»

SELECT * 
FROM `video_news` 
WHERE `site` LIKE CONVERT( _utf8 'evrodom-h.com'
USING utf8mb4 ) 
COLLATE utf8mb4_general_ci
AND (
`keyword` LIKE CONVERT( _utf8 '%краск%'
USING utf8mb4 ) 
COLLATE utf8mb4_general_ci
OR `keyword` LIKE CONVERT( _utf8 '%покрас%'
USING utf8mb4 ) 
COLLATE utf8mb4_general_ci
)




UPDATE `all_video`.`video_news` SET `path` = '/pokraska/' WHERE `site` LIKE CONVERT( _utf8 'evrodom-h.com' USING utf8mb4 ) COLLATE utf8mb4_general_ci AND (
`keyword` LIKE CONVERT( _utf8 '%краск%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%покрас%' USING utf8mb4 ) COLLATE utf8mb4_general_ci)



3. «Двери»

SELECT * 
FROM `video_news` 
WHERE `site` LIKE CONVERT( _utf8 'evrodom-h.com'
USING utf8mb4 ) 
COLLATE utf8mb4_general_ci
AND (
`keyword` LIKE CONVERT( _utf8 '%двер%'
USING utf8mb4 ) 
COLLATE utf8mb4_general_ci
OR `keyword` LIKE CONVERT( _utf8 '%двер%'
USING utf8mb4 ) 
COLLATE utf8mb4_general_ci
)




UPDATE `all_video`.`video_news` SET `path` = '/dveri/' WHERE `site` LIKE CONVERT( _utf8 'evrodom-h.com' USING utf8mb4 ) COLLATE utf8mb4_general_ci AND (
`keyword` LIKE CONVERT( _utf8 '%двер%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%двер%' USING utf8mb4 ) COLLATE utf8mb4_general_ci)

4. «Крыша»

SELECT * 
FROM `video_news` 
WHERE `site` LIKE CONVERT( _utf8 'evrodom-h.com'
USING utf8mb4 ) 
COLLATE utf8mb4_general_ci
AND (
`keyword` LIKE CONVERT( _utf8 '%крыш%'
USING utf8mb4 ) 
COLLATE utf8mb4_general_ci
OR `keyword` LIKE CONVERT( _utf8 '%крыш%'
USING utf8mb4 ) 
COLLATE utf8mb4_general_ci
)




UPDATE `all_video`.`video_news` SET `path` = '/krysha/' WHERE `site` LIKE CONVERT( _utf8 'evrodom-h.com' USING utf8mb4 ) COLLATE utf8mb4_general_ci AND (
`keyword` LIKE CONVERT( _utf8 '%крыш%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%крыш%' USING utf8mb4 ) COLLATE utf8mb4_general_ci)

5. «Проекты»

SELECT * 
FROM `video_news` 
WHERE `site` LIKE CONVERT( _utf8 'evrodom-h.com'
USING utf8mb4 ) 
COLLATE utf8mb4_general_ci
AND (
`keyword` LIKE CONVERT( _utf8 'проект%'
USING utf8mb4 ) 
COLLATE utf8mb4_general_ci
OR `keyword` LIKE CONVERT( _utf8 'проект%'
USING utf8mb4 ) 
COLLATE utf8mb4_general_ci
)




UPDATE `all_video`.`video_news` SET `path` = '/object/' WHERE `site` LIKE CONVERT( _utf8 'evrodom-h.com' USING utf8mb4 ) COLLATE utf8mb4_general_ci AND (
`keyword` LIKE CONVERT( _utf8 'проект%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 'проект%' USING utf8mb4 ) COLLATE utf8mb4_general_ci
)

Заменили старую страницу: http://evrodom-h.com/object

UPDATE `all_video`.`video_news` SET `path` = '/developer/' WHERE `site` LIKE CONVERT( _utf8 'evrodom-h.com' USING utf8mb4 ) COLLATE utf8mb4_general_ci AND (
`keyword` LIKE CONVERT( _utf8 '%цена%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%цены%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%стоимост%' USING utf8mb4 ) COLLATE utf8mb4_general_ci
) AND `path` LIKE '' LIMIT 25

UPDATE `all_video`.`video_news` SET `path` = '/production/' WHERE `site` LIKE CONVERT( _utf8 'evrodom-h.com' USING utf8mb4 ) COLLATE utf8mb4_general_ci AND (
`keyword` LIKE CONVERT( _utf8 '%цена%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%цены%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%стоимост%' USING utf8mb4 ) COLLATE utf8mb4_general_ci
) AND `path` LIKE '' LIMIT 25

UPDATE `all_video`.`video_news` SET `path` = '/napravlen/' WHERE `site` LIKE CONVERT( _utf8 'evrodom-h.com' USING utf8mb4 ) COLLATE utf8mb4_general_ci AND (
`keyword` LIKE CONVERT( _utf8 '%цена%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%цены%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%стоимост%' USING utf8mb4 ) COLLATE utf8mb4_general_ci
) AND `path` LIKE '' LIMIT 25

UPDATE `all_video`.`video_news` SET `path` = '/aboutus/' WHERE `site` LIKE CONVERT( _utf8 'evrodom-h.com' USING utf8mb4 ) COLLATE utf8mb4_general_ci AND (
`keyword` LIKE CONVERT( _utf8 '%цена%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%цены%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%стоимост%' USING utf8mb4 ) COLLATE utf8mb4_general_ci
) AND `path` LIKE '' LIMIT 25

UPDATE `all_video`.`video_news` SET `path` = '/aboutcomp/' WHERE `site` LIKE CONVERT( _utf8 'evrodom-h.com' USING utf8mb4 ) COLLATE utf8mb4_general_ci AND (
`keyword` LIKE CONVERT( _utf8 '%цена%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%цены%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%стоимост%' USING utf8mb4 ) COLLATE utf8mb4_general_ci
) AND `path` LIKE '' LIMIT 25

По 10:

UPDATE `all_video`.`video_news` SET `path` = '/perspectiv/' WHERE `site` LIKE CONVERT( _utf8 'evrodom-h.com' USING utf8mb4 ) COLLATE utf8mb4_general_ci AND (
`keyword` LIKE CONVERT( _utf8 '%цена%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%цены%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%стоимост%' USING utf8mb4 ) COLLATE utf8mb4_general_ci
) AND `path` LIKE '' LIMIT 10

По 3:

UPDATE `all_video`.`video_news` SET `path` = '/sandr/' WHERE `site` LIKE CONVERT( _utf8 'evrodom-h.com' USING utf8mb4 ) COLLATE utf8mb4_general_ci AND (
`keyword` LIKE CONVERT( _utf8 '%цена%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%цены%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%стоимост%' USING utf8mb4 ) COLLATE utf8mb4_general_ci
) AND `path` LIKE '' LIMIT 3

UPDATE `all_video`.`video_news` SET `path` = '/techno/' WHERE `site` LIKE CONVERT( _utf8 'evrodom-h.com' USING utf8mb4 ) COLLATE utf8mb4_general_ci AND (
`keyword` LIKE CONVERT( _utf8 '%цена%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%цены%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%стоимост%' USING utf8mb4 ) COLLATE utf8mb4_general_ci
) AND `path` LIKE '' LIMIT 3

UPDATE `all_video`.`video_news` SET `path` = '/sitecart/' WHERE `site` LIKE CONVERT( _utf8 'evrodom-h.com' USING utf8mb4 ) COLLATE utf8mb4_general_ci AND (
`keyword` LIKE CONVERT( _utf8 '%цена%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%цены%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%стоимост%' USING utf8mb4 ) COLLATE utf8mb4_general_ci
) AND `path` LIKE '' LIMIT 3

UPDATE `all_video`.`video_news` SET `path` = '/contacts/' WHERE `site` LIKE CONVERT( _utf8 'evrodom-h.com' USING utf8mb4 ) COLLATE utf8mb4_general_ci AND (
`keyword` LIKE CONVERT( _utf8 '%цена%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%цены%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%стоимост%' USING utf8mb4 ) COLLATE utf8mb4_general_ci
) AND `path` LIKE '' LIMIT 3

UPDATE `all_video`.`video_news` SET `path` = '/foto/' WHERE `site` LIKE CONVERT( _utf8 'evrodom-h.com' USING utf8mb4 ) COLLATE utf8mb4_general_ci AND (
`keyword` LIKE CONVERT( _utf8 '%цена%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%цены%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%стоимост%' USING utf8mb4 ) COLLATE utf8mb4_general_ci
) AND `path` LIKE '' LIMIT 3

UPDATE `all_video`.`video_news` SET `path` = '/documents/' WHERE `site` LIKE CONVERT( _utf8 'evrodom-h.com' USING utf8mb4 ) COLLATE utf8mb4_general_ci AND (
`keyword` LIKE CONVERT( _utf8 '%цена%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%цены%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%стоимост%' USING utf8mb4 ) COLLATE utf8mb4_general_ci
) AND `path` LIKE '' LIMIT 3

Теперь группируем категории:

SELECT path, COUNT( path ) AS count_path
FROM video_news
WHERE `site` LIKE CONVERT( _utf8 'evrodom-h.com'
USING utf8mb4 ) 
COLLATE utf8mb4_general_ci
GROUP BY path;

Берем дамп:

INSERT INTO `video_news` (`path`, `path`) VALUES
('', 2622),
('/aboutcomp/', 25),
('/aboutus/', 25),
('/banya/', 73),
('/contacts/', 3),
('/developer/', 25),
('/documents/', 3),
('/dveri/', 28),
('/foto/', 3),
('/krysha/', 22),
('/napravlen/', 25),
('/object/', 19),
('/perspectiv/', 10),
('/pokraska/', 80),
('/production/', 25),
('/sandr/', 3),
('/sitecart/', 3),
('/techno/', 6);

И добавляем категории:

INSERT INTO `video_categories` (`site`, `alias`) VALUES
('evrodom-h.com', '/aboutcomp/'),
('evrodom-h.com', '/aboutus/'),
('evrodom-h.com', '/banya/'),
('evrodom-h.com', '/contacts/'),
('evrodom-h.com', '/developer/'),
('evrodom-h.com', '/documents/'),
('evrodom-h.com', '/dveri/'),
('evrodom-h.com', '/foto/'),
('evrodom-h.com', '/krysha/'),
('evrodom-h.com', '/napravlen/'),
('evrodom-h.com', '/object/'),
('evrodom-h.com', '/perspectiv/'),
('evrodom-h.com', '/pokraska/'),
('evrodom-h.com', '/production/'),
('evrodom-h.com', '/sandr/'),
('evrodom-h.com', '/sitecart/'),
('evrodom-h.com', '/techno/');

Теперь делаем их публичыми

UPDATE `all_video`.`video_categories` SET `published` = '1' WHERE `site` LIKE ‘evrodom-h.com’

Теперь поработаем немного с сайтом svit-comfort.com

UPDATE `all_video`.`video_news` SET `path` = '/obshivka/' WHERE `site` LIKE CONVERT( _utf8 'svit-comfort.com' USING utf8mb4 ) COLLATE utf8mb4_general_ci AND (
`keyword` LIKE CONVERT( _utf8 '%обши%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%вагон%' USING utf8mb4 ) COLLATE utf8mb4_general_ci
) AND `path` LIKE ''

11 записей

UPDATE `all_video`.`video_news` SET `path` = '/derevo/' WHERE `site` LIKE CONVERT( _utf8 'svit-comfort.com' USING utf8mb4 ) COLLATE utf8mb4_general_ci AND (
`keyword` LIKE CONVERT( _utf8 '%обши%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%дерев%' USING utf8mb4 ) COLLATE utf8mb4_general_ci
) AND `path` LIKE ''

65 записей

UPDATE `all_video`.`video_news` SET `path` = '/derevo/' WHERE `site` LIKE CONVERT( _utf8 'svit-comfort.com' USING utf8mb4 ) COLLATE utf8mb4_general_ci AND (
`keyword` LIKE CONVERT( _utf8 '% печь%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%дерев%' USING utf8mb4 ) COLLATE utf8mb4_general_ci
) AND `path` LIKE ''

И еще

UPDATE `all_video`.`video_news` SET `path` = '/derevo/' WHERE `site` LIKE CONVERT( _utf8 'stroyka' USING utf8mb4 ) COLLATE utf8mb4_general_ci AND (
`keyword` LIKE CONVERT( _utf8 '% печь%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%дерев%' USING utf8mb4 ) COLLATE utf8mb4_general_ci
) AND `path` LIKE '' LIMIT 10

17 записей

UPDATE `all_video`.`video_news` SET `path` = '/kanalizaciya/' WHERE `site` LIKE CONVERT( _utf8 'svit-comfort.com' USING utf8mb4 ) COLLATE utf8mb4_general_ci AND (`keyword` LIKE CONVERT( _utf8 '% печь%' USING utf8mb4 ) COLLATE utf8mb4_general_ci OR `keyword` LIKE CONVERT( _utf8 '%канализ%' USING utf8mb4 ) COLLATE utf8mb4_general_ci
) AND `path` LIKE ''

19 записей

Группируем:

SELECT path, COUNT( path ) AS count_path
FROM video_news
WHERE `site` LIKE CONVERT( _utf8 'svit-comfort.com'
USING utf8mb4 ) 
COLLATE utf8mb4_general_ci
GROUP BY path;

Добавляем категории:

INSERT INTO `video_categories` (`keyword`, `alias`) VALUES
('Делаем сами','/delaem-sami/'),
('Делаем из дерева','/derevo/'),
('Как сделать?','/kak/'),
('Канализация','/kanalizaciya/'),
('Клеим обои','/oboi/'),
('Обшивка стен','/obshivka/'),
('Строим печь','/pech/'),
('Как сделать потолок','/potolok/'),
('Штукатурим стены','/shukatur/');

Теперь создаем главную страницу сайта. Для этого создаем файл, например для сайта evrodom-h.com: evrodom-h.com/o-kompanii.php

Прописываем в .htaccess строку:

DirectoryIndex o-kompanii.php

И в этот файл помещаем php код:

<?php
echo file_get_contents("http://evrodom-h.com/video/1100300-evrodom-holding/");
?>

Т.е. открывать конкретную страницу сайта.

Для того, чтобы canonical указывал на главную страницу, а не на /video/1100300-evrodom-holding/ пометим ее в БД специальной меткой: autoworkscript_info = «glavn», а для того, чтобы на нее автоматически не добавлялись заметки пометим как autoworkscript_info = «glavn».

Можем посмотреть все главные страницы так:

SELECT * 
FROM `video_news` 
WHERE `autoworkscript_info` LIKE CONVERT( _utf8 'glavn'
USING utf8mb4 ) 
COLLATE utf8mb4_general_ci

В данном случае получаем 3 страницы: