Using UNION with PHP output

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
atl
Forum Newbie
Posts: 1
Joined: Mon Jun 22, 2009 7:13 pm

Using UNION with PHP output

Post 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?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Using UNION with PHP output

Post by VladSun »

No, you don't need UNION. You need INNER JOIN.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply