Krdytkyu

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?

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