SQL question: Insert isn't ..workign

[KoihimeNakamura]

So I'm inserting the following string:

INSERT INTO scGUsers (userName, userPass, userEmail) 
VALUES ("KoihimeNakamura", "deleteme", "[email protected]");

into

CREATE TABLE IF NOT EXISTS `scGusers` (
  `userID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `userName` varchar(255) COLLATE utf8_bin NOT NULL,
  `userPass` varchar(255) COLLATE utf8_bin NOT NULL,
  `userEmail` text COLLATE utf8_bin,
  `userPriv` tinyint(3) unsigned DEFAULT '1',
  PRIMARY KEY (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

When I do so in phpMyAdmin, it tells me the row ID is 11, but there's no rows in it at all.

Typing in COMMIT didn't help either.

The message from the server is:
1 rows affected, 0 rows found, query time X.

 

[Denver]

Any chance it is a case-sensitvity problem, with scGusers in the create and scGUsers in the insert?

 

[Unabogie]

What happens if you try it like this?

INSERT INTO scGUsers (userName, userPass, userEmail, userPriv)
VALUES ("KoihimeNakamura", "deleteme", "[email protected]",1);

 

[El Greco]

Do you have more than one databases ?

 

[rjh01]

Do
select count(*)
from scGusers
Order by 1

both before and after the insert. Are there any secondary indexes? RI? What is the SQLCODE of the Insert statement?

 

[GlennB]

Long long time since I did any Sql, but does the AUTO_INCREMENT thingy for userID cope with the very first insert into the table?

 

[Spindrift]

I assume this is MySQL.

Are the single quotes okay in the CREATE TABLE?

I thought one used single quotes for column names in the INSERT.

 

[KoihimeNakamura]

What happens if you try it like this?

That works. I bet I need to specify default.

Do you have more than one databases ?

Yes, but I specify test in the connection string/use database line.

Long long time since I did any Sql, but does the AUTO_INCREMENT thingy for userID cope with the very first insert into the table?

yes

I assume this is MySQL.

Are the single quotes okay in the CREATE TABLE?

I thought one used single quotes for column names in the INSERT.

They apparently are now. Weird.

 

[GlennB]

That works. I bet I need to specify default.

I notice that in your INSERT statement you try to insert character "1" into a tinyint* column. Unabogie inserts numeric 1 and that works. This might well be irrelevant, but after many years retired from the field such problem-solving still intrigues me.

(*shows how long I've been away - never even heard of tinyint )

 

[KoihimeNakamura]

It shouldn't matter as I insert through prepared statements, which *should* not do that silly thing.

 

[kingstuart]

I've just run these on my computer, it works absolutely fine. It creates the tables and inserts the row just fine.

phpMyAdmin is useless. Is it possible you can use a different front end?