Daniel, > I have a question about determining which case > occurred when there are no matches - using two tables. > > Scenario: > - two tables, one for photos, and one for collections > - zero or more photos can belong to a collection > - the database may not know the collection_id requested > (i.e. the user on the browser side did something to request > a non-existent collection) > > We can hit: > a) no collection > b) a collection that hasn't had any photos moved/uploaded to it yet, or.. > c) a collection with one or more photos (the "normal, everything's fine" case) > > Here's the php setting up the query... > > // shortened version for posting question... > // there are many more fields... > $sql_str =<<<EOD > select > photo.photo_id AS photo_id, > collection.collection_dir AS collection_dir > from photo, collection > where photo.collection_id = $collection > and collection.collection_id = $collection > EOD; > $sql_result = $db->query($sql_str); > > When I check the number of rows returned, and get > 0, I have no way (yet) of knowing why (no collection, > or just no photos in a collection). > > Should I be using COUNT/GROUP BY?
The first observation is the way you have worded the join - I'm sure it will work when there is both collection and photo data, but to join the photo and collection tables it would be 'normal' to establish the relationship between their keys, eg: WHERE photo.collection_id = collection.collection_id AND ... To use COUNT would presumably necessitate querying the db more than once. How are you going to GROUP the resultset - I assume from the PHP code that you are only asking for details from one collection at a time? An inner join (as per PHP code above) will only return data when there are related rows in both tables [hit (c) above]. An outer join will enable you to find collections without photos [hit (b) above] (ie collection.collection_id = $collection, but with NULL(s) returned from the photo table) - and (theoretically) photos without collection identification [not mentioned above]. Obviously hit (a) is the empty resultset/'non-hit'/zero hit situation. Changing to an outer join will allow you to cover the three (indeed add a fourth) hit-cases, and by analysing the resultset you will be able to distinguish between them. Information about the different types of join is covered in the manual. Beware the left/right wording of an outer join, and for efficiency, word things so that MySQL will examine the table with the fewest likely hits first. Let us know how you get on! =dn --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
