php/mysql help

[c_pattle]

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. 

 

[Fadion]

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.

[c_pattle]

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