SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 34
  1. #1
    SitePoint Evangelist sp0om's Avatar
    Join Date
    Feb 2004
    Location
    MN
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Structuring A Most Popular Topic Query?

    On a commenting system I'm working on, I'm wondering how to put together a query, or set or queries that will get the most popular topics by date.

    The comments are split up into two different tables.

    comment_info
    comment_text

    From the comment_info table, I can get all of the posts made today, for instance, with the following code (thanks spf btw for helping with this):

    PHP Code:
    SELECT FROM comments_info WHERE comment_date LIKE '2006-01-13%' 
    From there, I'm pretty much stuck. I imagine I would maybe set up some loops or something to organize the posts under each topic and count how many posts were made in each topic, then spitting them out to show the top 10 most popular topics by post. Any guidance or code would be very very very helpful.

    Thanks much.

    -sp0om

  2. #2
    SitePoint Wizard
    Join Date
    Jan 2004
    Location
    3rd rock from the sun
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Might depend on what version of Mysql you are using.

    If you are using 4.x you can do something called "subselects" which will do 2 things pick from query a based on something from query a, and the other thing is you need to be just very slightly drunk to work it out. (well, I do)
    isempty()

  3. #3
    SitePoint Evangelist sp0om's Avatar
    Join Date
    Feb 2004
    Location
    MN
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Bump.

    Anyone? This is really holding my site back.

    Thanks

  4. #4
    SitePoint Wizard
    Join Date
    Jan 2004
    Location
    3rd rock from the sun
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you are using Mysql 4 go and read up on "subselects".

    If you are using Mysql 3 do what you said.

    Select the comment_type then for each one {
    select the comment text...
    }

    You may also question the wisdom of having 2 tables now - why dont you merge then into one, then youve only got to do one select?
    isempty()

  5. #5
    SitePoint Evangelist sp0om's Avatar
    Join Date
    Feb 2004
    Location
    MN
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Come to think of it, I only need to grab data from one table, the comment_id table.

    So how would this change things?

    - select today's comments
    - split them by topic (don't know how to do this)
    - count how many are in each topic (don't know how to do this)

    I'm going to look up subselects now.

    Thanks.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    subselects don't work in mysql until 4.1, whether you've been drinking or not

    sp0om, you'll have better answers in the mysql forum -- want me to move this thread over there for you?

    you may want to show the CREATE TABLE statement for the table(s), and give some more information about what you mean by "most popular by date"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist sp0om's Avatar
    Join Date
    Feb 2004
    Location
    MN
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Surely. Please move this thread.

    What I want to have happen is to list the top 10 topics by the number of comments made today. I imagine this could all be done with the following table:

    comment_info

    - comment_id
    - comment_topic
    - comment_username
    - comment_date

    So, the process would go something like this:

    1. grab all posts made today
    2. split them by topic
    3. count up the number of comments per topic
    4. spit out the most popular topics by comments made today

    Does that all make sense? I have no idea how to do steps 2-4 above and would appreciate any help. Thanks much.

    Edit: I'm running MySQL 4.0.22

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    your column called comment_date, what is its datatype?

    if it's DATE, here's your query:
    Code:
    select comment_topic
         , count(*) as comments
      from comment_info
     where comment_date = current_date
    group
        by comment_topic
    order
        by comments desc limit 10
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist sp0om's Avatar
    Join Date
    Feb 2004
    Location
    MN
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    This is the code I have so far, but I don't know how to display the data.

    PHP Code:
    $sql_get_most_active SELECT comment_topic count(*) as comments FROM comment_info WHERE comment_date LIKE '$date_today%' GROUP BY comment_topic ORDER BY comments DESC LIMIT 10;

    $res_get_most_active mysql_query($sql_get_most_active); 
    Where would I go from here?

    Thanks!!!

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you can't use LIKE on a DATE column -- DATEs aren't strings

    (actually, in MySQL you can, but that's an unfortunate "misfeature" which will only come back to bite you)

    what is the datatype of the comment_date column please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Evangelist sp0om's Avatar
    Join Date
    Feb 2004
    Location
    MN
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    datetime, 0000-00-00 00:00:00

    I'm using the LIKE for other date related queries. Should I be storing the date and time in a different format? Should I split the date and time into separate fields?

    Thanks.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    DATETIME is perfect

    no, please do not split a date and a time, if they are related

    (unrelated dates and times are exceeedingly rare)

    use this as your WHERE clause --
    Code:
    where comment_date >= current_date
      and comment_date < dateadd(current_date, interval 1 day)
    CURRENT_DATE is today's date without the time portion, so it is equivalent to midnight

    you want to include midnight this morning, but not include midnight tonight

    the above method is efficient, and will utilize an index, if any, on comment_date

    using LIKE means that the internal date representation of the date must first be converted to a string -- remember, LIKE is a string function -- which means that the index is ignored and you get a table scan

    you still get the same results, only slower, but remember, using LIKE "works" only in mysql, other databases won't let you use string functions on datetime columns

    and let's not even think about using a CHAR or VARCHAR column for datetimes

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Evangelist sp0om's Avatar
    Join Date
    Feb 2004
    Location
    MN
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good post. It makes a lot of sense now.

    Now how do I go about displaying this information?

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you're getting back 10 rows of 2 columns, just display them

    i might use OL and LI tags, many people use TABLE and TD tags

    was that your question? (i'm sorry, i don't do php)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Evangelist sp0om's Avatar
    Join Date
    Feb 2004
    Location
    MN
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm... no php. Dang. Because the following is giving me errors:

    PHP Code:
    where comment_date >= current_date
      
    and comment_date dateadd(current_dateinterval 1 day
    Ergh. Well, I appreciate the help. I'll take a look at the code and learn more about grouping results and that sort of thing. I'll be back with more questions for sure.

    Enjoy the weekend.

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    aaargh, sorry, my bad

    the function is DATE_ADD, not DATEADD
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Evangelist sp0om's Avatar
    Join Date
    Feb 2004
    Location
    MN
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Argh. Ok, silly mistake. Turns out the add_date thing was wrong and I forgot to put quotes around the entire thing. Well, it's working now, but I still don't know what code to use to display the data? What would be the query to display the first row of data from our first query?

    PHP Code:
    $sql_get_most_active "SELECT comment_topic, count(*) AS num_comment FROM comments WHERE comment_date >= current_date && grade_date < date_add(current_date, interval 1 day) GROUP BY comment_topic ORDER BY num_comment DESC LIMIT 10"

  18. #18
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    see example #1 at http://us3.php.net/manual/en/ref.mysql.php

    specifically, the while loop in the Printing results in HTML section.

  19. #19
    SitePoint Evangelist sp0om's Avatar
    Join Date
    Feb 2004
    Location
    MN
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Perfect, thanks.

    There is one little remaining problem. When there are two or more topics with the same amount of comments in the top 10 topics, they are always queried in a different order. Like lets say that the following topics are in the top 10 (focus on topic 2, topic 3, and topic 4 before and after the refresh):

    topic 1 # of comments : 57
    topic 2 # of comments : 46
    topic 3 # of comments : 46
    topic 4 # of comments : 46

    topic 5 # of comments : 35
    topic 6 # of comments : 26
    topic 7 # of comments : 27
    topic 8 # of comments : 14
    topic 9 # of comments : 6
    topic 10 # of comments : 3

    REFRESH POPULAR TOPICS PAGE:

    topic 1 # of comments : 57
    topic 4 # of comments : 46
    topic 2 # of comments : 46
    topic 3 # of comments : 46

    topic 5 # of comments : 35
    topic 6 # of comments : 26
    topic 7 # of comments : 27
    topic 8 # of comments : 14
    topic 9 # of comments : 6
    topic 10 # of comments : 3

    How can I further organize the topics alphabetically (or by most recent comment) so that the order will stay the same each time?

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you can do this by adding the alphabetic topic to the ORDER BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Evangelist sp0om's Avatar
    Join Date
    Feb 2004
    Location
    MN
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried adding AND comment_topic to the code, but it didn't seem to like that.

    $sql_get_most_active = "SELECT comment_topic, count(*) AS num_topic FROM comments WHERE comment_date >= current_date && comment_date < date_add(current_date, interval 1 day) GROUP BY comment_topic ORDER BY num_grade AND comment_topic DESC LIMIT 10";

    What is the correct way to do this?

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT comment_topic
         , count(*) AS num_topic 
      FROM comments 
     WHERE comment_date >= current_date 
       and comment_date < date_add(current_date, interval 1 day) 
    GROUP 
        BY comment_topic 
    ORDER 
        BY num_topic DESC
         , comment_topic 
    LIMIT 10
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    SitePoint Evangelist sp0om's Avatar
    Join Date
    Feb 2004
    Location
    MN
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ^ the above post works like a dream. Thanks.

    Alright, time for another twist.

    I have a topic_rating table in my database. How can I structure a query or set of queries to get the highest rated topic.

    This is how the topic rating works. A user can rate a topic as good or bad. When a topic is rated, data is added into the topic rating table:

    rating_id
    rating_date
    rating_topic
    rating_username
    rating_value

    The first 4 fields are pretty self explanatory. For the last field, rating_value, either "G" for good or "B" for bad is added to the table. So there is a big table of ratings. After doing some queries, you can find the net score for a topic by doing:

    The number of "G" ratings per topic - The number of "B" ratings per topic

    So what I'm hoping to accomplish is a series of queries that will find the most popular topics by net rating in a given time period. Does that make sense?

    This is the final hurdle in my site. The rest is adding some validation/security, moderation, and the best part: graphics/design. Oh joy.

    Any help would be great. Thanks.

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by sp0om
    So what I'm hoping to accomplish is a series of queries that will find the most popular topics by net rating in a given time period. Does that make sense?
    yes, it does -- but does it have to be a series of queries? would a single query work for you?
    Code:
    select rating_topic
         , sum(case when rating_value = 'G'
                    then 1 else 0 end)
           / ( select count(*)
                 from ratings
                where rating_topic
                    = t.rating_topic ) as avg_rating
      from ratings as t
     where rating_date between '2005-12-25' and '2006-01-02'  
    group
        by rating_topic
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Evangelist sp0om's Avatar
    Join Date
    Feb 2004
    Location
    MN
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1 query sounds good to me

    Does the above code factor in the "B" ratings too? Also, I just need a net value instead of an average.

    Thanks!


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
  •