[SOLVED] Active Row?

[madhatter301]

Hi,

 

I have an sql table with various fields in, one of which is "Active" which is an enum 'y' or 'n'. Basically This table holds different events, of which only 1 may be active at one time. So my question is, is their an easier/better way of doing this kind of thing. At the moment I have to check all rows in the table to find which one has active set to 'y', then if i want to change it, set it to 'n' and set the new row i want active to 'y'. Any advice on this would be appreciated.

 

Thanks

[AndyB]

This looks more like a simple php/MySQL question, so I'll move it.

 

"I have to check all rows to find ... " - why not just use a SELECT statement with WHERE active='y' to show the active row?

[aniesh82]

Hello madhatter301,

 

You can select the desired row using the sql query posted by AndyB.

 

For updation:

 

To set the row to 'n' , you have to write two sql query:

 

1.  SELECT id from test WHERE `active` = 'y'

retrieve id_from from above statement

 

2  UPDATE `test` SET `active` = 'n' WHERE id=id_from

 

 

Regards

Aniesh Joseph

[madhatter301]

Thanks for your reply, what i actually meant was do you think its ok to be doing it like this, or is their an easier or better way to do it. Sorry for posting in the wrong place.

 

Thanks

[per1os]

Can we see the code?

 

--FrosT

[madhatter301]

Well I haven't actually implemented the code yet, I'd just like to know if this database setup is suitable, and if not how best to do it. Its just that i'm no good when it comes to DB design  :-\

[per1os]

Well in that case I would set the active field to be an int(1) and use 0 for not active and 1 for active. The enum would just be a waste of processing time.

 

If you want when you pull it out of the DB you can (I know this works in Oracle but not sure about MySQL) do this

 

SELECT DECODE(active, 0, 'n', 1, 'y') FROM table ...

 

 

--FrosT

[madhatter301]

Yea i never though of that  lol

Thanks, its pretty obvious that would be a much better way of doing it, cheers.