Материал из CB Wiki
Практические моменты касательно работы с БД КлипБукета.
Создание и удаление БД
Перед установкой КлипБукета необходимо сначала создать БД и назначить ей пользователя с правами доступа. Коннектимся к mysql: mysql -uroot -p и сначала создаем юзера с паролем:
CREATE USER 'yurtube5'@'localhost' IDENTIFIED BY '*********';
Затем создаем БД, даем на нее юзеру права:
CREATE DATABASE yurtube5;
GRANT ALL PRIVILEGES ON yurtube5.* TO 'yurtube'@'localhost';
FLUSH PRIVILEGES;
Смотрим что получилось:
SHOW DATABASES;
Удаляем БД, если надо:
drop database yurtube5;
Бекап и копирование БД
Текущее состояние БД можно задампить:
mysqldump -uroot -p yurtube5 > yurtube5_8-03-2024.sql
А затем залить заново (перед заливкой удаляем БД, создаем заново, даем права):
mysqld -uroot -p yurtube5 < yurtube5_8-03-2024.sql
Для разработки надежнее работать с копией актуальной БД. Для этого сначала создаем ее:
CREATE DATABASE yurtube5dev;
GRANT ALL PRIVILEGES ON yurtube5dev.* TO 'yurtube'@'localhost';
FLUSH PRIVILEGES;
Затем заливаем в нее дамп актуальной БД:
mysqld -uroot -p yurtube5dev < yurtube5_8-03-2024.sql
В includes/config.php меняем имя БД на имя копии и проверяем движок.
Удаление добавленного языка
Сначала выясняем каков ИД интересующего языка:
USE yurtube5dev;
SELECT language_id,language_name FROM cb_languages;
Далее зная language_id (допустим, тут он 5) удаляем все итемсы и сам язык:
DELETE from cb_languages_translations WHERE language_id=5;
DELETE from cb_languages WHERE language_id=5;
Или даже так (последний из добавленных языков имеет максимальный language_id):
DELETE from cb_languages_translations WHERE language_id=(SELECT MAX(language_id) from cb_languages);
DELETE from cb_languages WHERE language_id=(SELECT MAX(language_id) from cb_languages);
Комплексный запрос к 2 таблицам
Запрос к таблицам cb_video и cb_video_thumbs - вывод всех полей для которых есть общий одинаковый videoid. Запрос для получения инфы, из которой будем строить превью видеофайла и параметры для дальнейшей обработки:
SELECT * FROM cb_video,cb_video_thumbs WHERE cb_video.videoid=6 AND cb_video_thumbs.videoid=6;
SELECT * FROM cb_video,cb_video_thumbs WHERE cb_video.videoid=6 AND cb_video_thumbs.videoid=6;
+---------+--------------+----------------+-------------+----------+--------+-------------------------------+-----------------+-----------+----------------+---------------------------------------------------+-----------+----------+---------------------+---------+-----------------+--------+----------+-----------+----------------+----------------+----------------+----------------+----------+---------------+--------------+--------+-----------------+----------------+-------+---------------------+---------------------+---------+----------+------------+---------------+------------+-----------+-------------+-------------+------------------+---------------+----------------+----------------+-------------+------------+--------------------+-----------------+----------------+------------------+---------+------------+------+-----------+---------+------+
| videoid | videokey | video_password | video_users | username | userid | title | file_name | file_type | file_directory | description | broadcast | location | datecreated | country | allow_embedding | rating | rated_by | voter_ids | allow_comments | comment_voting | comments_count | last_commented | featured | featured_date | allow_rating | active | favourite_count | playlist_count | views | last_viewed | date_added | flagged | duration | status | default_thumb | embed_code | downloads | uploader_ip | video_files | file_server_path | video_version | thumbs_version | re_conv_status | is_castable | bits_color | subscription_email | age_restriction | default_poster | default_backdrop | videoid | resolution | num | extension | version | type |
+---------+--------------+----------------+-------------+----------+--------+-------------------------------+-----------------+-----------+----------------+---------------------------------------------------+-----------+----------+---------------------+---------+-----------------+--------+----------+-----------+----------------+----------------+----------------+----------------+----------+---------------+--------------+--------+-----------------+----------------+-------+---------------------+---------------------+---------+----------+------------+---------------+------------+-----------+-------------+-------------+------------------+---------------+----------------+----------------+-------------+------------+--------------------+-----------------+----------------+------------------+---------+------------+------+-----------+---------+------+
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 168x105 | 0001 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 168x105 | 0002 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 168x105 | 0003 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 168x105 | 0004 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 168x105 | 0005 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 416x260 | 0001 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 416x260 | 0002 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 416x260 | 0003 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 416x260 | 0004 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 416x260 | 0005 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 632x395 | 0001 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 632x395 | 0002 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 632x395 | 0003 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 632x395 | 0004 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 632x395 | 0005 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 768x432 | 0001 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 768x432 | 0002 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 768x432 | 0003 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 768x432 | 0004 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 768x432 | 0005 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | original | 0001 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | original | 0002 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | original | 0003 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | original | 0004 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | original | 0005 | jpg | 5.5.1 | auto |
+---------+--------------+----------------+-------------+----------+--------+-------------------------------+-----------------+-----------+----------------+---------------------------------------------------+-----------+----------+---------------------+---------+-----------------+--------+----------+-----------+----------------+----------------+----------------+----------------+----------+---------------+--------------+--------+-----------------+----------------+-------+---------------------+---------------------+---------+----------+------------+---------------+------------+-----------+-------------+-------------+------------------+---------------+----------------+----------------+-------------+------------+--------------------+-----------------+----------------+------------------+---------+------------+------+-----------+---------+------+
25 rows in set (0,001 sec)
Или даже так:
SELECT * FROM cb_video INNER JOIN cb_video_thumbs ON cb_video.videoid=cb_video_thumbs.videoid WHERE cb_video.videoid=6;
+---------+--------------+----------------+-------------+----------+--------+-------------------------------+-----------------+-----------+----------------+---------------------------------------------------+-----------+----------+---------------------+---------+-----------------+--------+----------+-----------+----------------+----------------+----------------+----------------+----------+---------------+--------------+--------+-----------------+----------------+-------+---------------------+---------------------+---------+----------+------------+---------------+------------+-----------+-------------+-------------+------------------+---------------+----------------+----------------+-------------+------------+--------------------+-----------------+----------------+------------------+---------+------------+------+-----------+---------+------+
| videoid | videokey | video_password | video_users | username | userid | title | file_name | file_type | file_directory | description | broadcast | location | datecreated | country | allow_embedding | rating | rated_by | voter_ids | allow_comments | comment_voting | comments_count | last_commented | featured | featured_date | allow_rating | active | favourite_count | playlist_count | views | last_viewed | date_added | flagged | duration | status | default_thumb | embed_code | downloads | uploader_ip | video_files | file_server_path | video_version | thumbs_version | re_conv_status | is_castable | bits_color | subscription_email | age_restriction | default_poster | default_backdrop | videoid | resolution | num | extension | version | type |
+---------+--------------+----------------+-------------+----------+--------+-------------------------------+-----------------+-----------+----------------+---------------------------------------------------+-----------+----------+---------------------+---------+-----------------+--------+----------+-----------+----------------+----------------+----------------+----------------+----------+---------------+--------------+--------+-----------------+----------------+-------+---------------------+---------------------+---------+----------+------------+---------------+------------+-----------+-------------+-------------+------------------+---------------+----------------+----------------+-------------+------------+--------------------+-----------------+----------------+------------------+---------+------------+------+-----------+---------+------+
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 168x105 | 0001 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 168x105 | 0002 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 168x105 | 0003 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 168x105 | 0004 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 168x105 | 0005 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 416x260 | 0001 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 416x260 | 0002 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 416x260 | 0003 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 416x260 | 0004 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 416x260 | 0005 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 632x395 | 0001 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 632x395 | 0002 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 632x395 | 0003 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 632x395 | 0004 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 632x395 | 0005 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 768x432 | 0001 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 768x432 | 0002 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 768x432 | 0003 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 768x432 | 0004 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | 768x432 | 0005 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | original | 0001 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | original | 0002 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | original | 0003 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | original | 0004 | jpg | 5.5.1 | auto |
| 6 | BRU5HUBY2AKO | | | NULL | 1 | Трудный маршрут | 170978047436cfa | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | public | | 2024-03-07 00:00:00 | PK | yes | 0 | 0 | | yes | yes | 0 | NULL | no | NULL | yes | yes | 0 | 0 | 7 | 2024-03-10 02:07:53 | 2024-03-07 03:01:14 | no | 64.92 | Successful | 1 | NULL | 0 | 192.168.7.1 | [240] | NULL | 5.5.1 | 5.5.1 | NULL | 1 | 8 | sent | NULL | NULL | NULL | 6 | original | 0005 | jpg | 5.5.1 | auto |
+---------+--------------+----------------+-------------+----------+--------+-------------------------------+-----------------+-----------+----------------+---------------------------------------------------+-----------+----------+---------------------+---------+-----------------+--------+----------+-----------+----------------+----------------+----------------+----------------+----------+---------------+--------------+--------+-----------------+----------------+-------+---------------------+---------------------+---------+----------+------------+---------------+------------+-----------+-------------+-------------+------------------+---------------+----------------+----------------+-------------+------------+--------------------+-----------------+----------------+------------------+---------+------------+------+-----------+---------+------+
Выборочные параметры. Тут к таблице cb_video относятся параметры кроме 3 последних (resolution,num,extension), относящихся к cb_video_thumbs. Но таблица указана с 1м параметром через точку, а остальные - без префикса через запятую.
SELECT cb_video.videoid,title,file_name,video_files,file_type,file_directory,description,resolution,num,extension FROM cb_video LEFT JOIN cb_video_thumbs ON cb_video.videoid=cb_video_thumbs.videoid WHERE cb_video.videoid=6;
+---------+-------------------------------+-----------------+-------------+-----------+----------------+---------------------------------------------------+------------+------+-----------+
| videoid | title | file_name | video_files | file_type | file_directory | description | resolution | num | extension |
+---------+-------------------------------+-----------------+-------------+-----------+----------------+---------------------------------------------------+------------+------+-----------+
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | 168x105 | 0001 | jpg |
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | 168x105 | 0002 | jpg |
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | 168x105 | 0003 | jpg |
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | 168x105 | 0004 | jpg |
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | 168x105 | 0005 | jpg |
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | 416x260 | 0001 | jpg |
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | 416x260 | 0002 | jpg |
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | 416x260 | 0003 | jpg |
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | 416x260 | 0004 | jpg |
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | 416x260 | 0005 | jpg |
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | 632x395 | 0001 | jpg |
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | 632x395 | 0002 | jpg |
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | 632x395 | 0003 | jpg |
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | 632x395 | 0004 | jpg |
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | 632x395 | 0005 | jpg |
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | 768x432 | 0001 | jpg |
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | 768x432 | 0002 | jpg |
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | 768x432 | 0003 | jpg |
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | 768x432 | 0004 | jpg |
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | 768x432 | 0005 | jpg |
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | original | 0001 | jpg |
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | original | 0002 | jpg |
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | original | 0003 | jpg |
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | original | 0004 | jpg |
| 6 | Трудный маршрут | 170978047436cfa | [240] | mp4 | 2024/03/07 | Monty Python (??) - Трудный маршрут | original | 0005 | jpg |
+---------+-------------------------------+-----------------+-------------+-----------+----------------+---------------------------------------------------+------------+------+-----------+
И теперь одну строку со всем что надо:
SELECT cb_video.videoid,title,file_name,video_files,file_type,file_directory,description,resolution,num,extension FROM cb_video LEFT JOIN cb_video_thumbs ON cb_video.videoid=cb_video_thumbs.videoid WHERE cb_video.videoid=6 AND resolution='original' AND num=0001;
Выводим строки с максимальным num, у которых resolution не original (а содержит разрешение), сортируем по разрешению и выбираем 1 строку:
SELECT cb_video.videoid,title,file_name,video_files,file_type,file_directory,description,resolution,num,extension FROM cb_video LEFT JOIN cb_video_thumbs ON cb_video.videoid=cb_video_thumbs.videoid WHERE cb_video.videoid=6 AND num=(SELECT MAX(num) FROM cb_video_thumbs) AND resolution !='original' ORDER BY resolution DESC LIMIT 1;
