Cant insert

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Dave2000
Forum Contributor
Posts: 126
Joined: Wed Jun 21, 2006 1:48 pm

Cant insert

Post by Dave2000 »

Hi,

I have done something to my MySQL database :( I am no longer able to successfully execute INSERT queries.

I can do other queries fine - updade, select, delete all work fine - just not INSERT :?

The user has the privilege to do update. "ALL PRIVILEGES" is set..

I am using code that I know is right because I haven't changed it and it worked fine a few days ago. I have tried writing new code specifically to get any sort of update working...

Code: Select all

CREATE TABLE `test` (
`id` INT( 10 ) NOT NULL ,
`name` VARCHAR( 20 ) NOT NULL
)

Code: Select all

INSERT INTO `test` ( `id` , `name` )
VALUES (
'2', 'tt'
);
I also tried in phpmyadmin - nothing :(

Is there some kind of privilege I may be missing? Well, there must be, but I have no idea what... Help! Thanks in advance! :)
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

You're abusing the backticks `... Since you don't even have problematic table/column names you could easily do without them... And in SQL (what's so standard if every dbms has it's own dialect anway) string values have to be quoted...

Code: Select all

INSERT INTO test ( id, name ) VALUES ( 1 , 'xxx' );
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

timvw wrote:You're abusing the backticks `
Abusing the proper syntax...?


An in response to the question, it may be an issue if setting your INT column to the value of a string, but I believe that MySQL automatically converts the value that you give into an integer. I could be mistaken, however.
Dave2000
Forum Contributor
Posts: 126
Joined: Wed Jun 21, 2006 1:48 pm

Post by Dave2000 »

Sorry to waste everyone's time. I made a stupid mistake. The SQL statement I showed in my first post did insert the data - it was just that I had got so used to it not inserting that I didn't notice when it did insert. :oops:

What I was doing wrong...

My production server is MySQL 4.1.10. Inserting into an auto_incremented field can be done like

Code: Select all

INSERT INTO test (auto_increment_field, aaa) VALUES ('', 'hello');
but on my development server (MySQL 5.0.37) it only works if done like

Code: Select all

INSERT INTO test (auto_increment_field, aaa) VALUES (NULL, 'hello');
I dont know if this is an option that can be set or a change between versions...

Again, sorry to waste everyone's time.

Shears
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Shears wrote:My production server is MySQL 4.1.10. Inserting into an auto_incremented field can be done like

Code: Select all

INSERT INTO test (auto_increment_field, aaa) VALUES ('', 'hello');
but on my development server (MySQL 5.0.37) it only works if done like

Code: Select all

INSERT INTO test (auto_increment_field, aaa) VALUES (NULL, 'hello');
The whole point of the auto_increment field is so that you do not have to set it's value. Just remove it from the query and let it increment on it's own.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

superdezign wrote: Abusing the proper syntax...?
VALUES ( 1, `name` )

string values belong between quotes, not backticks...

Code: Select all

-- these days i prefer to be explicit as possible, but this one would work too...
INSERT INTO test ( aaa ) VALUES ( 'xxx' );
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

timvw wrote:string values belong between quotes, not backticks...
But nowhere in the example are backticks used to surround a string, only the table name and column names... :?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

superdezign wrote:
timvw wrote:string values belong between quotes, not backticks...
But nowhere in the example are backticks used to surround a string, only the table name and column names... :?
You're right (My silly underconscience tricked me into seeing things that weren't there...)
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

timvw wrote:(My silly underconscience tricked me into seeing things that weren't there...)
:lol:
Post Reply