mysql Select timestamp

[soupy1985]

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>";
} 


[XmisterIS]

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>";
} 


[soupy1985]

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

[Fou-Lu]

I believe MySQL is like PHP. That should be +/- DAY not DAYS for the interval.

[soupy1985]

I have no error now, but it is not displaying anything

[Fou-Lu]

Originally Posted by soupy1985

I have no error now, but it is not displaying anything

And using print mysql_num_rows($result); shows how many rows?

[soupy1985]

nothing

[soupy1985]

0 sorry

[Fou-Lu]

Originally Posted by soupy1985

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:

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;

[soupy1985]

what i want is the most commented thread this week.

Thanks

[Fou-Lu]

Originally Posted by soupy1985

what i want is the most commented thread this week.

Thanks

And the result of the above provide?

[soupy1985]

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?

[Fou-Lu]

Is your comment_timestamp and actual datetime datatype?

[soupy1985]

comment_timestamp is int(11) in db and it is added with time() when a commented is added. If that makes sence lol.

[Fou-Lu]

Then you cannot use dateinterval on it directly. You need to convert it first using FROM_UNIXTIME to convert the integer to a datetime.