• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4172
  • Last Modified:

enum

I am trying to set up and database to use the type ENUM and have '0,'1' and it keeps getting me an error when i try to save...any thoughs to why

Error

There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem

ERROR: Unclosed quote @ 88
STR: '
SQL: ALTER TABLE `pages` CHANGE `showing` `showing` ENUM(\'\'0\',\'1\'\') NOT NULL DEFAULT '1'


SQL query:

ALTER TABLE `pages` CHANGE `showing` `showing` ENUM(\'\'0\',\'1\'\') NOT NULL DEFAULT '1'

MySQL said: Documentation
#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 '\'\'0\',\'1\'\') NOT NULL DEFAULT '1'' at line 1
0
chefkeifer
Asked:
chefkeifer
  • 5
  • 4
1 Solution
 
Chris StanyonCommented:
You've got two single quotes before the values and 1 after them, so you're trying to insert ''0', ''1', which is why your got unclosed quote error.

Try the following

ALTER TABLE `pages` CHANGE `showing` `showing` ENUM('0','1') NOT NULL DEFAULT '1';

Open in new window

0
 
chefkeiferAuthor Commented:
i am actually typing single quotes..that is what is stumping me
0
 
Chris StanyonCommented:
Hmm. OK.

What exactly are you typing, and where are you typing it?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
chefkeiferAuthor Commented:
here is  a screen shot...
phpmyadmin.jpg
0
 
Chris StanyonCommented:
OK. Instead of clicking to edit a column, try entering the SQL ALTER statement directly.

In phpMyAdmin, while viewing your table structure, click on the SQL link (should be to the right of the STRUCTURE link, and paste in the following. Then click on the Go button.




ALTER TABLE `pages` CHANGE `showing` `showing` ENUM('0','1') NOT NULL DEFAULT '1';

Open in new window

0
 
chefkeiferAuthor Commented:
this is just weird...
i have copy and pasted the exact code you supplied and i got the error message in the image below..

for some reason its reading it different for some reason.


phpmyadmin.jpg
0
 
Chris StanyonCommented:
Ahh. This is happening because you have magic_quotes turned on in your php.ini, which magically adds a backslash (escapes) to various characters (the idea being that it's safer that way). It's either deprecated or removed completely in newer versions of PHP.

phpMyAdmin obviously uses PHP to manage the database, hence the addition of the backslash.

If you have access to your php.ini, you could try turning it off. Another alternative is to use a different app to manage your database. I use a program called SQLyog (free download from webyog.com), but not all hosts allow remote access to mySQL.

Other than that, I'm not sure how you're going to get around it. I don't know of another syntax for adding an ENUM field. Sorry :(


0
 
souquetbressandCommented:
Along the lines with the above, you can try MySQL GUI Tools.  This will solve the problem of magic_quotes.

http://dev.mysql.com/downloads/workbench/5.2.html
0
 
chefkeiferAuthor Commented:
i am checking with my hosting company to get me access to my php.ini i will fill you in when i here what they have to say..i might just have to make it a varchar and put the value of 1 or 0 in myself
0
 
Chris StanyonCommented:
Tinyint would probably be better.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now