本文介绍了需要有关PHP和MySQL的帮助的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一张歌曲表,有些歌曲是专辑歌曲,有些是单曲...我有一张专辑表...

I have a table of songs, some songs are album song, and some are singles...And I have a table of albums...

示例:

如果专辑ID为[null],则表示歌曲为单曲

if the album ID is [null], it means the song is a single

歌曲表:

+--------+---------+-----------+----------------+---------+---------------+-------+--------+--------+------+-------+-------+----------+
| 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    |
+--------+-----------+----------------+---------+---------------+-------------------+--------+-------+----------+

如果我只想拍最新的五张专辑和/或单曲这意味着结果将是(按从最新到最老的顺序排列):

And If I want to take only the latest five albums and/or singleswhich means the result will be (ordered from latest to eldest):

名称"列是指单个名称或相册名称"

by the column 'name' I mean 'Single name or Album name'

第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  |
+-----------------+---------+-----------------+-------------------+----------+-----------+--------+--------+------+-------+----------+-------+

如果我只想接听最近五张专辑(和上表中的专辑)和/或单曲之后的最新五张专辑,则结果将从最新到最年长排序:

And If I want to take only the latest five after the latest five (the ones in the table above) albums and/or singles, the result will be ordered from latest to eldest):

第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  |
+-----------------+---------+-----------------+-------------------+----------+-----------+--------+--------+------+-------+----------+-------+

在他们之前的五个将是:

And the five before them will be:

第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  |
+-----------------+---------+-----------------+-------------------+----------+-----------+--------+--------+------+-------+----------+-------+

在最后一个表(或页面)中:

And in the last table (or page):

第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    |   -   |
+-----------------+---------+-----------------+-------------------+----------+-----------+--------+--------+------+-------+----------+-------+

但是,问题是当我这样做时,"LIMIT X,Y"中的X和Y不会是X = 0,Y = 5或X = 5,Y = 5或X = 10,Y = 5等等...

But, the problem is that when I do that the X and the Y in "LIMIT X , Y" won't be X=0,Y=5 or X=5,Y=5 or X=10,Y=5 and so on...

所以,我的问题是:如何做以上在PHP和SQL(MySQL)中所做的事情?

抱歉,我的英语水平,在此先感谢

推荐答案

您弄错了LIMIT子句. OFFSET,QUANTITY.您的示例查询一次获取5条记录,因此QUANTITY始终为5,并且您将为结果的每个页面"将偏移量增加5.

You're getting the LIMIT clauses wrong. it's OFFSET,QUANTITY. Your sample queries are fetching 5 records at a time, so the QUANTITY would be 5 at all times, and you'd increment the offset by 5 for each "page" of results.

Top 5 songs/albums: LIMIT 0,5
Top 6-10 songs/album: LIMIT 5,5
Top 11-15 songs/albums: LIMIT 10,5
etc...

这篇关于需要有关PHP和MySQL的帮助的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-14 20:10