<?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! :D I'm normally horrible with SQL but now I think I got a grip on it.
The query for counting the nr of movies in one cat would be:
LainaaSELECT COUNT(*) FROM flashmovies WHERE cat = id_of_cat
id_of_cat is the ID of the category
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?
Try this sql query instead
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.
That worked and didn't affect any of my other $row variables. Could you explain how this query works? I really appreciate it :)
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.
Wow. I think I should definitely get a guide for SQL, any suggestions?