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 7 2288
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
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
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*******@attgl obal.net
=============== ===
>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
"J.O. Aho" <us**@example.n et> 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
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" <us**@example.n et> 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: David O'Farrell |
last post by:
Is this only on solaris ?
Python 2.3.3 (#1, Mar 19 2004, 16:18:33)
on sunos5
Type "help", "copyright", "credits" or "license" for more information.
>>> a=
>>> print a.count(333), a.count(66.6), a.count('x')
2 1 0
>>> a.append(333)
>>> print a
|
by: motorpsychkill |
last post by:
I have two tables: bc_post and bc_message. This is what I have so far:
select bc_post.*, count(bc_message.message_to) responses from
bc_post left join
bc_message on
bc_post.post_id = bc_message.message_topostid
where
bc_post.post_status = '1' and
bc_post.user_id = '1'
group by
|
by: Fatt Shin |
last post by:
Hi,
I'm running MySQL 4.0.13, connecting from PowerBuilder 9 using ODCB
Connector 3.51.
I'm facing a problem where whenever I issue a SELECT COUNT(*) statement
from PowerBuilder, I always get SQL syntax error back from MySQL. (Refer
to ODBC Trace I captured below).
metrohouse af8-b94 ENTER SQLExecDirect
HSTMT 014D2360
UCHAR * 0x020A0EA2 "select count ( *) from code
|
by: TGOS |
last post by:
Here's a little question to MySQL Gurus.
Two tables. One called 'document' one called 'page'. Needles to say, a
document consist out of multiple pages (1:n). The primary (P) and
foreign (F) keys are:
*** document
document_id (P)
|
by: Eddie |
last post by:
I am having a strange issue with MySQL. I just installed it on my Zaurus
and loaded a few of my databases in it. All very small.
When I do a count withoout a where, it hangs. It does this on all my
databases I have loaded. Example below.
_______________________________________________________________________
mysql Ver 9.38 Distrib 3.22.32, for unknown-linux-gnu (armv4l)
Connection id: 8
| |
by: Ted Mayett |
last post by:
OK. Here is a glitch, sorry if this has been mentioned before.
This is an erratic glitch. I am now up to three other people besides
myself who have been able to see this glitch. It seems it only
happens in IE. And the <hr> causes this thing to happen.
Now, I have ~tried~ to make this glitch, and that is tough to do. I
had did it that one time, and I should have saved the work. But I
didn't save the work :(
|
by: auron |
last post by:
Hi there,
I have a really stupid and banal problem with showing the results of a
MySQL query in PHP, preciselly with MySQL count() function that gives
to a variable in PHP the result.
NOTE:
The problem here is PHP not MySQL, in MySQL everything works just
fine.
Here is the query that I wrote for getting the number of how much
|
by: Luongo |
last post by:
Hi, I'm working on a mysql-based survey program. On the user page I display the survey form, and on the admin page I display the results for each question. The problem, which I find puzzling, is that on the results page the order of the questions (sorted by specified question numbers) are slightly jumbled, while on the user form they work fine. I've looked at these mysql queries for a while but can't seem to pinpoint the problem. I'll paste...
|
by: Auddog |
last post by:
I have the following query that works in mysql:
select id, order_no, price, count(item_no), sum(price) from production
WHERE item_no = '27714'
group by item_no;
When I setup my query in php, I use:
$query2 = "SELECT id, order_no, price, count(item_no) as count from
production where item_no = '27714";
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |