PHP Wizard & MySQL Insert Error

[joshmac]

I have a php wizard for an application that does the MySQL insert but it comes back with an error message, and I can't seem to figure out what. Any help is greatly appreciated.

Insert Statement

CREATE TABLE `{dbprefix}permissions` (
  `ID` bigint(20) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `permKey` varchar(30) NOT NULL,
  `permName` varchar(30) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `permKey` (`permKey`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `{dbprefix}permissions` VALUES(00000000000000000001, 'delete_groups', 'Delete Groups');

MySQL Error Message:

MySQL: 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 `chm_permissions` VALUES(00000000000000000002, 'create_groups', 'Cre' at line 2

[diafol]

INSERT INTO `chm_permissions` VALUES(00000000000000000002, 'create_groups', 'Cre...

You haven't included that line for us to check.

[pritaeas]

Have you tried inserting without the key ? It is auto increment, no need to specify it.

[joshmac]

@pritaeas, I've tried it with and without it.

@ardav, sorry. Here is line 2

INSERT INTO `{dbprefix}permissions` VALUES(00000000000000000002, 'create_groups', 'Create Groups');

[stoopkid]

Pretty sure you need single quotes wrapped around all of your INSERT Values.

[joshmac]

@stoopkid, yes tried that but get the same error.

[diafol]

I can't see anything majorly wrong with this, other than the first value - why do you need all the leading 0s? The 0s are meaningless in an INT type field. Use NULL if it is an autoincrement? *I think*

[joshmac]

@ardav, I shouldn't need to manually. From what I understand from the docs, MySQL should pad it automatically with "unsigned zerofill" but taking out the zeros doesn't work as well. But maybe I will try just using int and see if it will work with "unsigned zerofill".

[diafol]

Use NULL for the incremeter is what I meant:

INSERT INTO `{dbprefix}permissions` VALUES(NULL, 'delete_groups', 'Delete Groups');

[joshmac]

Ah, I see. That seems like it should work. I will try that out and return with my finding.

[joshmac]

@ardav, unfortunately that doesn't work either. I also tried changing bigint to int and that doesn't work either.

[pritaeas]

INSERT INTO `{dbprefix}permissions` (`permKey`, `permName`) VALUES ('delete_groups', 'Delete Groups');

[joshmac]

ugh, so frustrating. That didn't work either. I might have to thing of an alternative solution of doing this.

[diafol]

OK think I spotted it - your variable is not a variable:

INSERT INTO `{dbprefix}permissions` (`permKey`, `permName`) VALUES ('delete_groups', 'Delete Groups');

should be

INSERT INTO `{$dbprefix}permissions` (`permKey`, `permName`) VALUES ('delete_groups', 'Delete Groups');

[joshmac]

@ardav, I wish that was the issue but {dbprefix} is not a variable. It is a shortcode that is read by the php wizard to replace the prefix with whatever the user enters on the installation screen. This works and the prefix is being created. All other inserts work, it is the inserts with long strings that don't work. and even when I try inserting without the ID as you and @pritaeas have mentioned. It still doesn't work.

I think it is one of those times where I need to just step back for a while and come back to it with fresh eyes. I am missing something, but I just can't put my finger on it.