Page 1 of 1

doing this efficiently?

Posted: Sun Oct 22, 2006 12:20 am
by rami
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


i am making a joke system where the user posted by registered user can be rated...beside rating for each joke i am trying to develop efficient mechanism to display the over all rating of user ie ...average of rating from all the posted jokes...
i have come up with this

Code: Select all

<?php

$query="Select jokes_id from jokes WHERE userid=$userid";
$result=mysql_query($query);
while ($row=mysql_fetch_array($result,MYSQL_ASSOC))
{
$jokeid=$row['jokes_id'];
//here for simplicity i am not checking in mysql_num_row>0 for now
$query1="Select rating from ratings WHERE jokeid=$jokeid";
$result1=mysql_query($query1);
while ($row1=mysql_fetch_array($result1,MYSQL_ASSOC))
{
$individual_rate=$row1['rating'];
$total+=$individual_rate;
$count++;
}
}
$overallrate=$total/$count;
echo "you over all rate is $overall";
?>
the problem i see here is nested query or in a way subquery which is considered to be enemy of processor..or makes things slow...
and i want to display list of top 10(regarding over all average rating) in home page.....
how can i do that..i am not getting idea how to join tables to do that...
any idea how can it be made better or done efficiently


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Tue Oct 24, 2006 4:56 am
by amir
Try this,

SELECT
*
FROM
jokes jk, ratings rt
WHERE jk.userid = '$userid'
AND rt.jokeid = '$jokeid'

Comment both other queries,

Re: doing this efficiently?

Posted: Tue Oct 24, 2006 5:46 am
by timvw
I would suggest that you read a manual, book, .. on aggregate functions...

(untested)

Code: Select all

SELECT     
                 jokeid, 
                 SUM(rating) / COUNT(*) AS rating 
FROM        
                 ratings 
GROUP BY 
                 jokeid 
ORDER BY  
                 rating DESC 
LIMIT        
                 10;
I presume you can take it from here...