OrangeBoy Posted May 26, 2012 Share Posted May 26, 2012 Hello, I have 3 MySQL tables: The first table animal_type has 2 columns, the type of animal and a unique ID number. For example Dogs 1 Cats 2 Birds 3 The second table animal_breed has 2 columns, the breeds of the animal people own (not unique) and an ID for that record. So for example: Siamese 1 Poodle 2 Parrot 3 Parrot 4 Siamese 5 Persian 6 Terrier 7 The third table animal_map is a connecting table with 2 columns - the breed and the ID which is the right ID from the animal_type table. Siamese 2 Poodle 1 Parrot 3 Persian 2 Terrier 1 So there will be lots of records in the 2nd table - the animal_breed table. What I would like is a way to count all the records in the animal_breed table and display on the PHP page how many animal types there are. So, using the above examples, Siamese 1 Poodle 2 Parrot 3 Parrot 4 Siamese 5 Persian 6 Terrier 7 It would count them up and display: 3 cats 2 dogs 2 birds I have tried this but I can't get it to work. So far I have: At the top of the page I have this line for my database details <?php include("dbdetails.php"); ?> Then I have some HTML Then I have my select statement: $query = "SELECT ..."; I have tried many types of SELECT but I can't get any to work. Do I need to do something like SELECT COUNT(table column)? Then I have: $result = mysql_query($query) or die(mysql_error()); (I think the above line is correct) Then I have a WHILE loop such as: while($row = mysql_fetch_array($result)){ stuff like echo $row['animals']; etc } (I think the while loop is right? Or maybe there is another way to do it). I would appreciate any help. Thanks Link to comment https://forums.phpfreaks.com/topic/263178-multiple-table-select-issue/ Share on other sites More sharing options...
Psycho Posted May 26, 2012 Share Posted May 26, 2012 I think you should make a change in our structure. The animal_breed table should be a list of possible breeds along with unique ID. Then you would have a table (e.g. animals) that contains one record for each animal. That table would include a foreign key to the animal_breed table. But, using your current structure, this should work SELECT animal_type.name AS name, COUNT(animal_type.type_id) AS count FROM animal_type JOIN animal_map ON animal_map.type_id = animal_type.type_id JOIN animal_breed ON animal_breed.breeds = animal_map.breeds GROUP BY animal_type.type_id Link to comment https://forums.phpfreaks.com/topic/263178-multiple-table-select-issue/#findComment-1348809 Share on other sites More sharing options...
OrangeBoy Posted May 26, 2012 Author Share Posted May 26, 2012 Thanks for the help. Link to comment https://forums.phpfreaks.com/topic/263178-multiple-table-select-issue/#findComment-1348816 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.