SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 34 of 34
  1. #26
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  2. #27
    SitePoint Evangelist sp0om's Avatar
    Join Date
    Feb 2004
    Location
    MN
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, by net value, I mean G-B. Does that change anything?

  3. #28
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #29
    SitePoint Evangelist sp0om's Avatar
    Join Date
    Feb 2004
    Location
    MN
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  5. #30
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, add this --

    order by net_rating desc, rating_topic limit 10
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #31
    SitePoint Evangelist sp0om's Avatar
    Join Date
    Feb 2004
    Location
    MN
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #32
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    "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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #33
    SitePoint Evangelist sp0om's Avatar
    Join Date
    Feb 2004
    Location
    MN
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #34
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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