MySQL INSERT Syntax error and unsure why

Question

im getting the following error with my query from PHP:

Does anyone have any ideas on why? its passing an email address and from error looks like its breaking there but i might be wrong.

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 SET name = "James Brandon", email = "james@ambient' at line 10

PHP

$query .= "INSERT INTO customers SET
                name = '".$customer_name."',
                email = '".$customer_email."',
                address_1 = '".$customer_address_1."',
                address_2 = '".$customer_address_2."',
                town = '".$customer_town."',
                county = '".$customer_county."',
                postcode = '".$customer_postcode."',
                phone = '".$customer_phone."',

                name_ship = '".$customer_name_ship."',
                address_1_ship = '".$customer_address_1_ship."',
                address_2_ship = '".$customer_address_2_ship."',
                town_ship = '".$customer_town_ship."',
                county_ship = '".$customer_county_ship."',
                postcode_ship = '".$customer_postcode_ship."';
            ";

you have written wrong syntax for insert -- INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...) — sanir ranta, May 07 '15 at 07:48
And please, please, please, stop throwing out all this code vulnerable to [SQL Injections](http://en.wikipedia.org/wiki/SQL_injection). Have a look at [PDO](http://php.net/manual/en/book.pdo.php) which is so nice and simple to use, your code will even be shorter and cleaner! — Eggplant, May 07 '15 at 07:50
@sanirranta this is not the wrong syntax it is MySQL's extension. reference http://stackoverflow.com/questions/861722/mysql-insert-into-table-values-vs-insert-into-table-set — Noman, May 07 '15 at 07:58
Did you see @James that there is a double quote at the error message in the email? — Preeti Maurya, May 07 '15 at 08:05
I did not, however i rewrote and getting a syntax error after the email now here: https://shrib.com/SZyBXpxN (any ideas?) — James, May 07 '15 at 08:45

score 2 · Accepted Answer · answered May 07 '15 at 07:53

Two possible syntax for INSERT statement. In the first following case, you specify only col you want to fill.

INSERT INTO TABLE (COL1, COL2, COL3)
VALUES (VAL_COL1, VAL_COL2, VAL_COL3);

You can also INSERT without providing col_name but you will have to specify value of all columns and in the good order.

The first opton is better in my opinion and will avoid you many mistakes especially when you have a lot of different column in your table.

score 1 · Answer 2 · answered May 07 '15 at 07:52

According to this reference, you should try something like this:

INSERT INTO customers (name , email, address_1, ...)
VALUES (value1, value2, value3,...)

Edit: As mentioned in the comments to your answer, please consider using PDO. It is much safer and in my opinion even easier to handle.

score 0 · Answer 3 · answered May 07 '15 at 07:58

0

@James Since you are doing INSERT command. Do as the following.

$query = "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('".$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."')";

Thanks & Regards,

Vivek

answered May 07 '15 at 07:58

Vivek Keviv

496
4
19

Thanks, seems im getting a syntax issue with date and / any ideas? https://shrib.com/SZyBXpxN – James May 07 '15 at 09:06
can you please post little eloborate PHP script & Error message which will be useful to help you. And also given URL link is not working. – Vivek Keviv May 07 '15 at 09:15
As per I reviewed your code. I'm not getting any syntax errors. Only thing we need to consider is whether this foreach and post variables are inside this if condition. `if (isset($_POST['invoice_product'])) { //validates whether form is submitted or not. foreach ($_POST['invoice_product'] as $key => $value) { // invoice product items //your code goes here. } }` – Vivek Keviv May 07 '15 at 15:08

score 0 · Answer 4 · edited May 07 '15 at 08:11

Please note that you don't need to append that

; in between your mysql query

your modified query will be

 $query =      "INSERT INTO customers SET name = '".$customer_name."',

                  email = '".$customer_email."',

                  address_1 = '".$customer_address_1."',

                  address_2 = '".$customer_address_2."',

                  town = '".$customer_town."',

                  county = '".$customer_county."',

                  postcode = '".$customer_postcode."',

                  phone = '".$customer_phone."',

                  name_ship = '".$customer_name_ship."',

                  address_1_ship = '".$customer_address_1_ship."',

                  address_2_ship = '".$customer_address_2_ship."',

                  town_ship = '".$customer_town_ship."',

                  county_ship = '".$customer_county_ship."',

                  postcode_ship = '".$customer_postcode_ship."'
            ";

Please note there is a

semi-colon after $customer_postcode_ship and no need of the . in $query

MySQL INSERT Syntax error and unsure why

4 Answers4