php - How to automatically subtract or add data from columns in mysql


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??




is that possible within 1 mysql statement??

Yes. Just separate 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);");



Won't this work?

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

And by the way is the table name "quantity"?! don't forget that>

UPDATE tablename SET field1 = value1



I would do it in two steps. First read quantity and store it in a php variable. Then reduce it by 10 and finally update the database with the new quantity value. I think it is safer that way.

People are also looking for solutions to the problem: php - How to parse xml with xpath and retrieve an array of elements?


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.