Posted by Tony Marston on 10/23/05 13:10

"J.O. Aho" <user@example.net> wrote in message
news:3rusigFla8mtU1@individual.net...
> 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 be using 'select max(msg_id) from messagesdb;' instead of 'select
count(*) from messagesdb'. In this way it will not matter if any previous
entries get deleted.

--
Tony Marston
http://www.tonymarston.net



> 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]