...

View Full Version : mysql Select timestamp



soupy1985
01-12-2012, 02:01 PM
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...




$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
01-12-2012, 09:41 PM
Did you write the code yourself? I can see two syntax errors,

Try this:



$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
01-15-2012, 10:23 AM
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
01-15-2012, 11:17 AM
I believe MySQL is like PHP. That should be +/- DAY not DAYS for the interval.

soupy1985
01-15-2012, 11:19 AM
I have no error now, but it is not displaying anything :(

Fou-Lu
01-15-2012, 11:27 AM
I have no error now, but it is not displaying anything :(

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

soupy1985
01-15-2012, 11:32 AM
nothing

soupy1985
01-15-2012, 11:36 AM
0 sorry

Fou-Lu
01-15-2012, 11:40 AM
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
01-15-2012, 11:42 AM
what i want is the most commented thread this week.

Thanks

Fou-Lu
01-15-2012, 11:44 AM
what i want is the most commented thread this week.

Thanks

And the result of the above provide?

soupy1985
01-15-2012, 11:45 AM
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
01-15-2012, 11:47 AM
Is your comment_timestamp and actual datetime datatype?

soupy1985
01-15-2012, 11:50 AM
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
01-15-2012, 11:54 AM
Then you cannot use dateinterval on it directly. You need to convert it first using FROM_UNIXTIME to convert the integer to a datetime.

soupy1985
01-15-2012, 12:00 PM
I might give up, lol

Fou-Lu
01-15-2012, 12:03 PM
All you need to do is add FROM_UNIXTIME() around the MAX() call. That will convert the unix timestamp into a datetime datatype.


Also, check with the MySQL guys. I can think of a way to make this work by grabbing the lowest thread_id and the max date by title without using a having, but requires more aggregates. From an optimization standpoint, I don't know which will win.

soupy1985
01-15-2012, 12:05 PM
what do you mean by around, sorry for being a pain.

Fou-Lu
01-15-2012, 12:07 PM
what do you mean by around, sorry for being a pain.

Change this: MAX(comment_timestamp) to this FROM_UNIXTIME(MAX(comment_timestamp))

soupy1985
01-15-2012, 12:20 PM
for some reason its just displaying the second most commened thread of all time?

Fou-Lu
01-15-2012, 12:42 PM
for some reason its just displaying the second most commened thread of all time?

That could be a possibility yes. This will take the thread that has the most comments with a post within the past 7 days. All it takes is one if its already high in comments.
Is that not what you wanted?

soupy1985
01-15-2012, 12:48 PM
I want it to be any thread, it doesnt have to be one with most comments of all time, im trying to show what has been the most active thread of the week.

Fou-Lu
01-15-2012, 12:59 PM
Oh of the week as a total. I see that you mentioned that already too.
Just use your original query then, and convert the timestamp with FROM_UNIXTIME first. That will work fine so long as you don't need to retrieve the original thread_id since that will not be available in the inclusion unless it was created that week. That'll become a mess if you need to do that since the thread to post isn't normalized.

soupy1985
01-15-2012, 01:06 PM
$sql = "SELECT thread_id,thread_name,comment_timestamp, COUNT(thread_name) AS p_count FROM comment WHERE FROM_UNIXTIME(comment_timestamp) >= CURRENT_timestamp - INTERVAL 7 DAY GROUP BY thread_name ORDER BY p_count LIMIT 0,1";


???

Fou-Lu
01-15-2012, 01:15 PM
Yeah, but you'll need to add the DESC to the p_count.

soupy1985
01-15-2012, 01:23 PM
Thank you so much, works fine. :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum