mysql update issue

[Christy]

i have come across a problem while solving bugs and improving an currently existing system.

The prob can be best explained with an example. the input given to a form is
basic = 10000
scale = 16400-450-20900-500-22400

i need to add 450 to basic, everytime i submit it. (here 450 is picked from scale)
so the expected o/p is basic = 10450

but the output i get is 10900. the mysql query UPDATE is running twice for some reason. the code is mentioned below. if any of u could give me an soln , that would be cool.

i have asked a few of colleagues but even they were stumped. 

this is the structure of the attribute of the table pay_bill
CREATE TABLE IF NOT EXISTS `pay_bill` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `empid` varchar(6) COLLATE latin1_general_ci NOT NULL,
  `basic` int(15) NOT NULL,
  `other_al` int(255) NOT NULL,
  `scale` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `work_type` varchar(255) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=170 ;


// the code for the page where values is changing
<?php
...
$emp=$_GET['empid'];
$query =  mysql_query("SELECT basic,scale FROM pay_bill WHERE empid= '$emp' ") or die(mysql_error());
$qpay=mysql_fetch_array($query);
$q2 = $qpay['basic'];
$q5 = $qpay['scale'];  

$t_value = explode("-",$q5);

$val= $t_value[1];  
$sum = $val + $q2;

mysql_query( "UPDATE pay_bill SET basic='$sum' WHERE empid='$emp' ") or die(mysql_error());
?>


I have also tried another method to first DELETE and then INSERT the value, still the problem  persists.

Any solns.
Do reply soon as its an error within my current project.

--
By,

Christopher U,
SD,
DBIT.

[Rahul Isola]

Hi,

I used the code that you have given, and for me, the output comes properly, 10450.

Maybe d error is somewhere else, i dunno. But what you have given in d mail shows O/P as expected.

Regards,
Rahul Isola
http://theisopages.tk/
!$õ

--
Email to group : dbit-t...@googlegroups.com
To unsubscribe, send email to
dbit-techtal...@googlegroups.com
Visit this group at
http://groups.google.com/group/dbit-techtalk?hl=en

[peter dmello]

I'm not sure if this is the cause of your problem, but it could be that your form is being submitted twice (due to some reason). Hence the update is performed twice.

Regards,
Peter

[Christy]

hi friends,

isola when i run the file directly even i get the expected o/p. 

but if i call the page externally the above mentioned problem arises.

<a href='increment.php?empid=$empid'><input type='button' value='Increment'></a>

using the above code the page was linked to file. i.e using href and GET property.   

 agree with peter that the file is getting called twice somehow, although i dont know how. when i click here i get redirected to the file and the wrong o/p arise.

[peter dmello]

Ok... here's the problem:

1. Clicking the hyperlink calls increment.php?empid=$empid
2. So also, the form is getting submitted by default since you have kept the button inside a hyperlink tag. If the button doesn't perform any javascript task, remove it. I suppose you have kept it only for visual purposes (look and feel).

Just to make my point clear, try this. Make a form and keep just a text box in that form. Don't keep anything else. Type in a value in the text box and hit the enter key. The form gets submitted without having a submit button.

Get rid of the button which is inside the anchor 'a' tag. And your problem will be solved.

[Christy]

finally, i got the soln. as mentioned before the issue was with the the page calling.   

<a href='increment.php?empid=$empid'><input type='button' value='Increment'></a>

plain <a> tag gives no issue but with an <input> tag it gave this issue. 

so changing it too

<a href='increment.php?empid=$empid'>Increment</a>

this works.

i am still pissed at the person who developed this system, coz this problem must have been there since the start. coz of him i had to break my head for one full day. 

thx isola,peter coz of u i could pinpoint that it wasn't an mysql version issue or so. 

thx again.

[Christy]

thx peter i read ur mail now. i have already found the soln. the prob was <a> tag as u predicted. 

i found the soln around the same time u did therefore didnt read ur mail before sending the previous mail. 

anyway thx a lot. this will definitely help since this same bug might also arise in other places in this same project. 

thank u

[Sreepathi Pai]

On Thu, Jul 22, 2010 at 1:06 PM, Christy <christo...@gmail.com> wrote:
> finally, i got the soln. as mentioned before the issue was with the the page
> calling.
> <a href='increment.php?empid=$empid'><input type='button'
> value='Increment'></a>
> plain <a> tag gives no issue but with an <input> tag it gave this issue.
> so changing it too
> <a href='increment.php?empid=$empid'>Increment</a>
> this works.
> i am still pissed at the person who developed this system, coz this problem
> must have been there since the start. coz of him i had to break my head for
> one full day.

Don't you use version control? Then you could find out who it actually was?

Secondly, it was indeed being submitted twice. Once for the A, and
once for the INPUT.

Thirdly, you are NOT supposed to use GET for updating databases. You
MUST use POST. This is because GET must be idempotent.

Fourthly, did you [or the database designer] not study normalization
of databases wrt to the scale column?

Fifthly (and finally), do you not know that UPDATE can actually
perform the addition itself and if you had followed normalization
rules, it would be just a single update statement?

--
Sreepathi Pai

[Christy]

abt the db design, s/w was done by someone else. i was assigned with the work of making things works in  . 

abt 

Don't you use version control? Then you could find out who it actually was?

> i already know who made the system. but the person has left. although i'll take up ur suggestion of version control. 

 

Secondly, it was indeed being submitted twice. Once for the A, and
once for the INPUT.

> thx i wanted to know the technical reason as to why it was getting submitted twice. 

Thirdly, you are NOT supposed to use GET for updating databases. You
MUST use POST. This is because GET must be idempotent.

> i mainly prefer POST only turn to GET sometimes. In this case the code was already using GET so i stuck with it. 

 

Fourthly, did you [or the database designer] not study normalization
of databases wrt to the scale column?

> as i mentioned earlier the db design was done by the initial developer. causing change in table structure now may coz prob. 

 

Fifthly (and finally), do you not know that UPDATE can actually
perform the addition itself and if you had followed normalization
rules, it would be just a single update statement?

> i didnt know abt it, Hence forth i'll make it a point to apply normalization as an when needed appropriately in projects where i am involved in the DB design. 

thx sree for the concise explanation.

 

On a side note i am quite surprised about the responses, didnt expect it to be such a long thread coz there wasnt any activity on techtalk lately. Nice to see techtalk live and running. 

 

--
Sreepathi Pai

--
Email to group :  dbit-t...@googlegroups.com
To unsubscribe, send email to
dbit-techtal...@googlegroups.com
Visit this group at
http://groups.google.com/group/dbit-techtalk?hl=en

[Sreepathi Pai]

On Fri, Jul 23, 2010 at 1:35 AM, Christy <christo...@gmail.com> wrote:
>> Thirdly, you are NOT supposed to use GET for updating databases. You
>> MUST use POST. This is because GET must be idempotent.
>
>> i mainly prefer POST only turn to GET sometimes. In this case the code was
>> already using GET so i stuck with it.

Here's a thumb rule: If you modify something on the database side,
then you should mostly use POST.
If it's just a view action, and this is important: IF THERE IS NO HARM
IN RUNNING TWICE (or more), then use GET. You should read the HTTP RFC
for more details.

A browser will prevent a POST from running twice, while it will
happily run GET any number of times.

>> Fifthly (and finally), do you not know that UPDATE can actually
>> perform the addition itself and if you had followed normalization
>> rules, it would be just a single update statement?
>>
>> i didnt know abt it, Hence forth i'll make it a point to apply
>> normalization as an when needed appropriately in projects where i am
>> involved in the DB design.

Atleast the following rules were violated here:

* scale is not an atomic field
* scale could possibly have been a foreign key

In my opinion, getting the database design correct is crucial to
performance and correctness!

--
Sreepathi Pai

[Narayan rao]

I found this cool open source web app for php source control.
You can use this to keep a track of all the versions of file edit done and who exactly has done it.
http://asvcs.com/

ASVCS stands for "A simple version control system".
I suggest you should try this on a pilot scale and once mastered try to include the entire colmation project into it.

Regards,
Narayan Rao

_______________________________________________________________________________________________________

Vision without action is a daydream. Action with without vision is a
nightmare.......

---

From: Sreepathi Pai <sre...@gmail.com>
To: dbit-t...@googlegroups.com
Sent: Fri, 23 July, 2010 10:03:30 AM
Subject: Re: [Techtalk] mysql update issue

On Fri, Jul 23, 2010 at 1:35 AM, Christy <christo...@gmail.com> wrote:

>> Thirdly, you are NOT supposed to use GET for updating databases. You
>> MUST use POST. This is because GET must be idempotent.
>
>> i mainly prefer POST only turn to GET sometimes. In this case the code was
>> already using GET so i stuck with it.

Here's a thumb rule: If you modify something on the database side,
then you should mostly use POST.
If it's just a view action, and this is important: IF THERE IS NO HARM
IN RUNNING TWICE (or more), then use GET. You should read the HTTP RFC
for more details.

A browser will prevent a POST from running twice, while it will
happily run GET any number of times.

>> Fifthly (and finally), do you not know that UPDATE can actually
>> perform the addition itself and if you had followed normalization
>> rules, it would be just a single update statement?
>>
>> i didnt know abt it, Hence forth i'll make it a point to apply
>> normalization as an when needed appropriately in projects where i am
>> involved in the DB design.

Atleast the following rules were violated here:

* scale is not an atomic field
* scale could possibly have been a foreign key

In my opinion, getting the database design correct is crucial to
performance and correctness!

--
Sreepathi Pai

--
Email to group :  dbit-t...@googlegroups.com
To unsubscribe, send email to

dbit-techtalk+unsub...@googlegroups.com

[Sreepathi Pai]

On Sun, Aug 15, 2010 at 6:34 PM, Narayan rao <techn...@yahoo.co.in> wrote:
> I found this cool open source web app for php source control.
> You can use this to keep a track of all the versions of file edit done and
> who exactly has done it.
> http://asvcs.com/
> ASVCS stands for "A simple version control system".
> I suggest you should try this on a pilot scale and once mastered try to
> include the entire colmation project into it.

Umm ... why? This looks simple, but is quite likely overkill. I mean,
why require a webserver and PHP for version control? Also, it doesn't
seem to support important VCS concepts like conflict detection and
merging.

Narayan, have you actually used a VCS? Any VCS? Can you compare the
workflow of a command-line VCS with this?

Please use bzr/git/svn to get a flavour of what actual VCSes are like.

My most common personal bzr workflow uses only these commands:

1. bzr init (once per project)
2. bzr add (once per new file)
3. bzr diff (many times during development)
4. bzr stat (many times during development)
5. bzr commit (many times during development)
6. bzr push (many times during development)
7. bzr branch
7. bzr merge
8. bzr info
9. bzr log

If you do want a UI, check out bzr's GTK UI:
<http://wiki.bazaar.canonical.com/bzr-gtk>

--
Sreepathi Pai

[Narayan rao]

Hey Sree,
Please correct me if i am wrong.
The idea that i get on looking at bzr is in line with a share point. Though a bit more complicated and i feel is platform dependent.
Well i have been using Team foundation server(TFS) lately in my office project and have a bit(if not say in depth) understanding of version control though i guess bzr is generic to all files. Though i am not advertising for  ASVCS but i feel is good for starters as to get a hands on and also the system being on web is platform independant. So you can use the system even though you are developing the system sitting on a windows machine as well as linux machine.And since it is basically built for php it need not be customized for our php development need.Also i agree it does not have the complex facilities of merging though it has some naive functionality to view the revisions made to a page.
So both have their trade offs.

Thanks,
Narayan Rao

 

_______________________________________________________________________________________________________

Vision without action is a daydream. Action with without vision is a
nightmare.......

---

From: Sreepathi Pai <sre...@gmail.com>
To: dbit-t...@googlegroups.com

Sent: Sun, 15 August, 2010 6:49:06 PM

Subject: Re: [Techtalk] mysql update issue