Professional Web Applications Themes

Glitch in php or count() in mysql? - 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 ...

  1. #1

    Default 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
    Schraalhans Keukenmeester Guest

  2. #2

    Default Re: Glitch in php or count() in mysql?

    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 ?
    Race condition? If user 1 increments the $msgid and user 2 selects
    count(*) before user 1 has added her message -> duplicate entries for
    this $msgid. You can avoid this by putting a unique constraint on your
    message ID. Which you should have done anyway ... Or using transactions.
    Or autoincrement. Or you could, of course, re-invent other wheels.


    Christian Kirsch Guest

  3. #3

    Default Re: Glitch in php or count() in mysql?

    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
    J.O. Aho Guest

  4. #4

    Default Re: Glitch in php or count() in mysql?

    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.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  5. #5

    Default Re: Glitch in php or count() in mysql?

    >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 s 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
    Gordon Burditt Guest

  6. #6

    Default Re: Glitch in php or count() in mysql?


    "J.O. Aho" <userexample.net> wrote in message
    news:3rusigFla8mtU1individual.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
    [url]http://www.tonymarston.net[/url]


    > 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

    Tony Marston Guest

  7. #7

    Default Re: Glitch in php or count() in mysql?

    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
    J.O. Aho Guest

  8. #8

    Default Re: Glitch in php or count() in mysql?


    "J.O. Aho" <userexample.net> wrote in message
    news:3s1l8jFln4j6U1individual.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

    [url]http://www.tonymarston.net[/url]


    > 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

    Tony Marston Guest

Similar Threads

  1. I have a little glitch...
    By Robert Misiorowski in forum Macromedia Flash
    Replies: 0
    Last Post: April 4th, 08:17 PM
  2. Is it just me, or is this a glitch in CS?
    By Rydog23@adobeforums.com in forum Adobe Illustrator Macintosh
    Replies: 7
    Last Post: May 12th, 02:34 AM
  3. Is this a glitch??
    By Rydog23@adobeforums.com in forum Adobe Illustrator Macintosh
    Replies: 2
    Last Post: February 23rd, 02:56 PM
  4. [mysql] SELECT COUNT(*) question
    By Floris van den Berg in forum PHP Development
    Replies: 6
    Last Post: September 20th, 10:36 AM
  5. newbie : trying to count records in mysql table
    By jim in forum PHP Development
    Replies: 1
    Last Post: July 11th, 02:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139