How to automatically subtract or add data from columns in mysql


Problem :

I am building a system for doctors and patients where the patients submit the type of the medical examination and the quantity of medicine they use. I want to be able to subtract the value of the quantity in the database with PHP but without the user input. The type of the examination and the quantity are on different tables. Is that possible?

What I have done so far :

$records = $handler->query("INSERT INTO exam(id, code, name, medical_exam) VALUES('', '$_POST[code]', '$_POST[name]', '$_POST[medical_exam]')");

And I want to also do this:

$records = $handler->query("UPDATE quantity AND SET quantity = quantity -10");

is that possible within 1 MySQL statement??



Solution :

is that possible within 1 mysql statement??

Yes. Just seperate your SQL Statements with semicolon ;

Do something like

$records = $handler->query("INSERT INTO `exam`(`id`, `code`, `name`, `medical_exam`) VALUES('', '$_POST[code]', '$_POST[name]', '$_POST[medical_exam]');UPDATE `quantity` AND SET `quantity` = (`quantity` - 10);");

Mysql Tutorials

Mysql Howto..

how to get result if 2nd field is empty in mysql [closed]

How to run a java program on a server?

How to parse in the timezone into this php prettyprint library to print out the time according to set timezone?

How to make a “distinct” join with MySQL

How to use MySQL group_concat() function in more than one column

How to check only the first two letters from a mysql cell?

MySQL how to update 325 matches inside table in one go?

How to connect to a remote MySQL database via SSL using Play Framework?

How to select many database rows using custom rule?

How to send query from iOS to PHP code

show numbers there are not in a column in MySQL

how to check if there exists 2nd highest score in SQL?

how to combine more than one mysql query into single query

How to connect to a MySql-Server in the internet from MySql Workbench?

MySQL How To Use Main Select Value As Subquery Argument?

How to find the Server Name of MySQL

How to create a uniqule field in database mysql [closed]

How to select the field's increment from mysql

How to sum two mysql results row by row?

how to connect java and mysql using mysql connector java 5.1.12

How to connect mysql to objective c?

how to sent the ANY operand mysql

How to empty a very large mysql table?

How to calculate monthly average in MySQL given monthly data that spans more than one month?

How to add an offset to all timestamps/DATETIME in a MySQL database?

How to link students with form questions in mysql

How to use “any” in select list of a form to work in mysql?

How do i append an auto increment primary key to another field in the same table?

mysql query to show what doesnt match

How to properly handle a car race in MySQL? [closed]