I have 2 tables
TABLE videos has id, title, category
TABLE categories has id, name, title
I am trying to figure out how many videos there are in each category. Here is what I have that works:
Code: Select all
SELECT COUNT(*) cnt FROM videos GROUP by category UNION SELECT name FROM categories;
"There are 6 Videos in Category Funny"
"There are 0 Videos in Category Cartoon"
"There are 9 Videos in Category News"
But rather its output looks like this:
"There are 6 Videos in Category"
"There are 0 Videos in Category"
"There are 9 Videos in Category"
"There are Funny Videos in Category"
"There are Cartoon Videos in Category"
"There are News Videos in Category"
Here is my PHP code:
Code: Select all
<?php
include("includes/config.php");
$result=mysql_query("SELECT COUNT(*) cnt FROM videos GROUP by category UNION SELECT name FROM categories;");
while($row=mysql_fetch_assoc($result)){
?>
There are <?=$row['cnt']; ?> Videos in Category <?=$name ?><br>
<?
}
?>