How best to position PHP mysql query to run multiple query in single execution.

Question

0.00/5 (No votes)

See more:

I have a task I've been trying to solve, to do update and insert query into different tables in one click of button.
I did the code as below, the update side of the code performed ok, but the insert part could not insert to the table.

How do I reposition my code to be able to achieve my aim. I am relatively new in PHP, I have been a C# person.

NB: I called my database connection from config file.

PHP

$code = $_POST['code'];
		$name = $_POST['name'];
		$description = $_POST['description'];
		$qty_stock = $_POST['qty_stock'];
		$price = $_POST['price'];

 $qty_stock = $_POST['qty_stock'];
				 $update_return_inventory = "UPDATE `product_list` SET qty_stock = qty_stock - $qty_stock  WHERE `code` = '{$code}'";			
			     $update_invetroty_save = $this->conn->query($update_return_inventory);
				if ($update_invetroty_save)
				{				
                   //Performing Insert to another table  			
					$insert_inventory_log = "INSERT INTO `inventory_log`(`code`, `name`, `description`, `qty_stock`, `price`) values ('$code', '$name', '$description', '$qty_stock', '$price')";
					$insert_invetroty_save = $this->conn->query($insert_inventory_log);
					if ($insert_invetroty_save)
					{
						$this->settings->set_flashdata("Inventory update successfully saved.");
					}
				}

What I have tried:

I tried running the query separately, the query worked in separate mode.

Posted 28-Aug-23 0:54am

Member 12652465

Updated 28-Aug-23 7:58am

Deeksha Shenoy

v3

Add a Solution

Comments

Richard MacCutchan 28-Aug-23 7:00am

"but the insert part could not insert to the table."
What exactly happens when you run this code?

Member 12652465 28-Aug-23 7:08am

The update part updated the expected table, and the insert did not insert to the expected table, but there were no error message.

Richard MacCutchan 28-Aug-23 7:21am

Then you need to use the debugger to find out why the insert did not happen.

Member 12652465 28-Aug-23 8:09am

can you recommend a debugger to use, i use vs code to work.

Richard MacCutchan 28-Aug-23 8:18am

There is PHP Debug (Xdebug) in the VS Code extensions.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andre Oosthuizen · Accepted Answer · 2023-08-28T04:31:00

At the top of your php page, add the following for error checking/debugging -

PHP

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

I have the added additional error checking for you which you should always include in your code to help you see where your code is failing -

PHP

$code = $_POST['code'];
$name = $_POST['name'];
$description = $_POST['description'];
$qty_stock = $_POST['qty_stock'];
$price = $_POST['price'];

$update_return_inventory = "UPDATE `product_list` SET qty_stock = qty_stock - $qty_stock WHERE `code` = '{$code}'";
$update_inventory_result = $this->conn->query($update_return_inventory);

if ($update_inventory_result) {
    //Performing an insert into your 'inventory_log' table...
    $insert_inventory_log = "INSERT INTO `inventory_log`(`code`, `name`, `description`, `qty_stock`, `price`) VALUES ('$code', '$name', '$description', '$qty_stock', '$price')";
    $insert_inventory_result = $this->conn->query($insert_inventory_log);

    if ($insert_inventory_result) {
        //If both update and insert are successful, set a flash message...
        $this->settings->set_flashdata("Inventory update successfully saved.");
    } else {
        //If the insert fails, handle the error...
        //You might want to log or display an error message...
        $this->settings->set_flashdata("Error: Inventory insert failed.");
    }
} else {
    //If the update fails, handle the error...
    //You might want to log or display an error message...
    $this->settings->set_flashdata("Error: Inventory update failed.");
}