E-Num not returning data value.

[Ninjakreborn]

Here is my problem. I started work on a system that was already built.  Decent structure. It was built in CodeIgniter, and for the most part is pretty clean. There are a few things that were setup in a funny way though.  One of them I am dealing with now, and trying to sort out.  I will try to be brief.

 

I have a database table called "tbl_fighter_categories".

Simple enough.  An ID, Name, and Status (activated or not).

 

Then he has the player field. But the strange thing, is he has the mapping field..for categories, setup with some enum field.

enum('1', '2', '3', '4', '5', '6', '7', '10')

 

So here is what I did.

1. Created a new "Category".  Which went fine.

2. Then updated the add/edit forms to show the new category (since he was hard coding them all instead of building them from the database, and I didn't want to rewrite that). 

3. Then I go to get the data in a view..and what do you know, I don't see the results from the one I added. All the other ID's are getting correctly..but just the new one I added, for some reason doesn't.  The final query that is used to try to retrieve these, is as follows.

 

SELECT tbl_fighter. * , tfc.name AS fcategory
FROM tbl_fighter
LEFT OUTER JOIN tbl_fighter_category tfc ON tfc.id = tbl_fighter.Category
ORDER BY tbl_fighter.Name
LIMIT 0 , 30

 

It gets the listing when I run it into phpmyadmin..and for all of the categories that were previously there, it shows fine. However for my new one (10) it shows the player, shows 10 as the category ID, but when it maps up to the name field (tfc.name) it's returning null.

Totally lost, and any help is appreciated.  Thanks.

[Ninjakreborn]

Anybody have any feedback here.  Strange issue I know, any feedback is appreciated

[Illusion]

Your result set is contradicting with your statement "1. Created a new "Category".  Which went fine."

 

Inserting 10 is different than '10' in a enum field. You are also stating that category 10 is displayed in result set which means the category is assigned properly to the player.

 

I couldn't see any problem. Using numbers in enum field is not a good pracatice though.

 

A review on changes you have made could be helpful.

[Ninjakreborn]

Let me be more specific.  There are two tables. One for Categories, and one for Players which maps to categories.

 

The Category SQL is as follows:

 

CREATE TABLE IF NOT EXISTS `tbl_fighter_category` (
  `id` int(10) NOT NULL auto_increment,
  `name` varchar(75) default NULL,
  `status` tinyint(1) default '1',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `tbl_fighter_category`
--

INSERT INTO `tbl_fighter_category` (`id`, `name`, `status`) VALUES
(0, 'ALL', 1),
(1, 'Heavy Weight', 1),
(2, 'Light Heavyweight', 1),
(3, 'Middle Weight', 1),
(4, 'Welter Weight', 1),
(5, 'Light Weight', 1),
(6, 'Feather Weight', 1),
(7, 'Bantam Weight', 1),
(10, 'Flyweight', 1);

 

Very simple. Just a list of the "Categories".  Then the players, has a field called Category which maps to that.  That specific field is E-Num (I didn't set it up

that way).  It is simply the ID that maps to the categories table.  So it looks like this:

 

Fieldname: Category
Type: enum('1', '2', '3', '4', '5', '6', '7', '10')
Collation: atin1_swedish_ci
Attributes:
Null: No
Default: 1

 

So that is suppose to map the players to the categories table. Should be pretty straightforward.  Then when I run the query I mentioned above

it shows proper mapping for all "Categories" but for the one I added (10) it doesn't map the name correctly and shows Null.  Make sense?

 

[Illusion]

yeah , makes no sense.

 

http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/

 

There were issues with mysql enum field in many of previous releases.

 

Try casting both the fields to int and if the result is still same ...try in a newer version .. if it is still same ...you need to log the bug.

 

 

[DavidAM]

The problem is that ENUMs in MySQL are inherently strings.  Consider the following example:

 

CREATE TABLE Things (
  ThingID INT UNSIGNED AUTO_INCREMENT,
  Category ENUM ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'J')
);

CREATE TABLE Categories (
  CatID INT UNSIGNED AUTO_INCREMENT
  CatName VARCHAR(50)
);

 

Now, when you select from the Things table, you will see the Category column as those letters. However, if you use the Category column in an integer expression, you will get the integer value of the ENUM.  So,

 

SELECT ThingID, Category FROM Things

-- Might Return
ThingID  Category
     21  A
     22  B
     23  C
     24  D
     25  E
     26  F
     27  G
     28  J

-- However
SELECT ThingID, Category + 0 AS Cat FROM Things

-- Would Return
ThingID  Cat
     21  1
     22  2
     23  3
     24  4
     25  5
     26  6
     27  7
     28  8

 

Now, applying that to your situation. When you JOIN the tbl_fighter.Category to tbl_fighter_category.id the server has to use the integer value of the enum since the category table's ID is an integer. So your (string value of) "10" is actually ENUM value (integer) 8 and does not match the (integer) value 10 in the category table.  Note that the server is NOT converting the enum string value (in your case "10") to an integer (which would be ...  uhh ... 10). It IS using the ENUMERATED value, which in this case is 8.

 

 

This is very confusing, and is the best reason NOT to use numbers for ENUM values. If you MUST stick with the design, use the next logical digit in the sequence (i.e. '8') instead of '10' -- or add the empty inactive categories of '8' and '9'.

 

[Ninjakreborn]

You sir, rock.  Thanks.  That fixed it. I lined it as 8 and it works. I will change the design structure eventually but under a different quote. Thanks again.