Posted by Gordon Burditt on 10/22/05 20:06

>I have a PHP script that adds messages to a simple MySQL Database.
>(PHP 5.0.3, MySQL 4.1.1)
>
>One of the fields it stores is msgid.
>The new msgid is a count of all current msgs in the db plus one

If you ever delete a message, this will mess up. It would work
better if you make the new msgid the maximum of the message ids in
the db plus one. This only screws up if you delete the previously
highest msgid.

There is also a possibility of messing up if two copies of the script
running at the same time both do their select, then both do their
inserts.

It's exactly this problem that auto_increment solves.

>$query = 'select count(*) from messagesdb;';
>$result = mysql_query ($query, $conn);
>$msgid = mysql_result ($result, 'count(*)') + 1;
>
>The next message is added using the above msgid.
>For some reason (there are NO other scripts/systems accessing this
>table, it is all on a local testmachine) I now have about 200 messages
>in the system, but some id's occur more than once, up to 4 times.
>
>I cannot explain this behaviour. I know I could avoid the entire issue
>by autonumbering the messages, but still there is something funny going on.
>
>I am wondering, is php messing up, or is this a mysql glitch, or am I
>missing something here ?

If the msgid is supposed to be unique, you should have a unique index
on it.

Gordon L. Burditt

[Back to original message]