Re: [PHP-DB] Enum table entry
At 11:48 PM 10/18/02 -0500, Shiloh Madsen wrote:
For instance, a series of checkboxes with items such as abjuration,
conjuration, divination, and others, which will all have a numeric value
which gets plugged into the enum field. for instance, if a user selected
abjuration, and divination, it would be plugged into sql as 1, 3
Assuming your field is:
school enum( 'abjuration', 'conjuration', 'divination', ... ),
The database will return, 'abjuration,divination' in the example listed
above, and it will expect the same kind of string when setting the field
in an UPDATE or INSERT query.
(or however enum data is input into its column). That being the case how
do i utilize php to get this to work? what kind of form elements etc...
The problem im seeing with checkboxes are that they are discreet and dont
group together, so i cant get all the data to go into one column in mysql.
Hopefully i havent horribly confused the issue and some kind soul out
there can tell me how to send this data across. As a double nice
thing...how would you write it to pull the data back out...ie, convert 1,
3 to show abjuration, divination? Thanks for the help in advance.
To get the data in/out of the database you can do something like this:
Start with an array of possible choices, because you are going to
have to act on each possible choice.
$SchoolChoices = array( 'Abjuration', 'Conjuration', 'Divination', ... );
To setup the variables from the table for display. Note the value
from the database is in the string $School.
reset( $SchoolChoices );
while( list( , $Choice ) = each( $SchoolChoices )) {
$VarName = 'School' . $Choice;
$$VarName = ereg( $Choice, $School ) ? 'CHECKED' : '';
}
Now you can send the form displayed below with the values from
the database.
">
">
">
...
===
After the user enters the form, you can decode the fields and put the
data into a string for storage in the database with:
reset( $SchoolChoices );
$School = '';
while( list( , $Choice ) = each( $SchoolChoices )) {
if( 'on' == $_get( "School$Choice" )) {
$School .= ',' . $Choice;
}
}
$School = substr( $School, 1 );
Now you can INSERT/UPDATE the database with $School to set the enum field.
You can create the checkbox fields from $SchoolChoices with the following:
reset( $SchoolChoices );
while( list( , $Choice ) = each( $SchoolChoices )) {
$VarName = 'School' . $Choice;
echo "";
}
For extra credit, figure out how you can create the $SchoolChoices array
from the output of the following query:
DESCRIBE TableName School;
(Yes you can send this to mysql_query, and get the possible values of the
enum.)
Rick
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Enum table entry
Have a question that im trying to figure out how to resolve. I have a field type in mysql that is of the enum type. Unless youre familiar with Dungeons and Dragons, you wont get what the values mean, but hopefully youll get the gist anyway. I have a column labelled school which holds an enum data type comprised of the values 1 through 40. From the website front end, where the data is being entered, i want to display, ideally a series of checkboxes, otherwise a list which would allow a user to select multiple items in that will translate into this enum field. For instance, a series of checkboxes with items such as abjuration, conjuration, divination, and others, which will all have a numeric value which gets plugged into the enum field. for instance, if a user selected abjuration, and divination, it would be plugged into sql as 1, 3 (or however enum data is input into its column). That being the case how do i utilize php to get this to work? what kind of form elements etc... The problem im seeing with checkboxes are that they are discreet and dont group together, so i cant get all the data to go into one column in mysql. Hopefully i havent horribly confused the issue and some kind soul out there can tell me how to send this data across. As a double nice thing...how would you write it to pull the data back out...ie, convert 1, 3 to show abjuration, divination? Thanks for the help in advance.
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
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
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"
> <[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
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"
,
<[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
> 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-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. thanks much for any input. jeff -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] enum !!
how do i store multiple data in one field?
i have a table like this
+---+---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+---+--+-+-+---+
| test | enum('a','b','c') | YES | | NULL| |
+---+---+--+-+-+---+
insert into test values('a','b','c'); doens't work. please help
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] ENUM type
Greetings, Cahyo! At 15.01.01, 18:47, you wrote: CSA> I don't know what is the benefit if we use ENUM type. CSA> can everybody explain it? It is an ugly crutch. Well, you see, most RDBMSes will allow you to make a 'lookup table' or 'dictionary' or something (dunno its official name). Something like: CREATE TABLE foo_baz_dictionary ( baz integer not null primary key, baz_name char(50) ); -- baz is a potential "enum" field CREATE TABLE foo ( bar integer, ... baz integer, foreign key (baz) references foo_baz_dictionary ); Then when you insert something into 'foo', its 'baz' field is checked against foo_baz_dictionary (which is far more manageable than ENUM field definition). MySQL does not support foreign keys and so its creators came up with ENUM solution. -- Yours, Alexey V. Borzov, Webmaster of RDW -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] ENUM type
At 9:49 PM +0700 1/15/01, Cahyo S. Aji wrote: >I don't know what is the benefit if we use ENUM type. >can everybody explain it? Examples: It looks like a character column, but it's represented as an integer, so operations on it can be quite fast. The numeric representation also takes less space, in most cases. By using it, you make the legal values for a column explicit in the column definition. You can look up the legal values for the column from within programs. For example, you can parse SHOW COLUMNS LIKE 'col_name' output to get the legal enumeration values, then use them to construct a set of radio buttons or a popup menu in a web form. That way your form always presents exactly the legal options for the column. You can also use the column definition when validating submitted forms to make sure the value submitted is legal. -- Paul DuBois, [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] ENUM type
I don't know what is the benefit if we use ENUM type. can everybody explain it? _Ajik_ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
