2

I would like to know how can I insert an 'enum' type in a mysqli query. I mean, if the field type is string and I execute a query like this:

INSERT INTO 'table'(field1,field2) VALUES ('?,?');
$stmt->bind_param('ss',$value1,$value2);
$stmt->execute();`

Everything is ok, but if I change 'field2' to enum type :

CREATE TABLE IF NOT EXISTS table (
    field1 varchar(20) NOT NULL,
    field2 ENUM('Administrator', 'User', 'Guest'
);

Then this query inserts the first field but the second one is empty. I have checked that the second field value is one of the ENUM type defined.

Thank you.

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Charles
  • 282
  • 1
  • 4
  • 18
  • I can't actually answer because of incorrect duplications, plus I know you probably already figure out the answer, make sure there is no type-os in anything. I can't help much since this comment is more restricted then answering. – Jonathan J. Pecany Oct 06 '20 at 21:01

3 Answers3

3

You have defined the options for ENUM.

Also, you are inserting values into it.

As far as you enter pre-defined values, in your case:

('Administrator', 'User', 'Guest')

You can insert ENUM field as if it were a string, it will not create a problem.

If you enter any value other than the ones defined e.g. Administrator, ... the database will cause error.

Pupil
  • 23,834
  • 6
  • 44
  • 66
0

change

 INSERT INTO 'table'(field1,field2) VALUES ('?,?');

to

 INSERT INTO 'table'(field1,field2) VALUES (?,?);
Danyal Sandeelo
  • 12,196
  • 10
  • 47
  • 78
  • Errrrr https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql – RiggsFolly Oct 30 '18 at 15:55
  • @RiggsFolly I have seen that you have marked this answer as duplicate and I don't really see why it would be a duplicate. Could you explain how this question and that one is the same? – Jonathan J. Pecany Oct 06 '20 at 21:04
  • @JonathanJ.Pecany Already has an answer [see here](https://stackoverflow.com/a/27290373/2310830) But agree I may have been too quick on the draw as I guess I saw the invalid use of single quotes and assumed that was the error – RiggsFolly Oct 07 '20 at 10:43
  • @RiggsFolly we all jump to conclusions like that one point or another. – Jonathan J. Pecany Oct 07 '20 at 17:47
-2

I found the problem, here it goes:

I was saving special char in the enum type (accent)

field2 ENUM('Administrator', 'Usér', 'Guest');

and when my query was trying to insert Usér it fails. So I changed table definition to this:

field2 ENUM('Administrator', 'Usér', 'Guest'); and now everything is working fine.

Thank you for your responses and time.

Charles
  • 282
  • 1
  • 4
  • 18