Special count question

[Wal-Mart Security]

Koodi [Valitse] Laajenna

<?php
    mysql_connect($sitesettings['dbserver'], $sitesettings['dbuser'], $sitesettings['dbpw']) or die(mysql_error());
    mysql_select_db($sitesettings['dbname']) or die(mysql_error());
    $result = mysql_query("SELECT * FROM flash_cat, flash_movies") or die(mysql_error());
    mysql_close;
    
    echo '    <table border="0" cellpadding="2" cellspacing="2" width="100%">
      <tbody><tr>
        <td bgcolor="#6c000e" width="37%"><b><i><font color="#ffffff" face="Arial, Helvetica, sans-serif" size="-1">Title:</font></i></b></td>
        <td bgcolor="#6c000e" width="27%"><b><i><font color="#ffffff" face="Arial, Helvetica, sans-serif" size="-1">Created 
          By</font></i></b></td>
        <td bgcolor="#6c000e" width="21%"> 
          <div align="right"> 
            <p align="left"><b><i><font color="#ffffff" face="Arial, Helvetica, sans-serif" size="-1">Number 
              of Movies</font></i> 
              </b></p></div>        </td>
        <td bgcolor="#6c000e" width="15%"> <b><i><font color="#ffffff" face="Arial, Helvetica, sans-serif" size="-1">Select 
          Series</font></i> </b></td>
      </tr>';
    // keeps getting the next row until there are no more to get
    while($row = mysql_fetch_array( $result )) {
        // Print out the contents of each row into a table
      echo '
      <tr> 
        <td bgcolor="' , $row['is_odd'] != '0' ? '#334a66' : '#002233' , '" valign="middle" width="37%"><b>', $row['catName'], '</b></td>
        <td bgcolor="' , $row['is_odd'] != '0' ? '#334a66' : '#002233' , '" valign="middle" width="27%"><b><font color="#ffffff" size="3"><a href="" target="_blank"><font color="white">', $row['catAuthor'], '</font></a></font></b></td>
        <td align="left" bgcolor="' , $row['is_odd'] != '0' ? '#334a66' : '#002233' , '" width="21%"> 
          <div align="right"> 
            <p align="left"><b><font color="#ffffff" size="3">NUMBER OF MOVIES IN THIS CATEGORY</font> 
              </b></p></div>        </td>
        <td align="right" bgcolor="' , $row['is_odd'] != '0' ? '#334a66' : '#002233' , '" width="15%"> <b><a href="', $sitesettings['url'], 'index.php?action=flash;cat=', $row['ID_CAT'], '">
          <img src="', $sitesettings['url'], 'Videos/thumbnails/', $row['catName'], '.jpg" width="115" height="70" border="0"></a></b></td>
      </tr>';
    }
?>

Basically what I'm wanting to do is get php to count the amount of movies in a category.  I'm not quite sure how to explain it but I just need it to display the total amount of movies in a particular category of movies.  flash_cat has ID_CAT and flash_movies has a column called cat.

Meh....I'm even confusing myself right now lol.  But at least I got the SQL part to work!    I'm normally horrible with SQL but now I think I got a grip on it.

[Rudolf]

The query for counting the nr of movies in one cat would be:

SELECT COUNT(*) FROM flashmovies WHERE cat = id_of_cat

id_of_cat is the ID of the category

[Wal-Mart Security]

I found the query on the internet already, but I don't want to add another db query unless I absolutely have to.  Is there a way to do it with PHP?

[Rudolf]

Try this sql query instead

Koodi [Valitse] Laajenna


SELECT fc.*, count(*) as num FROM flash_cat as fc LEFT JOIN flash_movies as fm ON (fc.ID_cat = fm.cat) GROUP BY fc.ID_cat


You will have a new $row['num'] value to use. 

[Wal-Mart Security]

That worked and didn't affect any of my other $row variables.  Could you explain how this query works?  I really appreciate it

[Rudolf]

First of all I changed from cross join (,) to left join, because semantically it describes better what you want.
The rows in the result set will be joined based on the ID_cat and cat fields, this way the resultset will for each category as many rows as movies correspond from the other table.
The result set at this point is something like:

ID_cat	catName	ID of movie where cat = ID_cat
1	First category	1
1	First category	2
1	First category	3
2	Second category	4
3	Third category	5
3	Third category	6

Next the query tells that the results should be grouped by the ID_cat field, we don't actually need n row for every category. The grouped results will look something like this:

ID_cat	catName	ID of movie where cat = ID_cat
1	First category	1
2	Second category	4
3	Third category	5

Finally COUNT is an aggregate function that counts the items that are in a group. Having COUNT(*) will 'select' the number of items in that group. So you will have something like this:

ID_cat	catName	ID of movie	num
1	First category	1	3
2	Second category	4	1
3	Third category	5	2

Not too technical an explanation, but I hope it helps.

[Wal-Mart Security]

Wow.  I think I should definitely get a guide for SQL, any suggestions?