Posted by J.O. Aho on 10/22/05 16:21

Schraalhans Keukenmeester wrote:
> X-Followup: comp.lang.php
>
> 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
>
> $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.


Why don't you make your msgid column in the mysql to an AUTO_INCREMENT?
There is a flaw in your code that can cause problems if there happens to be
more than one person who adds something to the database, the

$query = 'select count(*) from messagesdb;';

may be executed more than once before a new row is inserted, which leads to
multiple use of the same msgid.

When you insert your 201st row and then delete row 198, and then insert a new
row again, the id will be 201 again and you will end up with multiples of the
same msgid.

You should add the UNIQUE to the megid and maybe even PRIMARY KEY too, so that
you will not have this kind of troubles of multiple msgids.


> I am wondering, is php messing up, or is this a mysql glitch, or am I
> missing something here ?

it's the php code you write that most likely is the cause of the trouble.


//Aho

[Back to original message]