PDO cannot compare mysql ENUM using integers in prepared statements

Question

I am using PDO and prepared statements, but i cannot seem to get any results when comparing an ENUM field with an integer.

Example:

$db = new PDO('mysql:host=localhost;dbname=****', '***', '***');
$s = $db->prepare('SELECT id FROM t2 WHERE lang = ?');

$s->execute(array('en')); // Works              
print_r($s->fetchAll());

$s->execute(array(2)); // Does not work            
print_r($s->fetchAll());

I Am testing against this table:

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (
  id int(10) NOT NULL AUTO_INCREMENT,
  lang enum('no','en','fr') NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
INSERT INTO  t2 (id, lang) VALUES (NULL , 'en');

Any idea on how to get this to work?

I am converting to PDO, and I'd prefer not to rewrite all constants, enums, and queries in my app :(

Answer 1

2 is not a valid ENUM element. It's as simple as that.

The reason it works in raw MySQL when you do lang = 2, is that it's exposing the underlying storage mechanism for the list (basically it's just a normalized value, but the normalization is hid from you by the ENUM column).

I'd suggest not trying to do this. MySQL hides the implementation for a reason. Realize that using 2 for the comparison is nothing more than a magic number...

Answer 2

language shouldn't be an enum, infact don't use enums at all instead use separate lookup/type tables:

create table languages
(
  lang_id tinyint unsigned not null auto_increment primary key,
  name varchar(255) unique not null
)
engine=innodb;

create table customers
(
  cust_id int unsigned not null auto_increment primary key,
  lang_id tinyint unsigned not null,
  foreign key (lang_id) references languages(lang_id)
)
engine=innodb;

you should also be aware that adding a new value to the ENUM definition will require MySQL to rebuild the entire table – less than optimal for large tables !!