[PHP] Quick query question
Alliteration at its finest... This is slightly off-topic, so please reply offlist. It's a MySQL question that I'm hoping someone can help me with. I figure since a large portion of PHP programmers also use MySQL, this is a valid question to at least -post- here. If I'm completely off about this, please let me know. I HAVE read the relevant portions of the MySQL manual, FYI. I discussed this a bit on the list a few weeks ago, it's about a project I'm working on to consolidate 75 playlists into a database sortable by song, album, and artist. I have 3 tables as was suggested, one for songs, one for albums, one for artists. Each has two columns, one being whatever information I'm entering, and the other a MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY column. I have 3 other tables containing two MEDIUMINT columns, these link each of the 3 others. I have: artists_tracks tracks_albums albums_artists Each is setup so that the records represent 'links' - for example, artists_tracks has artist_id and track_id columns. Each record is made up of an artist_id and a matching track_id. My problem is, when querying some records are lost. But only on a certain query. For instance: SELECT tracks.tracks, artists.artists FROM tracks, artists, artists_tracks WHERE tracks.id = artists_tracks.track_id AND artists.id = artist_tracks.tracks_id This works. It returns 614 records, each track mated with it's relative artist. This same query works using albums and the tracks_albums table, returning 614 records as well. Matching up albums to artists returns 413 records, which is the correct number. However, the following query returns 451 instead of the expected 614 and I can find no distinguishable pattern as to the records dropped: SELECT tracks.tracks, albums.albums, artists.artists FROM tracks, albums, artists, tracks_albums, albums_artists, artists_tracks WHERE tracks.id = tracks_albums.track_id AND albums.id = tracks_albums.album_id AND albums.id = albums_artists.album_id AND artists.id = albums_artists.artist_id AND artists.id = artists_tracks.artist_id AND tracks.id = artists_tracks.track_id I've tried LEFT JOIN as well, and have tried numerous other suggestions out of the manual, but everything turns up 451 records. I haven't been able to track down WHY records are missing - it seems almost random. If I sort by album, sometimes all songs from an album have been listed, sometimes only 3 of 5 or 7 of 10 are missing. Any help would be greatly appreciated and I apolgize again for posting this question here if it upsets anyone. I'm just really frustrated right now and not wanting to join yet another mailing list for one question. Please reply offlist. Thanks in advance. Jason Soza -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Quick query question
I think I'm a little more embarrassed than anything - I don't get upset! Thanks for letting me know, though. BTW, did you know that www.gremlins.com.hk doesn't turn up anything? I get a Cannot find server or DNS error -Original Message- From: Jason Wong [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 22, 2002 8:48 PM To: Jason Soza Subject: Re: [PHP] Quick query question On Thursday 23 May 2002 12:07, you wrote: I'm sorry. I'm used to other lists where occasional off-topic posts are tolerated a little more. Didn't know really how this list would react, but I thought I'd give it a shot. For me it probably depends on the circumstances. For instance if the list was low volume then I might be more inclined to tolerate (slightly) OT posts. But the php is such a high volume list and having to trawl through non-relevant posts can be daunting. I've subscribed to the MySQL list and have posted my message there as well. good :) In the future I'll not post any off-topic stuff. Thanks, Thanks for your understanding, some people get mighty upset when you ask them to stop posting OT stuff. cheers -- Jason Wong - Gremlins Associates - www.gremlins.com.hk Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* The truth of a proposition has nothing to do with its credibility. And vice versa. */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Quick query question
Well it just keeps getting better. I hit reply-to and the response went here. I apologize for this and my earlier off-topic post. I made a blatant mistake in judgement. Jason Soza -Original Message- From: Jason Soza [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 22, 2002 9:21 PM To: [EMAIL PROTECTED] Subject: RE: [PHP] Quick query question I think I'm a little more embarrassed than anything - I don't get upset! Thanks for letting me know, though. BTW, did you know that www.gremlins.com.hk doesn't turn up anything? I get a Cannot find server or DNS error -Original Message- From: Jason Wong [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 22, 2002 8:48 PM To: Jason Soza Subject: Re: [PHP] Quick query question On Thursday 23 May 2002 12:07, you wrote: I'm sorry. I'm used to other lists where occasional off-topic posts are tolerated a little more. Didn't know really how this list would react, but I thought I'd give it a shot. For me it probably depends on the circumstances. For instance if the list was low volume then I might be more inclined to tolerate (slightly) OT posts. But the php is such a high volume list and having to trawl through non-relevant posts can be daunting. I've subscribed to the MySQL list and have posted my message there as well. good :) In the future I'll not post any off-topic stuff. Thanks, Thanks for your understanding, some people get mighty upset when you ask them to stop posting OT stuff. cheers -- Jason Wong - Gremlins Associates - www.gremlins.com.hk Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* The truth of a proposition has nothing to do with its credibility. And vice versa. */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
