Page 1 of 1

mysql help ?

Posted: Wed Feb 03, 2010 7:08 am
by adilmarwat2004
I have mysql table named "act" contain fields:-

id, name, pay, allowances, total_salary

I want that when i amount of pay field & allowances field should add and display in total_salary field. How it is possible.

Please help me in serious matter.

thanks
Adil

Re: mysql help ?

Posted: Wed Feb 03, 2010 11:26 am
by JakeJ
I realize that English is not your first language but your question is very confusing. Please clarify.

Re: mysql help ?

Posted: Wed Feb 03, 2010 11:46 am
by AbraCadaver
adilmarwat2004 wrote:I have mysql table named "act" contain fields:-

id, name, pay, allowances, total_salary

I want that when i amount of pay field & allowances field should add and display in total_salary field. How it is possible.

Please help me in serious matter.

thanks
Adil
Something like:

Code: Select all

INSERT INTO act (`name`, `pay`, `allowances`, `total_salary`) VALUES ('$name', $pay, $allowances, $pay+$allowances)

Re: mysql help ?

Posted: Wed Feb 03, 2010 12:09 pm
by JakeJ
AbraCadaver wrote:
adilmarwat2004 wrote:I have mysql table named "act" contain fields:-

id, name, pay, allowances, total_salary

I want that when i amount of pay field & allowances field should add and display in total_salary field. How it is possible.

Please help me in serious matter.

thanks
Adil
Something like:

Code: Select all

INSERT INTO act (`name`, `pay`, `allowances`, `total_salary`) VALUES ('$name', $pay, $allowances, $pay+$allowances)
You're almost right - Try this:

Code: Select all

INSERT INTO act (name, pay, allowances, total_salary) VALUES ('$name', '$pay', 'allowances', '$pay'+'$allowances')
I should add that it's almost never necessary to store a calculated value in a table. You can just add them in your code when you use them. Write a function or do it manually. Storing calculated values only clutters up your database.

Re: mysql help ?

Posted: Wed Feb 03, 2010 12:45 pm
by AbraCadaver
I agree with not storing a calculated value, however stating that I was almost right when it was entirely right was strange. Quotes aren't needed for numeric values and are normally used to prevent SQL injection attacks which you should take care of with a real_escape_string() / type casting / filtering etc. before insert.

Also, with your insert, every row will have 0 for `allowances` field because you passed in the literal text 'allowances' instead of the value of $allowances ;-)

Re: mysql help ?

Posted: Wed Feb 03, 2010 1:06 pm
by JakeJ
AbraCadaver wrote:I agree with not storing a calculated value, however stating that I was almost right when it was entirely right was strange. Quotes aren't needed for numeric values and are normally used to prevent SQL injection attacks which you should take care of with a real_escape_string() / type casting / filtering etc. before insert.

Also, with your insert, every row will have 0 for `allowances` field because you passed in the literal text 'allowances' instead of the value of $allowances ;-)
LOL - you didn't have quotes around all of your variables though ;)

Ah! And I missed a $. Too funny!

I did such a quick read on that, I thought it was the query string from the original poster. I saw the missing quotes and corrected (and made more mistakes).

Re: mysql help ?

Posted: Wed Feb 03, 2010 1:37 pm
by AbraCadaver
That was my point. I had quotes around the value for the text field. They're not needed for numeric data, and I assumed that monetary amounts would be stored in a numeric field.

Re: mysql help ?

Posted: Wed Feb 03, 2010 3:53 pm
by JakeJ
AbraCadaver wrote:That was my point. I had quotes around the value for the text field. They're not needed for numeric data, and I assumed that monetary amounts would be stored in a numeric field.
Maybe I'm confused (i am fairly new at this) but aren't quote marks required around all variables regardless of data type?

Re: mysql help ?

Posted: Wed Feb 03, 2010 4:26 pm
by AbraCadaver
JakeJ wrote:
AbraCadaver wrote:That was my point. I had quotes around the value for the text field. They're not needed for numeric data, and I assumed that monetary amounts would be stored in a numeric field.
Maybe I'm confused (i am fairly new at this) but aren't quote marks required around all variables regardless of data type?
Nope. You should always quote text/varchar etc... but they will normally insert without them unless they contain a space. Now it can help against SQL injection, but if you are inserting a number you should have already checked/casted it: $id = (int)$_POST['id']; etc.