madhatter301 Posted March 10, 2007 Share Posted March 10, 2007 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 Link to comment https://forums.phpfreaks.com/topic/42101-solved-active-row/ Share on other sites More sharing options...
AndyB Posted March 10, 2007 Share Posted March 10, 2007 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? Link to comment https://forums.phpfreaks.com/topic/42101-solved-active-row/#findComment-204220 Share on other sites More sharing options...
aniesh82 Posted March 10, 2007 Share Posted March 10, 2007 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 Link to comment https://forums.phpfreaks.com/topic/42101-solved-active-row/#findComment-204226 Share on other sites More sharing options...
madhatter301 Posted March 10, 2007 Author Share Posted March 10, 2007 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 Link to comment https://forums.phpfreaks.com/topic/42101-solved-active-row/#findComment-204454 Share on other sites More sharing options...
per1os Posted March 10, 2007 Share Posted March 10, 2007 Can we see the code? --FrosT Link to comment https://forums.phpfreaks.com/topic/42101-solved-active-row/#findComment-204455 Share on other sites More sharing options...
madhatter301 Posted March 10, 2007 Author Share Posted March 10, 2007 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 :-\ Link to comment https://forums.phpfreaks.com/topic/42101-solved-active-row/#findComment-204460 Share on other sites More sharing options...
per1os Posted March 10, 2007 Share Posted March 10, 2007 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 Link to comment https://forums.phpfreaks.com/topic/42101-solved-active-row/#findComment-204465 Share on other sites More sharing options...
madhatter301 Posted March 10, 2007 Author Share Posted March 10, 2007 Yea i never though of that lol Thanks, its pretty obvious that would be a much better way of doing it, cheers. Link to comment https://forums.phpfreaks.com/topic/42101-solved-active-row/#findComment-204469 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.