On 28 Sep, 10:36, "João Morais" <jcsmor...@gmail.com> wrote:
> Hi there guys,
>
> I have a table like the one below:
>
> CREATE TABLE `news` (
> `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
> `name` VARCHAR( 100 ) NOT NULL ,
> `position` INT( 11 ) NOT NULL
> ) ENGINE = MYISAM ;
>
> And the field position specifies the position of each news while they
> are being displayed.
>
> What I have at the moment is the following processes:
>
> * When adding a new item, I need to know what is the max position, so
> that the new item position will be: max_position + 1 (adding at the
> bottom);
>
> That I can achieve with:
>
> SELECT position
> FROM news
> ORDER BY position DESC
> LIMIT 1
>
> And I think this isn't the best way of doing this, since this system
> is multi-user and maybe there will be problems if two users add a new
> item at the same time, then, there will be 2 equal positions.
>
> Is this correct?
> How can I avoid this?
>
> I also have the same kind of problem while deleting items, imagine
> that a user deletes a couple of items and another user at the same
> time updates some items positions, won't that be a problem? I'm using
> transactions on this one, but I'm not sure it will avoid this
> situation.
>
> Same doubt when moving an item up or down (using transactions on this
> one too).
>
> Thanks in advance.

Why not just use your autoincrement field?