Multiple Table Select issue

[OrangeBoy]

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

 

[Psycho]

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

[OrangeBoy]

Thanks for the help.