Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    New Coder
    Join Date
    Jan 2012
    Posts
    27
    Thanks
    5
    Thanked 0 Times in 0 Posts

    mysql Select timestamp

    Hello
    I am trying to select must commented thread of the week, and I have tried a few ways, but cant seem to get it working.

    Hope you can help...



    PHP Code:
    $sql "SELECT thread_id,thread_name,comment_timestamp, COUNT(thread_name) AS p_count FROM comment WHERE comment_timestamp >= CURRENT_timestamp - INTERVAL 7 DAYS GROUP BY thread_name ORDER BY p_count DESC LIMIT 0,1";
    $result mysql_query($sql$conn) or die(mysql_error());
    while (
    $newArray mysql_fetch_array($result))
     {
    $thread_name $newArray[thread_name'];
    $thread_id = $newArray[thread_id'
    ];
    $p_count $newArray['p_count'];
    echo 
    "<center><a href=thread_view.php?thread_id=$thread_id'>$thread_name</a><br>Comments ($p_count)<br><br></center>";


  • #2
    Regular Coder
    Join Date
    Jun 2010
    Posts
    293
    Thanks
    63
    Thanked 8 Times in 8 Posts
    Did you write the code yourself? I can see two syntax errors,

    Try this:

    PHP Code:
    $sql "SELECT thread_id,thread_name,comment_timestamp, COUNT(thread_name) AS p_count FROM comment WHERE comment_timestamp >= CURRENT_timestamp - INTERVAL 7 DAYS GROUP BY thread_name ORDER BY p_count DESC LIMIT 0,1";
    $result mysql_query($sql$conn) or die(mysql_error());

    while (
    $newArray mysql_fetch_array($result))
    {
      
    $thread_name $newArray['thread_name'];
      
    $thread_id $newArray['thread_id'];
      
    $p_count $newArray['p_count'];
      echo 
    "<center><a href=thread_view.php?thread_id=$thread_id'>$thread_name</a><br>Comments ($p_count)<br><br></center>";


  • #3
    New Coder
    Join Date
    Jan 2012
    Posts
    27
    Thanks
    5
    Thanked 0 Times in 0 Posts
    sorry didnt work

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DAYS GROUP BY thread_name ORDER BY p_count DESC LIMIT 0,1' at line 1

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    I believe MySQL is like PHP. That should be +/- DAY not DAYS for the interval.

  • #5
    New Coder
    Join Date
    Jan 2012
    Posts
    27
    Thanks
    5
    Thanked 0 Times in 0 Posts
    I have no error now, but it is not displaying anything

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Quote Originally Posted by soupy1985 View Post
    I have no error now, but it is not displaying anything
    And using print mysql_num_rows($result); shows how many rows?

  • #7
    New Coder
    Join Date
    Jan 2012
    Posts
    27
    Thanks
    5
    Thanked 0 Times in 0 Posts
    nothing

  • #8
    New Coder
    Join Date
    Jan 2012
    Posts
    27
    Thanks
    5
    Thanked 0 Times in 0 Posts
    0 sorry

  • #9
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Quote Originally Posted by soupy1985 View Post
    nothing
    Then you have no records matching the criteria you have specified.
    The reason is your selection of records. My assumption is you intend to find the most recent reply to the most popular topic in the last 7 days. Problem is you are selecting the most popular topics which started within the past 7 days.
    The MySQL guys can help you much better than I can with this, but I'd assume if you need to find the most recent reply based on popularity you will need to get the max date as well. So try this:
    Code:
    SELECT thread_id, thread_name, comment_timestamp, count(thread_name) as p_count FROM comment GROUP BY thread_name HAVING MAX(comment_timestamp) >= NOW() - INTERVAL 7 DAY ORDER BY p_count DESC LIMIT 0,1;

  • #10
    New Coder
    Join Date
    Jan 2012
    Posts
    27
    Thanks
    5
    Thanked 0 Times in 0 Posts
    what i want is the most commented thread this week.

    Thanks

  • #11
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Quote Originally Posted by soupy1985 View Post
    what i want is the most commented thread this week.

    Thanks
    And the result of the above provide?

  • #12
    New Coder
    Join Date
    Jan 2012
    Posts
    27
    Thanks
    5
    Thanked 0 Times in 0 Posts
    doesnt work, id i take out:

    HAVING MAX(comment_timestamp) >= NOW() - INTERVAL 7 DAY
    It gets the top thread of all time, so the rest of the code works, its just the timestamp part we are having probs with?

  • #13
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Is your comment_timestamp and actual datetime datatype?

  • #14
    New Coder
    Join Date
    Jan 2012
    Posts
    27
    Thanks
    5
    Thanked 0 Times in 0 Posts
    comment_timestamp is int(11) in db and it is added with time() when a commented is added. If that makes sence lol.

  • #15
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Then you cannot use dateinterval on it directly. You need to convert it first using FROM_UNIXTIME to convert the integer to a datetime.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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