Hello Justin,

Thursday, September 13, 2001, 11:07:26 AM, you wrote:

JB> On Thu, 13 Sep 2001, Jacob Singh wrote:

>> Hello,
>>   main
>>   ----------
>>   ID autonumber
>>   content_ID enum('artist','song','album',user function','genre', etc.)
>>   name char
>>   img char
>>   des char
>>   etc. etc.
>>   songs
>>   -----------
>>   main_ID ( link to main table) int
>>   artist_ID (link to parent) int
>>   length (length of song) char
>>   size (size of file) char
>>   file (file location) char
>>   etc.
>>   etc.
>>   Now my goal is to take a given number of songs say 10 songs of
>>   various artists and get a list like:
>>   artist 1
>>          song1  - length - size
>>          song2  - length - size
>>          song3  - length - size
>>   artist 2
>>          song1  - length - size
>>          song2  - length - size
>>          song3  - length - size

JB> I -think- I'm seeing your database design... but I'm not sure if your
JB> parent id (artist_id) links back to the songs table or the main table.. at
JB> any rate, a simple GROUP BY on your query would let you query out songs by
JB> criteria and then sort them by artist.

JB> BTW, you may wish to take a look at any similar projects at
JB> www.freshmeat.net for examples, unless you've already done that and found
JB> that no other projects come close to your needs.

JB> Justin Buist

The parent id links back to the main table.  I'm sorry, I didn't
eleborate enough.  There is one more table to note, the downloads
table.  This table just matches member id's to song id's to see whos
bought what.
member_ID (from user table, unimportant)
song_ID (links to both the main table and songs table)

so if my data looked like:
member_ID  | song_ID
    1      |    1
    1      |    2
    1      |    5
    2      |    1
    3      |    5
    3      |    4
    3      |    1

I'd want to query something like:
SELECT song_ID,songs.*,main.* from downloads,songs,main WHERE
downloads.member_ID = X <X being an int> AND songs.main_ID = main.ID
and songs.artist_ID = main.ID (obvious problem here)
The issue is that I need to get the name field and others from the
main table for each song and simultaneously get those same fields from
that same table from the artist_ID field in the same recordset, get
it?  I'm thinking that there is no way to do this with sub-selects, is
it possible to do with PHP someway?

Best regards,
 Jacob                            mailto:[EMAIL PROTECTED]

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Reply via email to