Sorting(?) question

[Frankenchrist]

Hi,

I'm trying to do a film page with voting, but have a problem.

I'm reading the votes into $voteval[$i]
and the number of votes into $votenum[$i]

So for example, 4 persons has voted 3,3,5,6 on film no.12, so I have these variables:

$voteval[12] = 17
$votenum[12] = 4

Then I calculate the averages into $AVG[$i] with

$AVG[12] = $voteval[12] / $votenum[12];

Now, how can I sort/output $AVG[] so the film with the highest average is listed first?

Frank

[njm]

rsort() will sort an array in reverse (descending) order, but I didn't see where the film names were stored - how do you link up the films with the averages?

hth, njm

[Frankenchrist]

I'm using 3 tables, one with the films (film), one with the votes (seen) and one with users (who) which is not used.

They look like this:

film (filmID,filmName,filmYear etc.)

1,Spiderman,2002, etc.
2,Daredevil,2003, etc.
3,X-Men,2000, etc.

seen (seenID,sVote,sWho, etc.)

1,3,1, etc.
2,8,1, etc.
3,2,2, etc.
4,3,3, etc.

who (whoID,whoName etc.)

1,"Mike", etc.
2,"Jane", etc.
3,"John", etc.

Here's the php:

$SQL = "SELECT seen.sVote, film.filmID FROM film INNER JOIN seen ON (film.filmID = seen.sName)";
$SQLfilm = mysql_query($SQL) or die($SQL);
$SQLrows = mysql_num_rows($SQLfilm);
while ( $row = mysql_fetch_array($SQLfilm) ){
$i++;
$FilmVote[$row['filmID']] = $FilmVote[$row['filmID']] + $row['sVote'];
$FilmVoteNum[$row['filmID']]++;
}

for ($i = 1; $i <= $SQLrows; $i++) {
if($FilmVote[$i]){ // only process film that has votes
$AVG[$i] = number_format($FilmVote[$i]/$FilmVoteNum[$i],2);
print $AVG[$i]
}
}

Maybe there's a better way to do it?

[leatherback]

Hi,

Have alook at the mysql website
There is a way to calculate the average directly from the database, if I am correct:

SELECT avg(seen.sVote) as aver_vote, film.filmID FROM film INNER JOIN seen ON (film.filmID = seen.sName) group by filmID order by aver_vote desc";

[Frankenchrist]

Thanks! You pointed me in the right diection!
I solved it.

this works just fine:

SELECT
film.filmID,
avg(seen.sVote) AS AV
FROM
film
INNER JOIN seen ON (film.filmID = seen.sName)
GROUP BY
film.filmID
ORDER BY
AV DESC

Thanks again!