469,623 Members | 1,420 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,623 developers. It's quick & easy.

Glitch in php or count() in mysql?

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.

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 ?

Thanks for your time
Sh
Oct 22 '05 #1
7 2089
Schraalhans Keukenmeester wrote:
$query = 'select count(*) from messagesdb;';
$result = mysql_query ($query, $conn);
$msgid = mysql_result ($result, 'count(*)') + 1;

I know I could avoid the entire issue
by autonumbering the messages
YES! (Btw, does the ; in the query not pose trouble?)
but still there is something funny going on.


Concurrent updates perhaps.

--
E. Dronkert
Oct 22 '05 #2
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
Oct 22 '05 #3
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.

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 ?

Thanks for your time
Sh


In addition to what the others have said, if you ever delete a message
from the table, COUNT(*) will be decremented and you'll have duplicate
values.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Oct 22 '05 #4
>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
Oct 22 '05 #5

"J.O. Aho" <us**@example.net> wrote in message
news:3r************@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

Oct 23 '05 #6
Tony Marston wrote:
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.


This can still generate doublets of msgid as the select statment can be
trigged by two different people trying to add something,
AUTO_INCREMENT UNIQUE should in most cases be used for ID (numerial) columns.
//Aho
Oct 23 '05 #7

"J.O. Aho" <us**@example.net> wrote in message
news:3s************@individual.net...
Tony Marston wrote:
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.

Not if you lock the table bfeore performing the 'select max(id)' and the
'insert'.

--
Tony Marston

http://www.tonymarston.net
This can still generate doublets of msgid as the select statment can be
trigged by two different people trying to add something,
AUTO_INCREMENT UNIQUE should in most cases be used for ID (numerial)
columns.
//Aho

Oct 24 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

15 posts views Thread by David O'Farrell | last post: by
reply views Thread by motorpsychkill | last post: by
2 posts views Thread by TGOS | last post: by
1 post views Thread by Eddie | last post: by
11 posts views Thread by Ted Mayett | last post: by
3 posts views Thread by auron | last post: by
3 posts views Thread by Auddog | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.