Mysql - store a count using a column

Question

Hello,

I might have missed something about MySQL.

I'm trying to find a way to store a "count" in a column. It has to be dynamically updated without any manual update.

I don't want to add or remove 1 to the count any time the member creates or delete a content. The field in contentcount must automatically be a count of every content owned by this member.

create table content
(
   id_content           int not null auto_increment,
   id_member            int not null,
   primary key (id_content)
);

create table member
(
   id_member            int not null auto_increment,
   contentcount         ???????????????,
   primary key (id_member)
);

alter table content add constraint fk_member_content foreign key (id_member)
      references member (id_member) on delete cascade on update cascade;

How can I achieve this ? Thank you for any help.

Answer 1

You don't need to store the count in as a field in the table in order to access it; you can access it at any time using

SELECT COUNT(field_name) FROM table_name;

Note that if field_name contains some NULL values these won't contribute to the count, so it's best to use a column that never contains null values (such as a primary key or other index field)

Answer 2

I think this can only be accomplished with an after insert and an after delete trigger. In the after insert you increment the contentcount column, in the after delete you decrement it.

Answer 3

CREATE TRIGGER teh_counter AFTER INSERT ON content
FOR EACH ROW
BEGIN
update member set contentcount=(contentcount+1) where id_member=NEW.id_member;
END;

Answer 4

Trigger is the way to go if you really want this stored in the database. But storing this is unnecessarily (adding complexity), and probably slower than not storing it. Just add an index to the member_id column of content.

Then use:

  SELECT COUNT(*) FROM content WHERE member_id = ?

It's also pretty easy to select multiple members and order by their content count, if necessary.

Answer 5

I would create a table with auto_increment, insert a row into it, use the last_insert_id() function to get the value that that insert has generated, and then store it

Answer 6

I wanted to store the contentcount because I'm using Propel/AMFPHP and class mapping between Flex AS3 and PHP classes generated by Propel.

Triggers for update/delete is my solution. Never used a trigger before so I'm gonna learn.

Thank you all for your answers. I'm impressed that you answered so quickly !