RE: [PHP-DB] enum and bad planning
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
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: [EMAIL PROTECTED],
[EMAIL PROTECTED]
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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 01, 2002 3:24 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
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:
[EMAIL PROTECTED], [EMAIL PROTECTED]
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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] enum and bad planning
At 03:35 PM 10/1/02 -0400, John W. Holmes wrote: You should BENCHMARK the two solutions and see which is faster. Or use EXPLAIN to see if there is any difference. I have no doubt this will be slower. Oh well, he wanted to keep the table definitions. Don't forget to consider the time required for the programmer to change all the programs that already have been coded. Rick -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] enum and bad planning
At 03:35 PM 10/1/02 -0400, John W. Holmes wrote: You should BENCHMARK the two solutions and see which is faster. Or use EXPLAIN to see if there is any difference. I have no doubt this will be slower. Oh well, he wanted to keep the table definitions. Don't forget to consider the time required for the programmer to change all the programs that already have been coded. I meant the two different queries that still solve the problem with the columns set to Y or N. Someone gave one query with a bunch of Ifs and I gave a solution with length and replace, etc. See which one of those is faster. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
