Page 1 of 1

Using UNION with PHP output

Posted: Mon Jun 22, 2009 7:25 pm
by atl
Hello community members! I am in need of some help here, I am new to the php/sql and can't get certain things to happen.
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;
 
I am trying to out put as follows
"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>
<?
}
?>
 
Is there a way how to group these together instead of separate?

Re: Using UNION with PHP output

Posted: Tue Jun 23, 2009 2:40 am
by VladSun
No, you don't need UNION. You need INNER JOIN.