CODEWITHSUNDEEP
phpmysql
Leiton
Leiton

Reputation: 35

PHP output summary based on 2 tables.

I have 2 tables in my database and I wanted a PHP output of the following: 

Dogs 5   
Cats 2  
Birds 4

how can I do this?

The above is listing of categories with a count of how many dogs, cats and birds are in each category.

I have 2 tables in MySQL laid out like this:

Pets (MySql Table name) 

AnimalNAME | AnimalCAT | AnimalDES  
   Bolt    |      1    | Smelly dog  
   Minx    |      2    | Snobby cat  
   Twit    |      3    | Cherpy bird  
   Rony    |      1    | Sneaky dog

Categories (MySql Table name) 

AnimalCAT | Name 
    1     | Dogs  
    2     | Cats  
    3     | Birds

Upvotes: 0

Views: 62

Answers (1)

Dan Grossman
Dan Grossman

Reputation: 52372

Here's the query:

SELECT
  Categories.Name,
  COUNT(Pets.AnimalCAT)
FROM
  Categories
LEFT OUTER JOIN
  Pets
ON
  Categories.AnimalCAT = Pets.AnimalCAT
GROUP BY
  Categories.AnimalCAT

PHP example:

mysql_connect('localhost', 'username', 'password');
mysql_select_db('dbname');

$sql = "
    SELECT
      Categories.Name AS `category`,
      COUNT(Pets.AnimalCAT) AS `count`
    FROM
      Categories
    LEFT OUTER JOIN
      Pets
    ON
      Categories.AnimalCAT = Pets.AnimalCAT
    GROUP BY
      Categories.AnimalCAT
";
$result = mysql_query($sql) or die("Error in SQL: " . mysql_error());

while ($row = mysql_fetch_array($result)) {
  echo $row['category'] . ' ' . $row['count'] . '<br />';
}

Upvotes: 1

Related Questions