0

I have an MySQL database and a table table1 with columns:

  • id of uint type
  • some other, not important now fields (like name, age etc.),
  • status of enum type (met, like, love, hate, kill).

For the inserted row (1, love), i want to select the "next" status (hate).

My attempt:

I tried:

SELECT (status + 1) FROM table1 WHERE id = 1

but it retured 4 (index, not text value).

Of course:

SELECT status FROM table1 WHERE id = 1

returns love (text value, not index).

Question:

Is there an way to achive it (some kind of "casting" maybe)?

I preffer solution that do not mess with database schema tables.

Also, I could UPDATE that row with status = status + 1, than select it and after it UPDATE again with status = status - 1, but it's even worse idea...

Edit

As I have been asked to give some more details: I do not have any additional table that hold the order of my enum. I just added it with phpMyAdmin, and the selection from it works as I described. Moreover when I update the table with status = status + 1 i get hate from love.

PolGraphic
  • 3,233
  • 11
  • 51
  • 108

2 Answers2

1

That is no that easy as you might think. How do you manage the order of your enum? Via ID? Via an ordering column? At least you have to work with sub-queries or something like that. Could you give us some more detailed information?

Paul Facklam
  • 1,623
  • 12
  • 16
0

Something like this?

SELECT * FROM test 
WHERE status+0=(SELECT (status + 0)+1 FROM test   WHERE id = 1)

SQL Fiddle

Here is the next status

SELECT (status + 0)+1 FROM test WHERE id = 1
Mihai
  • 26,325
  • 7
  • 66
  • 81
  • Ok, but it works only if value "hate" has been insterted in the table. I have only one row which I mentioned. And I cannot assume there will be all possible values of enum type in my table every time. – PolGraphic Jan 25 '14 at 19:20
  • So whats the problem:you want to select the id and status even if its not there? – Mihai Jan 25 '14 at 19:21
  • Close. But only the status. Preciselly: what will be the *next* status of field with given id (but without updating it). – PolGraphic Jan 25 '14 at 19:24
  • But the "here is the next status" return number not value and it's the same like what I wrote in my post (section "I tried"). My problem is to convert that returned "4" to "hate". – PolGraphic Jan 25 '14 at 19:53
  • Create a separate table with the respective enum,populated with all possibilities and get the enum from there.I dont see other way. – Mihai Jan 25 '14 at 19:57
  • Kind of creepy way ;) When I would change the enum possible values, I would also had to change that extra table (that exists only to select next value of enum). Still thx for pointing some idea. – PolGraphic Jan 25 '14 at 20:02
  • Like this,is fairly easy if you dont have a gigantic enum http://sqlfiddle.com/#!2/daa30/2 – Mihai Jan 25 '14 at 20:04
  • 1
    OR,get in application code http://stackoverflow.com/questions/2350052/how-can-i-get-enum-possible-values-in-a-mysql-database – Mihai Jan 25 '14 at 20:05