RE: [PHP-DB] enum and bad planning

You should BENCHMARK the two solutions and see which is faster. Or use EXPLAIN to see if there is any difference. ---John Holmes... > -----Original Message----- > From: Jeffrey_N_Dyke@Keane.com [mailto:Jeffrey_N_Dyke@Keane.com] > Sent: Tuesday, October 01, 2002 3:24 PM > To: holmes072000@charter.net > Cc: php-db@lists.php.net > Subject: RE: [PHP-DB] enum and bad planning > > > Thanks for the responses. I got one earlier that did the trick. I wanted > to try as hard as i could from altering the table, as i was already using > the Y value in other reports...and here is a great solution. > > this may help someone else. so I wanted to post it. I'll use it again, > i'm > sure > > SELECT if( ENUM1 = 'y', 1, 0 ) + if( ENUM2 = 'Y', 1, 0 ) + if(ENUM3 = 'Y', > 1, 0 ) + if(ENUM4 = 'Y', 1, 0 ) + if(ENUM5 = 'Y', 1, 0 ) AS NumHits, > {other values to select} > > HAVING NumHits > 1 > > Thanks again Rick. > > > > > > > "John W. Holmes" > <holmes072000@ch To: > <Jeffrey_N_Dyke@Keane.com>, <php-db@lists.php.net> > arter.net> cc: > Subject: RE: [PHP-DB] enum > and bad planning > 10/01/2002 03:19 > PM > Please respond > to holmes072000 > > > > > > > > I have five enum fields, all Y/N. There is now(last minute) a > requirment > > to > > report on all clients that said Y to more then one of the > questions(its a > > survey). Had i known about this requirement, i could have made them > BIT > > and had a simple 1/0 option and summed the fields to check the > total.... > > > > select * from table where sum(ENUM1, ENUM2,ENUM3, ENUM4, ENUM5) > 1; > //or > > something like that... > > > > anyone have an idea of how i could accomplish the same with Y/N or > should > > i > > modify the tables and change them to 1/0's which is a doable option. > > Well, you could fix your "bad planning" by just altering your table. > > Create a new column, then use > > UPDATE your_table SET new_column = 0 where old_column = 'N'; > > Set it to one for 'Y', drop the old column, rename the new column, and > your done. Well, you'd have to do it for five columns, but it would > "fix" it at least. > > Or you could use something like this: > > SELECT * FROM your_table WHERE > LENGTH(REPLACE(CONCAT(enum1,enum2,enum3,enum4,enum5),'N','')) > 1) > > That'll join all the enums together, like 'YNYNN', replace all N with an > empty string, resulting in 'YY', and then make sure the length is more > than one, i.e. there are more than two 'Y' in the string. > > Hope that helps. > > ---John Holmes... > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > >