Need help with PHP and MySQL

Question

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

I'm not sure you could have made this more confusing if you tried. show your SQL. — JNK, Apr 05 '11 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. — Kevin Peno, Apr 05 '11 at 20:20
**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 — Andy, Apr 05 '11 at 20:48

score 3 · Answer 1 · answered Apr 05 '11 at 20:20

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

answered Apr 05 '11 at 20:20

Marc B

356,200
43
426
500

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... – Andy Apr 05 '11 at 20:27
1

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. – Marc B Apr 05 '11 at 20:38
This confusion is a good reason to advocate `LIMIT x OFFSET y` instead of `LIMIT y,x`. – staticsan Apr 05 '11 at 23:14
how can I do that using PHP and MySQL ? – Andy Apr 06 '11 at 13:02

Kevin Peno · Answer 2 · 2011-04-05T21:04:41.817

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.

edited Apr 05 '11 at 21:04

answered Apr 05 '11 at 20:27

Kevin Peno

9,107
1
33
56

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 ... – Andy Apr 05 '11 at 20:39
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. – Kevin Peno Apr 05 '11 at 20:44
I get the error: "#1248 - Every derived table must have its own alias" ? – Andy Apr 05 '11 at 21:02
Sorry about that, I forgot to provide an alias. Maybe I should test before answering :P I've updated my answer. – Kevin Peno Apr 05 '11 at 21:05
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 ? – Andy Apr 05 '11 at 21:18
You need to update your question to include all of the columns you need. However, I must say that you should probably rethink your selection if you believe that you need MORE information from a song than an album. In the case of this current query I see that you are simply using the fact that a song is a single to create an album form it on the fly. Thus, ALL results are albums, none of them are songs. – Kevin Peno Apr 05 '11 at 21:20
No, i don't want to create an album form a song on the fly... i need for example the length, time, size... of the song, but I don't need that from the album... what do you think is the best solution for this problem? – Andy Apr 05 '11 at 21:29
From the songs table I need: song_id1, song_id2, song_name, band, album_id2, time_released, genre, lyrics, lenght, size, plays, likes, dislikes, rating. From the albums table I need: album_id1, album_id2, album_name, band, time_released, lenght, likes, dislikes, rating. Note if the song was in an album the album info will be shown in the result and the info need from the songs only won't be needed, otherwise, the song's info with it's additional info will be shown... – Andy Apr 05 '11 at 21:43
Update your answer, not reply here. Include the schema and columns you need as well as the expected results. – Kevin Peno Apr 05 '11 at 21:56

Need help with PHP and MySQL

2 Answers2

Related