Does anyone have any ideas why im getting this Syntax error in my query?
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO customers ( name, email, address_1, address_' at line 22
INSERT INTO invoices
(
invoice,
invoice_date,
due_date,
subtotal,
shipping,
discount,
vat,
total
)
VALUES (
'AMBMN0001',
'14/05/2015',
'20/05/2015',
'985.45',
'23',
'90.55',
'197.09',
'1008.45'
);
INSERT INTO customers
(
name,
email,
address_1,
address_2,
town,
county,
postcode,
phone,
name_ship,
address_1_ship,
address_2_ship,
town_ship,
county_ship,
postcode_ship
)
VALUES (
'James Brandon',
'[email protected]',
'5 Some Road',
'Town',
'County',
'Other',
'POSTCODE',
'0748646013845',
'James Brandon',
'5 Some Road',
'Town',
'County',
'Other',
'POSTCODE'
);
INSERT INTO invoice_items
(
invoice,
product,
qty,
price,
discount,
subtotal
)
VALUES (
'AMBMN0001',
'Versa Table - Wildberry',
'3',
'200',
'10%',
'540.00'
);
INSERT INTO invoice_items
(
invoice,
product,
qty,
price,
discount,
subtotal
)
VALUES (
'AMBMN0001',
'Versa Table - Aubergine',
'1',
'178',
'30.55',
'147.45'
);
INSERT INTO invoice_items
(
invoice,
product,
qty,
price,
discount,
subtotal
)
VALUES (
'AMBMN0001',
'Versa Table - Blue Jazz',
'2',
'149',
'',
'298.00'
);
PHP
// Create invoice
if ($action == 'create_invoice'){
// invoice customer information
// billing
$customer_name = $_POST['customer_name']; // customer name
$customer_email = $_POST['customer_email']; // customer email
$customer_address_1 = $_POST['customer_address_1']; // customer address
$customer_address_2 = $_POST['customer_address_2']; // customer address
$customer_town = $_POST['customer_town']; // customer town
$customer_county = $_POST['customer_county']; // customer county
$customer_postcode = $_POST['customer_postcode']; // customer postcode
$customer_phone = $_POST['customer_phone']; // customer phone number
//shipping
$customer_name_ship = $_POST['customer_name_ship']; // customer name (shipping)
$customer_address_1_ship = $_POST['customer_address_1_ship']; // customer address (shipping)
$customer_address_2_ship = $_POST['customer_address_2_ship']; // customer address (shipping)
$customer_town_ship = $_POST['customer_town_ship']; // customer town (shipping)
$customer_county_ship = $_POST['customer_county_ship']; // customer county (shipping)
$customer_postcode_ship = $_POST['customer_postcode_ship']; // customer postcode (shipping)
// invoice details
$invoice = $_POST['invoice_id']; // invoice number
$invoice_date = $_POST['invoice_date']; // invoice date
$invoice_due_date = $_POST['invoice_due_date']; // invoice due date
$invoice_subtotal = $_POST['invoice_subtotal']; // invoice sub-total
$invoice_shipping = $_POST['invoice_shipping']; // invoice shipping amount
$invoice_discount = $_POST['invoice_discount']; // invoice discount
$invoice_vat = $_POST['invoice_vat']; // invoice vat
$invoice_total = $_POST['invoice_total']; // invoice total
// insert invoice into database
$query = "INSERT INTO invoices (
invoice,
invoice_date,
invoice_due_date,
subtotal,
shipping,
discount,
vat,
total
) VALUES (
'".$invoice."',
'".$invoice_date."',
'".$invoice_due_date."',
'".$invoice_subtotal."',
'".$invoice_shipping."',
'".$invoice_discount."',
'".$invoice_vat."',
'".$invoice_total."'
);
";
// insert customer details into database
$query .= "INSERT INTO customers (
invoice,
name,
email,
address_1,
address_2,
town,
county,
postcode,
phone,
name_ship,
address_1_ship,
address_2_ship,
town_ship,
county_ship,
postcode_ship
) VALUES (
'".$invoice."',
'".$customer_name."',
'".$customer_email."',
'".$customer_address_1."',
'".$customer_address_2."',
'".$customer_town."',
'".$customer_county."',
'".$customer_postcode."',
'".$customer_phone."',
'".$customer_name_ship."',
'".$customer_address_1_ship."',
'".$customer_address_2_ship."',
'".$customer_town_ship."',
'".$customer_county_ship."',
'".$customer_postcode_ship."'
);
";
// invoice product items
foreach($_POST['invoice_product'] as $key => $value) {
$item_product = $value;
// $item_description = $_POST['invoice_product_desc'][$key];
$item_qty = $_POST['invoice_product_qty'][$key];
$item_price = $_POST['invoice_product_price'][$key];
$item_discount = $_POST['invoice_product_discount'][$key];
$item_subtotal = $_POST['invoice_product_sub'][$key];
// insert invoice items into database
$query .= "INSERT INTO invoice_items (
invoice,
product,
qty,
price,
discount,
subtotal
) VALUES (
'".$invoice."',
'".$item_product."',
'".$item_qty."',
'".$item_price."',
'".$item_discount."',
'".$item_subtotal."'
);
";
}
header('Content-Type: application/json');
// execute the query
if($mysqli -> query($query)){
//if saving success
echo json_encode(array(
'status' => 'Success',
'message' => 'Invoice has been created successfully!'
));
} else {
// if unable to create invoice
echo json_encode(array(
'status' => 'Error',
//'message' => 'There has been an error, please try again.'
// debug
'message' => 'There has been an error, please try again.<pre>'.$mysqli->error.'</pre><pre>'.$query.'</pre>'
));
}
//close database connection
$mysqli->close();
}
;) after each insert statement