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,
  UNIQUE KEY `permKey` (`permKey`)

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
5 Years
Discussion Span
Last Post by joshmac
Featured Replies
  • 1
    diafol 3,669   5 Years Ago

    OK think I spotted it - your variable is not a variable: [CODE]INSERT INTO `{dbprefix}permissions` (`permKey`, `permName`) VALUES ('delete_groups', 'Delete Groups');[/CODE] should be [CODE]INSERT INTO `{$dbprefix}permissions` (`permKey`, `permName`) VALUES ('delete_groups', 'Delete Groups');[/CODE] Read More


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

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


@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');

Edited by joshmac: n/a


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*


@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".


Use NULL for the incremeter is what I meant:

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

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


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


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


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');
Votes + Comments
I need new glasses...

@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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.