php/mysql help

I'm having trouble working out a mysql query.  I have two tables called "content" and "providers". 

 

Content Table

content_id

content_provider

1

Provider 1

2

Provider 2

3

Provider 1

 

Providers Table

provider_name

provider_url

Provider 1

www.provider1.com

Provider 2

www.provider2.com

 

What I want to do is to select all of the information in the "providers" table but also to perform a count on the "content" table to get the number of content for each provider.  So in this case it would show all of the information for provider 1 and also show that provider 1 has 2 pieces of content because they have 2 entries in the "content" table. 

 

I am able to get these results as two separate queries but I was wondering if it was possible to do it in one query. 

 

Thanks for any help. 

 

In MySQL (any RDBMS actually) you can use JOINs to intersect tables and keep everything tidy with one query. While there is a little learning curve to JOINs, once you start using them, you'll find out it is a better and faster way (coding-wise and performance-wise) of querying multiple tables.

 

Using the INNER JOIN keyword

<?php
$results = mysql_query("SELECT COUNT(*) AS contentCount, p.provider_name FROM providers p
		INNER JOIN content c ON c.content_provider=p.provider_name
		GROUP BY p.provider_name");

while ($values = mysql_fetch_array($results)) {
echo "{$values['provider_name']} -> {$values['contentCount']}<br />";
}
?>

 

Intersecting with WHERE

<?php
$results = mysql_query("SELECT COUNT(*) AS contentCount, p.provider_name
		FROM providers p, content c
		WHERE c.content_provider=p.provider_name
		GROUP BY p.provider_name") or die();

while ($values = mysql_fetch_array($results)) {
echo "{$values['provider_name']} -> {$values['contentCount']}<br />";
}
?>

 

They produce the same exact results and which one you choose is a matter of preference. Personally I like using the INNER JOIN keyword as it makes the query more readable and also I can change in a snap if I decide to use a LEFT or RIGHT JOIN.

 

NOTE: I used table aliases to quickly reference a table. I.e: in "FROM providers p", the "p" is the table alias. For better readability you can use the "AS" keyword to declare aliases: "FROM providers AS p". The alias can be any-letter-word you like.

Thanks.  I get it now.  Also thanks for the tip on table alias's, that'll come in handy.