CODEWITHSUNDEEP
mysql
Mohammed Ahmed
Mohammed Ahmed

Reputation: 455

auto update column in mysql

Currently I build the database for my next project however My database include discussions, posts and some other table

discussions table 

id | user_id | title | comments_count | replies_count

posts table

id | user_id | content | type

type in the posts table may be comment,discussion of reply I want comments_count column in discussions table to be auto refer and auto select the count of posts that have the type comment and I want same thing for replies_count.

If this is not possible can help me to auto update the comments and replies count columns automatically using Triggers or events

I hope you can help me

Thanks :)

Upvotes: 0

Views: 5358

Answers (1)

lorandd
lorandd

Reputation: 174

[EDITED]

Assuming you have this schema:

CREATE TABLE discussions(id INT,user_id INT, title VARCHAR(50), comments_count INT, replies_count INT);
CREATE TABLE posts(id INT,user_id INT, content VARCHAR(50), type VARCHAR(50));

and data in the tables like:

INSERT INTO discussions VALUES
(1,1,"test1",0,0),
(2,2,"test2",0,0);

This trigger might do the trick you want

DELIMITER $

CREATE TRIGGER update_comments_count_trigger
AFTER INSERT ON posts
FOR EACH ROW
BEGIN
  UPDATE discussions d 
  SET d.comments_count = (SELECT count(*) from posts where user_id = NEW.user_id and type="comment");

  UPDATE discussions d 
  SET d.replies_count = (SELECT count(*) from posts where user_id = NEW.user_id and type="reply");


END

So when you insert values to posts table like:

INSERT INTO posts VALUES 
(1,1,'content1','comment'),
(1,2,'content2','comment'),
(1,2,'reply1','reply');

the comments_count and replies_count should be updated.

Upvotes: 1

Related Questions