Counting occurences in second table compared to first

I have two tables, one holds the information of contributors to my site and one holds information on photographs contributed.

For the admin side of the site, I want to create a table using php and mysql that开发者_Python百科 displays all contributors but also counts the number of photographs each contributor has available for the site.

I get the list of names using this code

  SELECT *  
    FROM site_con 
ORDER BY surn ASC

I have then set up a loop to list all the names but have added a query within that loop to count the number of photographs using this code

$contributor = $row_rsContrib['con_Code'];

mysql_select_db($database_connGrowl, $connGrowl);
$query_rsCounter = "SELECT COUNT(*) AS Count 
                      FROM site_phts 
                     WHERE photter = $contributor";

$rsCounter = mysql_query($query_rsCounter, $connGrowl) or die(mysql_error());
$row_rsCounter = mysql_fetch_assoc($rsCounter);
$totalRows_rsCounter = mysql_num_rows($rsCounter);

The only problem is when '$contributor' is not in the photographs table, it returns an error.

Any ideas?

You can get the list of contributors & the number of photos in a single query:

   SELECT sc.*,
          COALESCE(x.numPhotos, 0) AS numPht
     FROM SITE_CON sc
LEFT JOIN (SELECT sp.photter,
                  COUNT(*) AS numPhotos
             FROM SITE_PHTS sp
         GROUP BY sp.photter) x ON x.photter = sc.con_code
 ORDER BY ssc.surn

Your query fails because a photographer doesn't necessarily have contributions -- the query above returns the list of photographers, and those without photos associated will have a numPht value of zero. Here's a primer on JOINs, to help explain the OUTER JOIN that's being used.

Actually the best way to do this is by using MSQL to count rather than PHP:

SELECT site_con.*, COUNT( photo_id )
FROM site_con
LEFT JOIN site_phts ON site_con.con_Code = site_phts.photter
GROUP BY site_con.con_Code
ORDER BY site_con.surn

The LEFT JOIN has the special property of creating NULL entries when there is no row in the right table (photos) that matches a contributor row. COUNT will not count these NULL entries. (You need some unique column in the photos table, I used photo_id for that.)

this is the relation between Contributors and photographs:

• 1 photograph can have a most 1 Contributor
• 1 Contributor can have a most infinit photograph

Contributor <-(0,n)------(0,1)-> Photograph

so you might wanna add a connexion betweet those two tables, I mean you add the con_id to the photographs table (as a column).

this way you'll be able to retrieve all the informations in one SQL query.

(like OMG Ponies just said)

Do something like this, I believe this should work :

$result = mysql_query("SELECT COUNT(*) AS Count FROM site_phts WHERE photter = '$contributor'"); // put the single quote if $contributor is a string value

//use mysql_fetch_array

if ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    printf("ID: %d", $row[0]);  
}

Hopefully this works, Good luck mate !