How to select value number of ENUM types in MySql?

Question

I need to select a row from table below, but the problem is the value in $row['city'] is the textual represent of the value, and i need its number(Toronto = 2). (Same as when we INSERT INTO, and we use value number instead of text)

Requests Table Structure:

req_id INT
uname  VARCHAR(30)
city   ENUM('New York', 'Toronto', 'Las Vegas')

Answer 1

You just need to force your city into a numeric context, from the fine manual:

If you retrieve an ENUM value in a numeric context, the column value's index is returned. For example, you can retrieve numeric values from an ENUM column like this:

mysql> SELECT enum_col+0 FROM tbl_name;

So you want this sort of thing:

select req_id, city+0
from your_table
where city = 'Toronto'

BTW, you can insert an enum using either the string or integer representation.

Answer 2

You can use the CAST function. The documentation doesn't mention this specific use case, but it works as expected. I prefer it because it looks elegant and clear:

SELECT CAST(city AS UNSIGNED) FROM your_table;