php - SQL Upvote Downvote system -

i working on forum posts need have upvote/downvote system.

my current sql(phpmyadmin) structure this:

table 1 (posts) | post_id | post_title | post_score |  table 2 (pvotes) | pvote_id | fk_post_id | fk_user_id | pvote_score | 

i want somehow make post_score (in table 1), find pvote_score (table 2) columns , add/subtract them together, fk_post_id (table 2) = post_id (table 1)

this way hope make voting system allows every user vote once, , automatically calculate posts post_score pvote_score values.

example: user_1 upvotes post_1 inserting following table 2: | (pvote_id) 1 | (fk_post_id) 1 | (fk_user_id) 1 | (pvote_score) 1 |

i want post_score (table 1) find entries in table 2 where: fk_post_id same post_id, , thereafter add or subtract values pvote_score , make sum new value of post_score.

i trying make work stackoverflows own upvote/downvote system.

edit 1:

question:

i want know how can make post_score column automatically add/subtract values pvotes_score , show sum value?

i've created database structure this:

create table `posts` (   `post_id` int(11) unsigned not null auto_increment,   `post_title` varchar(50) default null,   `post_score` int(11) default null,   primary key (`post_id`) ) engine=innodb auto_increment=3 default charset=latin1;  insert `posts` values (null, 'test', 0), (null, 'test2', 0);  create table `pvotes` (   `pvote_id` int(11) unsigned not null auto_increment,   `fk_post_id` int(11) default null,   `fk_user_id` int(11) default null,   `pvote_score` int(11) default null,   primary key (`pvote_id`) ) engine=innodb auto_increment=5 default charset=latin1;  insert `pvotes` values (null, 1, 0, 2), (null, 1, 0, 3), (null, 1, 0, -1), (null, 2, 0, 2); 

this query should trick:

update posts set post_score = (select sum(pvote_score) pvotes fk_post_id = post_id); 

the result i've got this:

post_id | post_title | post_score

1 | test | 4

2 | test2 | 2