3
\$\begingroup\$

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
\$\endgroup\$
0

1 Answer 1

1
\$\begingroup\$

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"
\$\endgroup\$
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.