Thread: Structuring A Most Popular Topic Query?

1. it factors in the B ratings in the sense that if B + G = N, then it calculates G/N as a percentage

i'm sure you can work this around to get whatever variation you want

what do you mean by net value? G - B?

2. Yes, by net value, I mean G-B. Does that change anything?

3. if B+G=N, then G-B=2G-N
Code:
```select ...
, 2 * sum(case when rating_value = 'G'
then 1 else 0 end)
- ( select count(*)
from ratings
where rating_topic
= t.rating_topic ) as net_rating```

4. So the following would be the total code?

select rating_topic
, 2 * sum(case when rating_value = 'G'
then 1 else 0 end)
- ( select count(*)
from ratings
where rating_topic
= t.rating_topic ) as net_rating
from ratings as t
where rating_date between '2005-12-25' and '2006-01-02'
group
by rating_topic

Wouldn't I also need to add the Limit 10 part too?

Thanks!

order by net_rating desc, rating_topic limit 10

6. The following code doesn't seem to be working.

===================================================

\$sql_get_high_ratings = "select rating_topic
, 2 * sum(case when rating_value = 'G'
then 1 else 0 end)
- ( select count(*)
from ratings
where rating_topic
= t.rating_topic ) as net_rating
from ratings as t
where rating_date >= current_date AND rating_date < date_add(current_date, interval 1 day)
group
by rating_topic order by net_rating desc, rating_topic limit 10";

===================================================

And to make sure we are on the same page, A "G" rating adds 1 point to a topic's rating. A "B" rating subtracts 1 point from a topic's rating. So what the code is doing is finding the topics with the highest rating in a given time period. The above code is a bit difficult for me to understand.

7. "doesn't seem to be working" ==

- crashes the server?
- causes the query to go into an infinite loop?
- runs but returns 0 rows?
- runs but returns the wrong rows?
- produces an error? if so, what is the error?

8. I get this error message in each of the 10 looped outputs:

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in MYSERVER/highest_rating.php on line 45 (I bolded line 45 below)

=======================================

\$sql_get_high_ratings = "select rating_topic
, 2 * sum(case when rating_value = 'G'
then 1 else 0 end)
- ( select count(*)
from ratings
where rating_topic
= t.rating_topic ) as net_rating
from ratings as t
where rating_date >= current_date AND rating_date < date_add(current_date, interval 1 day)
group
by rating_topic order by net_rating desc, rating_topic limit 10";

\$res_get_highest_rating = mysql_query(\$sql_get_highest_rating);

\$i=0;
while (\$i < 10) {

\$sel_highest_rating = mysql_result(\$res_get_highest_rating,\$i,"rating_topic");

echo "<a href=MYSERVER/topic.php?topic=\$sel_highest_rating>\$sel_highest_rating</a> <br>";

\$i++;

}

=======================================

Thanks

9. sorry, those are php errors, not mysql errors

please run your query outside of php to test it and see what the error really says

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•