Cant insert text with mysql insert query

Question

I have a table temp_inventory_cust with the fields customer_id [int(11)] and first_name [varchar(50)], as well as other fields. I'm trying to run this query by typing it into phyMyAdmin, just to make sure it works before coding it in php:

insert into temp_inventory_cust (`customer_id`,`first_name`) values (34,'fred');

It gives me: #1064 - 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 '\'fred\')' at line 1.

I'm not seeing the problem and am going crazy.

This query runs just fine:

insert into temp_inventory_cust (`customer_id`,`department_id`) values (34,2);

(both fields are int fields in this case.

Table structure

cust_id              int(11)     No 
first_name           varchar(50) No
last_name            varchar(50) No 
email_address        varchar(100)No 
phone_number         varchar(25) No 
department_id        int(11)     No 
position_id          int(11)     No 
end_date             date        No 
t_stamp              datetime    No

Any ideas?

The error can be that the column departmentId will be foreign key and as it is going null for your first query it will be returning error — Meherzad
– Meherzad, Commented Feb 20, 2013 at 7:26

Ankit Suhail · Accepted Answer · 2013-02-20 07:19:51Z

2

The syntax looks just fine.

I tried the query in Fiddle and the schema was built sucessfully.

Here is the code I used:

CREATE TABLE temp_inventory_cust(
customer_id int(11),
first_name varchar(50)
);

INSERT INTO temp_inventory_cust (customer_id,first_name) VALUES (1,'Ankit');

If you want to include the quotation marks then use the escape sequence () before using the quotation marks like

INSERT INTO temp_inventory_cust (customer_id,first_name) VALUES (1,'\'Ankit\'');

check here :

SqlFiddle

Update:

try using double quotes instead of single :

  INSERT INTO temp_inventory_cust (customer_id,first_name) VALUES (1,"Ankit");

edited Feb 20, 2013 at 7:19

answered Feb 20, 2013 at 7:11

Ankit Suhail

2,1051 gold badge22 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ankit Suhail Over a year ago

have you used the (;) at the end of creating a table and the query?

user2089988 Over a year ago

Thanks. I tried creating the table again with sql as you did and then ran the query you gave and it gave the same error. I also tried double quotes, and with and without ; at end.

Ankit Suhail Over a year ago

try inserting only the first_name only like INSERT INTO temp_inventory_cust (first_name) VALUES ("Ankit");

Yuvraj · Accepted Answer · 2013-02-20 06:50:02Z

0

it would be better if you can share ur table structure but my fist glance says 'issue must be in table first name filed type', its not accepting string. Cross check your filed type in database.

answered Feb 20, 2013 at 6:50

Yuvraj

688 bronze badges

3 Comments

user2089988 Over a year ago

cust_id int(11) No first_name varchar(50) No last_name varchar(50) No email_address varchar(100) No phone_number varchar(25) No department_id int(11) No position_id int(11) No end_date date No t_stamp datetime No No index defined!

user2089988 Over a year ago

sorry that's messy - not sure how to best show table structure

user2089988 Over a year ago

Field Type Null Default cust_id int(11) No first_name varchar(50) No last_name varchar(50) No email_address varchar(100) No phone_number varchar(25) No department_id int(11) No position_id int(11) No end_date date No t_stamp datetime No

Rohit R.K. · Accepted Answer · 2013-02-20 06:50:34Z

0

if you this query directly in phpmyadmin database then no problem

but if you use php code then you must remove " ' ", and query is:

 insert into temp_inventory_cust (customer_id,department_id) values (34,2);

answered Feb 20, 2013 at 6:50

Rohit R.K.

2972 gold badges4 silver badges14 bronze badges

1 Comment

user2089988 Over a year ago

I wish there were no problem, but I've been going crazy with this for hours. The one that trys to insert strings is the one that doesn't work, not the one with just numeric values.

Meherzad · Accepted Answer · 2013-02-20 07:15:56Z

0

As per your error you want to add ' in your string to escape ' you need to add \'

MySQL server version for the right syntax to use near '\'fred\')' at line 1.

Change query to

 insert into temp_inventory_cust (`customer_id`,`first_name`) values (34,'\'fred\'');

It will add quotes in the db also so record will be 'fred'

If you dont want to add quotes then use

insert into temp_inventory_cust (`customer_id`,`first_name`) values (34,'fred');

Refer http://dev.mysql.com/doc/refman/5.0/en/string-literals.html

edited Feb 20, 2013 at 7:15

answered Feb 20, 2013 at 7:03

Meherzad

8,5611 gold badge33 silver badges40 bronze badges

5 Comments

user2089988 Over a year ago

I didn't want to add 'fred' to the database, just the simple name itself: fred. I put single quotes in the values because it's a string, like one is supposed to do.

user2089988 Over a year ago

Your edited suggestion is exactly the query that I entered originally, which is my problem.

Meherzad Over a year ago

The error can be that the column departmentId will be foreign key and as it is going null for your first query it will be returning error

user2089988 Over a year ago

I made a simpler table just to eliminate things: table tt: a varchar(12) b varchar(12) insert into tt (a,b) values ('c','d') gives me the same error

Meherzad Over a year ago

@user2089988 Which version of mysql you are using ? Have you started facing this problem suddenly ?

Collectives™ on Stack Overflow

Cant insert text with mysql insert query

4 Answers 4

3 Comments

3 Comments

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

3 Comments

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related