5

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

Example:

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

Songs Table:

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

Albums Table:

+--------+-----------+----------------+---------+---------------+-------------------+--------+-------+----------+
| 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 singles which means the result will be (ordered from latest to eldest):

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

Page 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):

Page 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:

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

And the five before:

Page 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):

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

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

So, my question is: how can I do what I did above in PHP and SQL (MySQL) ?

Sorry for my english and, thanks in advance

Improve this question
5
  • X=5, Y=10 right? Is that a typo? Commented Apr 5, 2011 at 20:00
  • 2
    I'm not sure you could have made this more confusing if you tried. show your SQL. Commented Apr 5, 2011 at 20:15
  • Please post the query you are currently using to pull albums and singles. Only then can we help you with your paging issue. Commented Apr 5, 2011 at 20:20
  • +1 for taking the time to plan/display this question... Commented Apr 5, 2011 at 20:29
  • Charliepiga & JNK & Kevin Peno: Yes, it's a typo, the code i wrote for this wasn't working as i want it to, so i deleted it... JNK: I will try to edit the description to make it less confusing... But if you look at the result example i gave (all of them) you will understand what's my issue... Lawrence Cherone: hhh :p Commented Apr 5, 2011 at 20:48

2 Answers 2

Reset to default
3

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...
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

It was a typing mistake, I meant: 0,5 or 5,5 or 10,5 ... But that don't work, because I want to get the result (from the songs table) to be: the distinct albums + the not distinct singles (since all singles have the same "album" which is [null])... Look at the result tables examples...
Given you've got two incompatible queries, you'd probably want to do two seperate queries, union them, then select from that result and do the ordering/limiting on that parent/outer query.
This confusion is a good reason to advocate LIMIT x OFFSET y instead of LIMIT y,x.
how can I do that using PHP and MySQL ?
0

Here's my quick stab at your question, but I'm making some huge assumptions.

I believe that you want all albums and singles listed in the order of release and you want to show 5 per "page". Assuming that, this query should work. Long term I would turn this into a view.

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

Then, starting with 0, increment X in the query above by 5 on each page.

Improve this answer

9 Comments

yes, you understood me right, but, that won't work if i want to a album or a single be shown in only one page,... in the example i gave above in "page" 1 there is two albums and three songs, and these are 6 rows in the songs table and in "page" 4 there is another two albums and another three songs, and these are 9 rows in the songs table, so i can't just increase X by 5 because each page gets rows more or less than 5 ...
You are overcomplicating my answer. The above query returns only null album songs and albums. It doesn't care if an album has 14 songs or 3. This query will return the albums and singles (songs without an album) in order of release limited and offset by your needs.
I get the error: "#1248 - Every derived table must have its own alias" ?
Sorry about that, I forgot to provide an alias. Maybe I should test before answering :P I've updated my answer.
Thanks, but there's one more problem, the tables don't have the same columns and have a different number of columns, and I need more columns from the songs table than from the albums table, Error: "#1222 - The used SELECT statements have a different number of columns"..., is there a solution for this or should I make the columns in the tables the same ?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.