Problem
Edit: I’ve added everything I can…
I have the following query, which gets the last comment from the user in a given topic, then I check in the while loop if there are newer comments.
My problem is, that the while loop runs through every thread the user commented (imagine a user commented 2000 times, then this runs 2000 times).
Can I simplify it somehow to run only if there’s a newer comment?
EDIT: I can, added answer below, but new query is slow.
With the current database setup probably not, but maybe I’m missing something.
In my comment database I have columns like date (comment date), user_id, topic_id (and picture_id and news_id ehich is the id of the given topic, pciture or news) and seen (which is a datetime and it gets updated when the user checks the given thread).
E.g.:
id (1) date (2018-12-02 10:00:00), topic_id (3), user_id (3), text (comment text...), seen (2018-12-02 10:00:00)
id (2) date (2018-12-04 15:30:00), topic_id (3), user_id (5), text (comment text...), seen (2018-12-04 15:30:00)
User 3 goes back to the thread on 12-05 12:00, so seen gets updated from 2018-12-02 10:00:00 to 2018-12-05 12:00:00).
Schema
CREATE TABLE `comment` (
`id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`topic_id` int(11) NOT NULL,
`picture_id` int(11) NOT NULL,
`news_id` int(11) NOT NULL,
`text` text COLLATE utf8_hungarian_ci NOT NULL,
`date` datetime NOT NULL,
`reply_id` int(11) NOT NULL,
`comment_vote` int(11) NOT NULL,
`comment_hit` int(11) NOT NULL,
`moderated` int(11) NOT NULL,
`seen` datetime NOT NULL,
`deleted` tinyint(4) NOT NULL,
`ip` text COLLATE utf8_hungarian_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci;
PHP code
$sql = "SELECT p1.* FROM comment p1 INNER JOIN
(SELECT max(date) MaxPostDate, user_id
FROM comment WHERE user_id='$me' and deleted=0
GROUP BY topic_id, picture_id, news_id) p2
ON p1.user_id = p2.user_id AND p1.date = p2.MaxPostDate
WHERE p1.user_id='$me' and deleted=0
ORDER BY p1.date DESC "
$comment_query = sql_query($conn, $sql);
if(sql_num($comment_query)!=0)
{
while ($comment = sql_fetch($comment_query))
{
Here’s some more code, if you need it for some reason:
if($comment['topic_id']!=0)
{
$current_forum = sql_fetch(sql_query($conn, "SELECT url, name
FROM forum
WHERE id='".$comment['topic_id']."' and deleted=0"));
$current_comments = sql_fetch(sql_query($conn, "SELECT count(id) as count, date
FROM comment
WHERE deleted=0 and topic_id='".$comment['topic_id']."'"));
$comment_topic_id = $comment['topic_id'];
$comment_id = $comment['id'];
$comment2_query = sql_fetch(sql_query($conn,"SELECT count(id) AS cid
FROM comment
WHERE topic_id=".$comment_topic_id ." and id<".$comment_id ." and deleted=0 "));
$result = $comment2_query['cid'] + 1;
if($comment['seen']=='0000-00-00 00:00:00') {
$unread = $current_comments[0] - $result;
if($unread!=0)
{
if((!empty($_GET['p'])) and $_GET['p']=='forum' and
$_GET['x']==$current_forum['url'])
//If I'm at the specific url (I'm watching the new comments, so update it)
{
$now = date('Y-m-d H:i:s');
sql_query($conn,"UPDATE comment SET seen='$now' WHERE user_id='$me' AND id='$comment_id' AND topic_id='.$comment_topic_id.' ");
}
else //increase number to add it to noficiation bell
{
$count++;
$forum_notif++;
}
}
else
{
$last_time_seen = $comment['seen'];
$count_comments = sql_fetch(sql_query($conn,"SELECT count(id) AS cid
FROM comment
WHERE topic_id=".$comment_topic_id." and deleted=0 and date>'.$last_time_seen.' "));
if($count_comments['cid']!=0)
{
if((!empty($_GET['p'])) and $_GET['p']=='forum' and
$_GET['x']==$current_forum['url'])
{
$now = date('Y-m-d H:i:s');
sql_query($conn,"UPDATE comment SET seen='$now' WHERE user_id='$me' AND id='$comment_id' AND topic_id='.$comment_topic_id.' ");
}
else
{
$count++;
$forum_notif++;
}
}
}
}
elseif($comment['picture_id']!=0)
{
//same code again for a different type of forum (only topic_id is replaced to picture_id and the links)...
}
elseif($comment['news_id']!=0)
{
//same code again for a different type of forum (only topic_id is replaced to new_id and the links)...
}
}
} //This is the whole code
Solution
I found a solution, but the query is really slow (on first time, it’s obviously quick when coming from cache).
This new query is great, it does exactly what I want finally (get only the comments after a user commented), but unfortunately it’s slow (~2 sec), when not cached.
$sql = "SELECT p1.* FROM comment p1 INNER JOIN
(SELECT max(seen) last_seen, max(id) MaxId,
user_id, topic_id, picture_id, news_id, date
FROM comment WHERE user_id='$me' and deleted=0
GROUP BY topic_id, picture_id, news_id) p2
ON p1.topic_id = p2.topic_id and p1.picture_id= p2.picture_id
and p1.news_id= p2.news_id
WHERE p1.date > p2.last_seen and p1.id > p2.MaxId and deleted=0
GROUP BY p1.topic_id, p1.picture_id, p1.news_id ORDER BY p1.date DESC"
The original query was this one:
$sql = "SELECT p1.* FROM comment p1 INNER JOIN
(SELECT max(date) MaxPostDate, user_id
FROM comment WHERE user_id='$me' and deleted=0
GROUP BY topic_id, picture_id, news_id) p2
ON p1.user_id = p2.user_id AND p1.date = p2.MaxPostDate
WHERE p1.user_id='$me' and deleted=0
ORDER BY p1.date DESC"