DROP PROCEDURE add_meta_description;
DELIMITER //
CREATE PROCEDURE add_meta_description()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE count_directions INT;
DECLARE id_direction INT DEFAULT NULL;
DECLARE city_from, city_to VARCHAR(100) DEFAULT NULL;
SELECT COUNT(*) INTO count_directions FROM aircrafts_in;
WHILE count_directions > 0 DO
SELECT id INTO id_direction FROM aircrafts_in ORDER BY id LIMIT i,1;
SELECT c.name INTO city_from FROM aircrafts_in ain INNER JOIN cities c ON c.id = ain.city_from WHERE ain.id = id_direction;
SELECT c.name INTO city_to FROM aircrafts_in ain INNER JOIN cities c ON c.id = ain.city_to WHERE ain.id = id_direction;
UPDATE aircrafts_in SET meta_description = (SELECT CONCAT('Самолет из ', city_from,' в ', city_to,'. Расписание рейсов с указанием времени вылета и прилета. Стоимость авиабилетов на самолет ', city_from, ' - ', city_to,'.'))
WHERE id = id_direction;
SET i = i+1;
SET count_directions = count_directions-1;
END WHILE;
END;
//
DROP PROCEDURE add_meta_keywords;
DELIMITER //
CREATE PROCEDURE add_meta_keywords()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE count_directions INT;
DECLARE id_direction INT DEFAULT NULL;
DECLARE city_from, city_to VARCHAR(100) DEFAULT NULL;
SELECT COUNT(*) INTO count_directions FROM aircrafts_in;
WHILE count_directions > 0 DO
SELECT id INTO id_direction FROM aircrafts_in ORDER BY id LIMIT i,1;
SELECT c.name INTO city_from FROM aircrafts_in ain INNER JOIN cities c ON c.id = ain.city_from WHERE ain.id = id_direction;
SELECT c.name INTO city_to FROM aircrafts_in ain INNER JOIN cities c ON c.id = ain.city_to WHERE ain.id = id_direction;
UPDATE aircrafts_in SET meta_keywords = (SELECT CONCAT('самолет, авиабилет, ',city_from,' ', city_to,' , авиа билет, билет на самолет, цена, расписание, стоимость, время вылета, прилета'))
WHERE id = id_direction;
SET i = i+1;
SET count_directions = count_directions-1;
END WHILE;
END;
//
DROP PROCEDURE trains_add_meta_description;
DELIMITER //
CREATE PROCEDURE trains_add_meta_description()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE count_directions INT;
DECLARE id_direction INT DEFAULT NULL;
DECLARE city_from, city_to VARCHAR(100) DEFAULT NULL;
SELECT COUNT(*) INTO count_directions FROM trains_in;
WHILE count_directions > 0 DO
SELECT id INTO id_direction FROM trains_in ORDER BY id LIMIT i,1;
SELECT c.name_genitive INTO city_from FROM trains_in tin INNER JOIN cities c ON c.id = tin.city_from WHERE tin.id = id_direction;
SELECT c.name_accusative INTO city_to FROM trains_in tin INNER JOIN cities c ON c.id = tin.city_to WHERE tin.id = id_direction;
UPDATE trains_in SET meta_description = (SELECT CONCAT('Поезд из ', city_from,' в ', city_to,' (стоимость жд билетов, расписание движения, проверка доступных мест, время в пути)'))
WHERE id = id_direction;
SET i = i+1;
SET count_directions = count_directions-1;
END WHILE;
END;
//
DROP PROCEDURE trains_add_meta_keywords;
DELIMITER //
CREATE PROCEDURE trains_add_meta_keywords()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE count_directions INT;
DECLARE id_direction INT DEFAULT NULL;
DECLARE city_from, city_to VARCHAR(100) DEFAULT NULL;
SELECT COUNT(*) INTO count_directions FROM trains_in;
WHILE count_directions > 0 DO
SELECT id INTO id_direction FROM trains_in ORDER BY id LIMIT i,1;
SELECT c.name INTO city_from FROM trains_in tin INNER JOIN cities c ON c.id = tin.city_from WHERE tin.id = id_direction;
SELECT c.name INTO city_to FROM trains_in tin INNER JOIN cities c ON c.id = tin.city_to WHERE tin.id = id_direction;
UPDATE trains_in SET meta_keywords = (SELECT CONCAT('поезд, ',city_from,', ', city_to,', ',city_from,'-', city_to,', жд билет, цена, расписание, стоимость, наличие мест, отправление, прибытие, время в пути'))
WHERE id = id_direction;
SET i = i+1;
SET count_directions = count_directions-1;
END WHILE;
END;
//