I have a table called foo with columns like the following:
id
usedId (id of the person who posted this foo)
title
postedDate
What I want to do, is add another column called position, which will record how many foos a user has posted. E.g, his first posted foo will have the position of 1, 2nd will have 2, 100th will have 100, and so on. But this position is specific to the userId only.
Is there a way to add such a column to mysql, or I must calculate the position programatically?
Calculating it pragmatically is the best way to keep your database normalised, since you shouldn't have any data stored which can be calculated later. You should just order by the postedDate, and count which position you're on with each iteration.
However, if you were going to go ahead with this, you'd have to count the number of rows that the user already has, using a sub-query. So this would be part of your insert query:
[..], position = (SELECT COUNT(*) + 1 FROM foo WHERE userId = $userId), [..]
The sub-query would just return the number of rows already in the table for the user with the ID $userId, and increment it by one.
insert into foo (id, usedId, title, postedDate, position)
values (
1, 2, 'title', '2011-07-23', (select count(*) + 1 from foo where usedId = 2)
)
It seems like your question is related to
mysql auto-increment on a non-primary key
Or you should use triggers
