php - INSERT SELECT FROM WHERE generating a row for every occurence of WHERE

I have two sales_orders and sales_invoices. My goal is to copy data from sales_orders to sales_invoices on the press of a button.

I am calling a function in PHP from a submit in a form that passes the WHERE variable (sales_order_id). I have confirmed the variable is being called correctly and when I do this statement direct in mysql it succeeds just as expected, but when I run it in my function it creates a row for every occurence of the sales_order_id (50 rows, as opposed to a single row). I am using 12345 for my sales_order_id.

What am I doing wrong?

Here is my query:

INSERT into sales_invoices(si_customer_id, si_purchase_order_number, ref, si_gross_total)
 select so_customer_id, so_purchase_order_number, sales_order_id, sum(so_gross_total + so_vat_adjustment_value) as gross_total
 from sales_orders where sales_order_id = 12345
Copy code

and here is my PHP code

function create_invoice(){

    if(isset($_POST['create_invoice'])){

        $sales_order_id = escape_string($_POST['sales_order_id']);

        $query = query("INSERT into sales_invoices(si_customer_id, si_purchase_order_number, ref, si_gross_total)
 select so_customer_id, so_purchase_order_number, sales_order_id, sum(so_gross_total + so_vat_adjustment_value) as gross_total
 from sales_orders where sales_order_id = $sales_order_id");
        confirm($query);


    }

}
Copy code

All the varaibles work as they should, tested by echoing them out... sales_order_id is a hidden input on the form and is being pulled through as it should.

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.