Submit dynamically created row values into the database

[Webville312]

Dear all,

I have been working on this code and I included dynamically created rows.
The rows are being created very well.
However, when it gets to the submission part, the code is only suubmitting the first row ONLY.
Can someone point me in the right direction please.
Below is the code that I am working with;

<?
if($_POST['Submit'])
{
    include_once $_SERVER['DOCUMENT_ROOT']."/src/constants.php";
    include_once $_SERVER['DOCUMENT_ROOT']."/src/Database/connect2DB3.php";

    echo "Processing, please wait...";

    //Obtain the posted values
    $tax_invoice_id          = (array_key_exists('tax_invoice_id',$_POST)) ? $_POST['tax_invoice_id'] : "";
    $document_number         = (array_key_exists('document_number',$_POST)) ? $_POST['document_number'] : "";
    $our_reference           = (array_key_exists('our_reference',$_POST)) ? $_POST['our_reference'] : "";
    $order_date              = (array_key_exists('order_date',$_POST)) ? $_POST['order_date'] : "";
    $document_date           = (array_key_exists('document_date',$_POST)) ? $_POST['document_date'] : "";
    $purchase_order_number   = (array_key_exists('purchase_order_number',$_POST)) ? $_POST['purchase_order_number'] : "";
    $dispatch_date           = (array_key_exists('dispatch_date',$_POST)) ? $_POST['dispatch_date'] : "";
    $invoice_address         = (array_key_exists('invoice_address',$_POST)) ? $_POST['invoice_address'] : "";
    $invoice_branch_id       = (array_key_exists('invoice_branch_id',$_POST)) ? $_POST['invoice_branch_id'] : "";
    $account_number          = (array_key_exists('account_number',$_POST)) ? $_POST['account_number'] : "";
    $payment_due_date        = (array_key_exists('payment_due_date',$_POST)) ? $_POST['payment_due_date'] : "";
    $consignment_note_number = (array_key_exists('consignment_note_number',$_POST)) ? $_POST['consignment_note_number'] : "";
    $delivery_note_number    = (array_key_exists('delivery_note_number',$_POST)) ? $_POST['delivery_note_number'] : "";
    $order_type              = (array_key_exists('order_type',$_POST)) ? $_POST['order_type'] : DEFAULT_ORDER_TYPE;
    $tax_point_date          = (array_key_exists('tax_point_date',$_POST)) ? $_POST['tax_point_date'] : "";
    $delivery_address        = (array_key_exists('delivery_address',$_POST)) ? $_POST['delivery_address'] : "";
    $delivery_branch_id      = (array_key_exists('delivery_branch_id',$_POST)) ? $_POST['delivery_branch_id'] : "";
    $customer_vat_number     = (array_key_exists('customer_vat_number',$_POST)) ? $_POST['customer_vat_number'] : DEFAULT_VAT_NUMBER;
    $supplier_tin_number     = (array_key_exists('supplier_tin_number',$_POST)) ? $_POST['supplier_tin_number'] : "";
    $supplier_vat_number     = (array_key_exists('supplier_vat_number',$_POST)) ? $_POST['supplier_vat_number'] : "";
    $payment_terms           = (array_key_exists('payment_terms',$_POST)) ? $_POST['payment_terms'] : "";
    $vehicle_number          = (array_key_exists('vehicle_number',$_POST)) ? $_POST['vehicle_number'] : "";
    $haulier                 = (array_key_exists('haulier',$_POST)) ? $_POST['haulier'] : "";
    $order_created_by        = (array_key_exists('order_created_by',$_POST)) ? $_POST['order_created_by'] : "";
    $dispatched_from         = (array_key_exists('dispatched_from',$_POST)) ? $_POST['dispatched_from'] : "";
    $delivery_terms          = (array_key_exists('delivery_terms',$_POST)) ? $_POST['delivery_terms'] : "";
    $taxable_value           = (array_key_exists('taxable_value',$_POST)) ? $_POST['taxable_value'] : "";
    $vat_rate                = (array_key_exists('vat_rate',$_POST)) ? $_POST['vat_rate'] : DEFAULT_VAT_RATE;
    $tax_amount              = (array_key_exists('tax_amount',$_POST)) ? $_POST['tax_amount'] : "";
    $currency                = (array_key_exists('currency',$_POST)) ? $_POST['currency'] : DEFAULT_CURRENCY;
    $total_due               = (array_key_exists('total_due',$_POST)) ? $_POST['total_due'] : "";
    $number_of_line_entries  = (array_key_exists('number_of_line_entries',$_POST)) ? $_POST['number_of_line_entries'] : 0;
    $line_values_calculated_total       = (array_key_exists('line_values_calculated_total',$_POST)) ? $_POST['line_values_calculated_total'] : 0;
    $line_values_entered_total= (array_key_exists('line_values_entered_total',$_POST)) ? $_POST['line_values_entered_total'] : 0;
    $line_value_entered       = (array_key_exists('line_value_entered',$_POST)) ? $_POST['line_value_entered'] : "";
    $product_number             = $_POST['product_number'];

        $mysqli = connect2DB3();

        //Insert data in table Beer_Tax_Invoice_From_UBL
        $query = "INSERT INTO Beer_Tax_Invoice_From_UBL ";
        $query.= "SET ";
        $query.= "document_number = '".$document_number."', ";
        $query.= "our_reference = '".$our_reference."', ";
        $query.= "order_date = '".$order_date."', ";
        $query.= "document_date = '".$document_date."', ";
        $query.= "purchase_order_number = '".$mysqli->real_escape_string($purchase_order_number)."', ";
        $query.= "dispatch_date = '".$dispatch_date."', ";
        $query.= "invoice_branch_id = '".$mysqli->real_escape_string($invoice_branch_id)."', ";
        $query.= "invoice_address = '".$mysqli->real_escape_string($invoice_address)."', ";
        $query.= "account_number = '".$mysqli->real_escape_string($account_number)."', ";
        $query.= "payment_due_date = '".$payment_due_date."', ";
        $query.= "consignment_note_number = '".$mysqli->real_escape_string($consignment_note_number)."', ";
        $query.= "delivery_note_number = '".$mysqli->real_escape_string($delivery_note_number)."', ";
        $query.= "order_type = '".$mysqli->real_escape_string($order_type)."', ";
        $query.= "tax_point_date = '".$tax_point_date."', ";
        $query.= "delivery_branch_id = '".$mysqli->real_escape_string($delivery_branch_id)."', ";
        $query.= "delivery_address = '".$mysqli->real_escape_string($delivery_address)."', ";
        $query.= "customer_vat_number = '".$mysqli->real_escape_string($customer_vat_number)."', ";
        $query.= "supplier_tin_number = '".$mysqli->real_escape_string($supplier_tin_number)."', ";
        $query.= "supplier_vat_number = '".$mysqli->real_escape_string($supplier_vat_number)."', ";
        $query.= "payment_terms = '".$mysqli->real_escape_string($payment_terms)."', ";
        $query.= "vehicle_number = '".$mysqli->real_escape_string($vehicle_number)."', ";
        $query.= "haulier = '".$mysqli->real_escape_string($haulier)."', ";
        $query.= "order_created_by = '".$mysqli->real_escape_string($order_created_by)."', ";
        $query.= "dispatched_from = '".$mysqli->real_escape_string($dispatched_from)."', ";
        $query.= "delivery_terms = '".$mysqli->real_escape_string($delivery_terms)."', ";
        $query.= "line_values_calculated_total = '".$line_values_calculated_total."', ";
        $query.= "line_values_entered_total = '".$line_values_entered_total."', ";
        $query.= "taxable_value = '".$taxable_value."', ";
        $query.= "vat_rate = '".$vat_rate."', ";
        $query.= "tax_amount = '".$tax_amount."', ";
        $query.= "currency = '".$mysqli->real_escape_string($currency)."', ";
        $query.= "total_due = '".$total_due."', ";
        $query.= "number_of_line_entries = '".$number_of_line_entries."' ";

        echo "$query<br>";
        $result = $mysqli->query($query) or die($mysqli->error); 

        //Get beer_tax_invoice_id to be used in recording the invoice entry
        $beer_tax_invoice_from_ubl_id = $mysqli->insert_id;

        $query = "";

        foreach($product_number as $product_number_value => $product_number_count)
            {
                $product_number_identifier      = $product_number[$product_number_value];
                $product_description_identifier = $_POST["product_description"][$product_number_value];
                $product_abv_percent            = $_POST["product_abv_percent"][$product_number_value];
                $product_quantity               = $_POST["product_quantity"][$product_number_value];
                $uom                            = $_POST["uom"][$product_number_value];
                $base_price                     = $_POST["base_price"][$product_number_value];
                $customer_discount              = $_POST["customer_discount"][$product_number_value];
                $promotional_discount           = $_POST["promotional_discount"][$product_number_value];
                $net_price_per_unit             = $_POST["net_price_per_unit"][$product_number_value];
                $line_value                     = $_POST["line_value"][$product_number_value];
                $line_value_entered             = $_POST["line_value_entered"][$product_number_value];
                $vat_rate_perecentage           = $_POST["vat_rate_perecentage"][$product_number_value];

                //Prepare Query to Store to Beer_Tax_Invoice_Entry
                $query = "INSERT INTO Beer_Tax_Invoice_From_UBL_Entry ";
                $query.= "SET ";
                $query.= "beer_tax_invoice_from_ubl_id = '".$beer_tax_invoice_from_ubl_id."', ";
                $query.= "product_number = ".$product_number_identifier.", ";
                $query.= "product_description = '".$mysqli->real_escape_string($product_description_identifier)."', ";
                $query.= "product_abv_percent = '".$product_abv_percent."', ";
                $query.= "quantity = '".$product_quantity."', ";
                $query.= "uom = '".$mysqli->real_escape_string($uom)."', ";
                $query.= "base_price = '".$base_price."', ";
                $query.= "customer_discount = '".$customer_discount."', ";
                $query.= "promotional_discount = '".$promotional_discount."', ";
                $query.= "net_price_per_unit = '".$net_price_per_unit."', ";
                $query.= "line_value = '".$line_value."', ";
                $query.= "line_value_entered = '".$line_value_entered."', ";
                $query.= "vat_rate = '".$vat_rate_perecentage."' ";
        }

    if($query != "")
    {
        $result = $mysqli->multi_query($query) or die($mysqli->error); 
    }

    echo "End of process_spirits_tax_invoice_from_ubl_entries";
    $mysqli->close();

    $post_action = "../record.php";
    $message     = "Spirits Tax Invoice from UBL with document number $document_number recorded to database. ";

    echo'<form name="process_spirits_tax_invoice_from_ubl_entries" id="process_spirits_tax_invoice_from_ubl_entries" method="post" action="'.$post_action.'">
        <input type="hidden" name="message"          id="message"          value="'.$message.'" />
    </form>';

    echo'<script language="JavaScript" type="text/javascript">
        window.onload=function(){ window.document.process_spirits_tax_invoice_from_ubl_entries.submit(); }
        </script>';

    }
?>

Thank you inadvance.

[diafol]

You're using js to submit an invisible form which has nothing to send except $message. Is that right? This doesn't make any sense to me at all.

[Webville312]

Not really.
I am using PHP scripts.
I actually attempted to display some of the records, and they are being displayed correctly, i.e if I add two rows, then it displays the two records. The form is not empty, it contains content, and here is an example of the queries being executed when they are echoed;

INSERT INTO Beer_Tax_Invoice_From_UBL 
SET document_number = '1209', 
our_reference = 'Ref-128493', 
order_date = '2014-08-22 00:00', 
document_date = '2014-08-22 00:00', 
purchase_order_number = '1290', 
dispatch_date = '2014-08-22 00:00', 
invoice_branch_id = '1', 
invoice_address = 'AOT - Sample Town', 
account_number = '111335', 
payment_due_date = '2014-08-22 00:00', 
consignment_note_number = '', 
delivery_note_number = '', 
order_type = 'Duty Paid', 
tax_point_date = '2014-08-29 00:00', 
delivery_branch_id = '1', 
delivery_address = 'AOT - Sample Town', 
customer_vat_number = '10567Q', 
supplier_tin_number = '1000023775', 
supplier_vat_number = '11616K', 
payment_terms = '9 Days from Invoice Date', 
vehicle_number = '', 
haulier = 'Tibbett and Britten International,7062, ,, ,', 
order_created_by = 'clovis', 
dispatched_from = 'DHL', 
delivery_terms = 'Ex Works', 
line_values_calculated_total = '7827877.968854', 
line_values_entered_total = '7827881.968854', 
taxable_value = '7827881.968854', 
vat_rate = '18', 
tax_amount = '0', 
currency = 'UGX', 
total_due = '7827881.968854', 
number_of_line_entries = '2'
beer_tax_invoice_from_ubl_id: 0

INSERT INTO Beer_Tax_Invoice_From_UBL_Entry 
SET beer_tax_invoice_from_ubl_id = '0', 
product_number = 661314, 
product_description = 'Tusker', 
product_abv_percent = '4.2', 
quantity = '110', uom = 'CAS', 
base_price = '38228.33', 
customer_discount = '0.002', 
promotional_discount = '0', 
net_price_per_unit = '38227.5654334', 
line_value = '4205116.3', 
line_value_entered = '4205118.3', 
vat_rate = '18' 

[Webville312]

The $message is simply passing on a success message to the home page for display.

[diafol]

so you're using a js form sub just to redirect?

[Webville312]

Yes I am.

[Webville312]

After alot of huffing and puffing, I've gotten it to work. Looks like I was executing the SQL query outside the foreach loop.

[diafol]

So you didn't think to concatenate a query string in the loop and just run one query directly after exiting the loop?