У меня есть таблица песен, некоторые песни – песня альбома, а некоторые – синглы … И у меня есть таблица альбомов …
Пример:
если идентификатор альбома [null], это означает, что песня является одиночной
Таблица композиций:
+--------+---------+-----------+----------------+---------+---------------+-------+--------+--------+------+-------+-------+----------+ | Row ID | Song ID | Album ID | Song Name | Band | Date Released | Genre | Lyrics | Lenght | Size | Plays | Likes | Dislikes | +--------+---------+-----------+----------------+---------+---------------+-------+--------+--------+------+-------+-------+----------+ | 1 | Song-01 | Album-1 | Song-001 | Band-1 | 2010-12-15 | Rock | ... | 4:30 | 4 MB | 101 | 11 | 1 | | 2 | Song-02 | Album-1 | Song-002 | Band-1 | 2010-12-15 | Rock | ... | 5:30 | 5 MB | 102 | 12 | 2 | | 3 | Song-03 | Album-1 | Song-003 | Band-1 | 2010-12-15 | Rock | ... | 3:30 | 3 MB | 103 | 13 | 3 | | 4 | Song-04 | [null] | Song-004 | Band-1 | 2010-12-16 | Rock | ... | 4:30 | 4 MB | 104 | 14 | 4 | | 5 | Song-05 | Album-2 | Song-005 | Band-1 | 2010-12-17 | POP | ... | 3:30 | 3 MB | 105 | 15 | 5 | | 6 | Song-06 | Album-2 | Song-006 | Band-1 | 2010-12-17 | RAP | ... | 5:30 | 5 MB | 106 | 16 | 6 | | 7 | Song-07 | [null] | Song-007 | Band-2 | 2010-12-17 | Rock | ... | 3:30 | 3 MB | 107 | 17 | 7 | | 8 | Song-08 | Album-3 | Song-008 | Band-2 | 2010-12-17 | Rock | ... | 4:30 | 4 MB | 108 | 18 | 8 | | 9 | Song-09 | Album-3 | Song-009 | Band-2 | 2010-12-17 | POP | ... | 5:30 | 5 MB | 109 | 19 | 9 | | 10 | Song-10 | Album-3 | Song-010 | Band-2 | 2010-12-17 | Punk | ... | 6:30 | 6 MB | 110 | 20 | 0 | | 11 | Song-11 | Album-3 | Song-011 | Band-2 | 2010-12-17 | RAP | ... | 7:30 | 7 MB | 111 | 21 | 1 | | 12 | Song-12 | [null] | Song-012 | Band-2 | 2010-12-18 | Rock | ... | 3:30 | 3 MB | 112 | 22 | 2 | | 13 | Song-13 | [null] | Song-013 | Band-2 | 2010-12-18 | Rock | ... | 2:30 | 2 MB | 113 | 23 | 3 | | 14 | Song-14 | [null] | Song-014 | Band-3 | 2010-12-18 | Rock | ... | 6:30 | 6 MB | 114 | 24 | 4 | | 15 | Song-15 | [null] | Song-015 | Band-3 | 2010-12-19 | Rock | ... | 7:30 | 7 MB | 115 | 25 | 5 | | 16 | Song-16 | [null] | Song-016 | Band-3 | 2010-12-19 | Rock | ... | 4:30 | 4 MB | 116 | 26 | 6 | | 17 | Song-17 | [null] | Song-017 | Band-4 | 2010-12-19 | POP | ... | 3:30 | 3 MB | 117 | 27 | 7 | | 18 | Song-18 | [null] | Song-018 | Band-4 | 2010-12-19 | POP | ... | 2:30 | 2 MB | 118 | 28 | 8 | | 19 | Song-19 | [null] | Song-019 | Band-5 | 2010-12-20 | Rock | ... | 4:30 | 4 MB | 119 | 29 | 9 | | 20 | Song-20 | [null] | Song-020 | Band-5 | 2010-12-20 | Rock | ... | 5:30 | 5 MB | 120 | 30 | 0 | | 21 | Song-21 | [null] | Song-021 | Band-5 | 2010-12-20 | Rock | ... | 6:30 | 6 MB | 121 | 31 | 1 | | 22 | Song-22 | Album-4 | Song-022 | Band-5 | 2010-12-21 | Rock | ... | 3:30 | 3 MB | 122 | 32 | 2 | | 23 | Song-23 | Album-4 | Song-023 | Band-5 | 2010-12-21 | Rock | ... | 2:30 | 2 MB | 123 | 33 | 3 | | 24 | Song-24 | Album-4 | Song-024 | Band-5 | 2010-12-21 | Rock | ... | 4:30 | 4 MB | 124 | 34 | 4 | | 25 | Song-25 | [null] | Song-025 | Band-6 | 2010-12-22 | Rock | ... | 5:30 | 5 MB | 125 | 35 | 5 | | 26 | Song-26 | [null] | Song-026 | Band-6 | 2010-12-22 | Rock | ... | 6:30 | 6 MB | 126 | 36 | 6 | | 27 | Song-27 | Album-5 | Song-027 | Band-7 | 2010-12-22 | POP | ... | 4:30 | 4 MB | 127 | 37 | 7 | | 28 | Song-28 | Album-5 | Song-028 | Band-7 | 2010-12-22 | PUNK | ... | 3:30 | 3 MB | 128 | 38 | 8 | | 29 | Song-29 | [null] | Song-029 | Band-7 | 2010-12-23 | Rock | ... | 2:30 | 2 MB | 129 | 39 | 9 | | 30 | Song-30 | Album-6 | Song-030 | Band-8 | 2010-12-25 | Rock | ... | 5:30 | 5 MB | 130 | 40 | 0 | +--------+---------+-----------+----------------+---------+---------------+-------+--------+--------+------+-------+-------+----------+ Таблица альбомов:
+--------+-----------+----------------+---------+---------------+-------------------+--------+-------+----------+ | Row ID | Album ID | Album Name | Band | Date Released | Genre | Lenght | Likes | Dislikes | +--------+-----------+----------------+---------+---------------+-------------------+--------+-------+----------+ | 1 | Album-1 | Album One | Band-1 | 2010-12-15 | Rock | 13:30 | 101 | 31 | | 2 | Album-2 | Album Two | Band-1 | 2010-12-17 | POP/RAP | 9:00 | 102 | 32 | | 3 | Album-3 | Album Three | Band-2 | 2010-12-17 | Rock/Punk/POP/RAP | 24:00 | 103 | 33 | | 4 | Album-4 | Album Four | Band-5 | 2010-12-21 | Rock | 10:30 | 104 | 34 | | 5 | Album-5 | Album Five | Band-7 | 2010-12-22 | Punk/POP | 8:00 | 105 | 35 | | 6 | Album-6 | Album Six | Band-8 | 2010-12-25 | Rock | 5:30 | 106 | 36 | +--------+-----------+----------------+---------+---------------+-------------------+--------+-------+----------+
И если я хочу взять только последние пять альбомов и / или синглов, что означает, что результат будет (от последнего к старшему):
по имени столбца «Я имею в виду« Единое имя или название альбома »,
Страница 1:
+-----------------+---------+-----------------+-------------------+----------+-----------+--------+--------+------+-------+----------+-------+ | Name | Band | Date Released | Genre | IsAlbum? | IsSingle? | Lyrics | Lenght | Size | Likes | Dislikes | Plays | +-----------------+---------+-----------------+-------------------+----------+-----------+--------+--------+------+-------+----------+-------+ | Album Six | Band-8 | 2010-12-25 | Rock | YES | NO | - | 5:30 | -- | 106 | 36 | - | | Song 29 | Band-7 | 2010-12-23 | Rock | NO | YES | ... | 2:30 | 2 MB | 39 | 9 | 129 | | Album Five | Band-7 | 2010-12-22 | Punk/POP | YES | NO | - | 8:00 | -- | 105 | 35 | - | | Song 26 | Band-6 | 2010-12-22 | Rock | NO | YES | ... | 6:30 | 6 MB | 36 | 6 | 126 | | song 25 | Band-6 | 2010-12-22 | Rock | NO | YES | ... | 5:30 | 5 MB | 35 | 5 | 125 | +-----------------+---------+-----------------+-------------------+----------+-----------+--------+--------+------+-------+----------+-------+
И если я хочу взять только последние пять после последних пяти (те, что указаны в таблице выше) альбомы и / или синглы, результат будет отправлен от последнего до старшего):
Страница 2:
+-----------------+---------+-----------------+-------------------+----------+-----------+--------+--------+------+-------+----------+-------+ | Name | Band | Date Released | Genre | IsAlbum? | IsSingle? | Lyrics | Lenght | Size | Likes | Dislikes | Plays | +-----------------+---------+-----------------+-------------------+----------+-----------+--------+--------+------+-------+----------+-------+ | Album Four | Band-5 | 2010-12-21 | Rock | YES | NO | - | 10:30 | -- | 104 | 34 | - | | Song 21 | Band-5 | 2010-12-20 | Rock | NO | YES | ... | 6:30 | 6 MB | 31 | 1 | 121 | | Song 20 | Band-5 | 2010-12-20 | Rock | NO | YES | ... | 5:30 | 5 MB | 30 | 0 | 120 | | Song 19 | Band-5 | 2010-12-20 | Rock | NO | YES | ... | 4:30 | 4 MB | 29 | 9 | 119 | | song 18 | Band-4 | 2010-12-19 | POP | NO | YES | ... | 2:30 | 2 MB | 28 | 8 | 118 | +-----------------+---------+-----------------+-------------------+----------+-----------+--------+--------+------+-------+----------+-------+
И пять перед ними будут:
Страница 3:
+-----------------+---------+-----------------+-------------------+----------+-----------+--------+--------+------+-------+----------+-------+ | Name | Band | Date Released | Genre | IsAlbum? | IsSingle? | Lyrics | Lenght | Size | Likes | Dislikes | Plays | +-----------------+---------+-----------------+-------------------+----------+-----------+--------+--------+------+-------+----------+-------+ | Song 17 | Band-4 | 2010-12-19 | POP | NO | YES | ... | 3:30 | 3 MB | 27 | 7 | 117 | | Song 16 | Band-3 | 2010-12-19 | Rock | NO | YES | ... | 4:30 | 4 MB | 26 | 6 | 116 | | Song 15 | Band-3 | 2010-12-19 | Rock | NO | YES | ... | 5:30 | 5 MB | 25 | 5 | 115 | | Song 14 | Band-3 | 2010-12-18 | Rock | NO | YES | ... | 6:30 | 6 MB | 24 | 4 | 114 | | song 13 | Band-2 | 2010-12-18 | Rock | NO | YES | ... | 2:30 | 2 MB | 23 | 3 | 113 | +-----------------+---------+-----------------+-------------------+----------+-----------+--------+--------+------+-------+----------+-------+
И пять до:
Страница 4:
+-----------------+---------+-----------------+-------------------+----------+-----------+--------+--------+------+-------+----------+-------+ | Name | Band | Date Released | Genre | IsAlbum? | IsSingle? | Lyrics | Lenght | Size | Likes | Dislikes | Plays | +-----------------+---------+-----------------+-------------------+----------+-----------+--------+--------+------+-------+----------+-------+ | Song 12 | Band-2 | 2010-12-18 | Rock | NO | YES | ... | 3:30 | 3 MB | 22 | 2 | 112 | | Album Three | Band-2 | 2010-12-17 | Rock/Punk/POP/RAP | YES | NO | - | 24:00 | -- | 103 | 33 | - | | Song 7 | Band-2 | 2010-12-17 | Rock | NO | YES | ... | 3:30 | 3 MB | 17 | 7 | 107 | | Album Two | Band-1 | 2010-12-17 | POP/RAP | YES | NO | - | 9:00 | -- | 102 | 32 | - | | song 4 | Band-1 | 2010-12-16 | Rock | NO | YES | ... | 4:30 | 4 MB | 14 | 4 | 104 | +-----------------+---------+-----------------+-------------------+----------+-----------+--------+--------+------+-------+----------+-------+
И в последней таблице (или странице):
Страница 5:
+-----------------+---------+-----------------+-------------------+----------+-----------+--------+--------+------+-------+----------+-------+ | Name | Band | Date Released | Genre | IsAlbum? | IsSingle? | Lyrics | Lenght | Size | Likes | Dislikes | Plays | +-----------------+---------+-----------------+-------------------+----------+-----------+--------+--------+------+-------+----------+-------+ | Album One | Band-1 | 2010-12-15 | Rock | YES | NO | - | 13:00 | -- | 101 | 31 | - | +-----------------+---------+-----------------+-------------------+----------+-----------+--------+--------+------+-------+----------+-------+
Но проблема в том, что когда я делаю это, X и Y в «LIMIT X, Y» не будут X = 0, Y = 5 или X = 5, Y = 5 или X = 10, Y = 5 и скоро…
Итак, мой вопрос: как я могу сделать то, что я сделал выше в PHP и SQL (MySQL)?
Извините за мой английский и, спасибо заранее
Вы неправильно делаете предложения LIMIT. это OFFSET,QUANTITY . Ваши примерные запросы собирают по 5 записей за раз, поэтому QUANTITY будет 5 в любое время, и вы увеличиваете смещение на 5 для каждой «страницы» результатов.
Top 5 songs/albums: LIMIT 0,5 Top 6-10 songs/album: LIMIT 5,5 Top 11-15 songs/albums: LIMIT 10,5 etc...
Вот мой быстрый удар по вашему вопросу, но я делаю некоторые огромные предположения.
Я считаю, что вы хотите, чтобы все альбомы и синглы были перечислены в порядке выпуска, и вы хотите показать 5 за «страницу» . Предполагая, что этот запрос должен работать. В долгосрочной перспективе я бы превратил это в точку зрения.
SELECT "Name", "Date Released" FROM ( SELECT "Album ID" as "Name", "Date Released" FROM Albums UNION ALL SELECT "Song Name" as "Name", "Date Released" FROM Songs WHERE "Album ID" IS NULL ) as AlbumsAndSingles ORDER BY "Date Released" ASC LIMIT X,5
Затем, начиная с 0, приращение X в запросе выше на 5 на каждой странице.