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.
downloads
--------------------
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?
thanx,
J
--
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]