Using UNION with PHP output
Posted: Mon Jun 22, 2009 7:25 pm
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:
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:
Is there a way how to group these together instead of separate?
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>
<?
}
?>