Order by amount of comments

[ArgamanZa]

Hey,

MySQL Ver: 4.1.21-standard

Here's my problem:

My site got some games i show, (let's call them "Items")

I'm using 2 tables:

 

The Items table:

Stores the items,

Important columns:

'id' (each created item is given an auto-incremented id)

'name'

'url'

'views'(how many people viewed the item)

'dateadded' (date of item creation),

 

The second table: the Votes table:

Contains the data of the rating system of the site, people can vote from 1 to 5 and then i use that data to

perform "total rating" / "number of voters" = Average Rating

Columns:

'name' (The 'name' of each row on the Votes table is equal to the item's 'id' column in the Items table)

'rateid' (auto incremented value i'm not really using)

'total' (Total rating, if 2 people voted 3 and 5 it will be

'votes' (How many people voted).

 

I got an "Order By" option, and so far i can order by Views, Rating and Date because i already got all that info in my tables and i just 'ORDER BY $order' and change the order to 'views' or whatever i need.

 

Now, i got a 3rd table called 'Comments',

It's the Comments system of my site, Columns:

'commentid' (Just auto incremented field to see Total number of comments)

'tutorialid' (Just like the 'name' field, the id on this field is equal to the item's id on the first table)

And of course Email, Name and Comment columns,

All the rest are not really important for my issue...

 

Now here's my problem, i wanna add a "Order by Comments" option,

So, i need to create something that contains the number of comments for each item id,

like 152 = 3, 100 = 0 ETC.

 

I have no real idea where to start and even though i know the Count() can help me i have no idea where to putt it,

Hope you people could help me,

 

Here's my code so far (Above in the HTML part of my page i got the order by drop menus which has to menus, one for

Views, Rating and Date, and one for DESC/ASC)

 

$per = 10;
$end=$per*$page;
$start = $per*($page - 1);  
$active=1;
mysql_query("SELECT *  FROM  vote  WHERE  name = '$id");
$order_opts = array(
		'date'=>'id',
		'views'=>'views',
		'rating'=>'IF(votes <> 0, total/votes, 0)',
		'random'=>'RAND()',
);
if(in_array($_GET['order'], array_keys($order_opts)))
{
	$order = $order_opts[$_GET['order']];
	$porder = $order_opts[$_GET['order']];
} else {
	$order = 'id';
	$porder = 'id';
}

$type_opts = array(
		'desc'=>'DESC',
		'asc'=>'ASC',
);

if(in_array(strtoupper($_GET['type']), $type_opts))
{
	$type = $type_opts[$_GET['type']];
	$ptype = $type_opts[$_GET['type']];
} else {
	$type = 'DESC';
	$ptype = 'DESC';
}

if(!$_GET['cat'])   {
$select_comments = mysql_query("SELECT * FROM  items JOIN vote ON(items.id = vote.name) where type='game' order by $order $type LIMIT $start,$per"); 
}
else    
{
$cat = $_GET['cat'];    
$select_comments = mysql_query("SELECT * FROM  items JOIN vote ON(items.id = vote.name) where type='game' and category='$cat' order by $order $type LIMIT $start,$per"); 
}

 

the $select_comments is the part where i select items from my tables.

If you got any question just ask i'll reply fast with the info.