Does this exist in mySQL?
Does this exist in mySQL?
Use the field type "enum" with two options being specified as 'y' and 'n'. So, in a sense, yes, the yes/no data type is supported.
But the values in an enum field don't HAVE to be 'y' and 'n'. It can be 't' & 'f' (true/false), or anything else your imagination takes you.
Hope this helps,
Although ENUM does exist, you are better off setting it to a TINYINT(1) UNSIGNED field, using 1=true/yes and 0=false/no. Smaller table size and (slightly) faster indexes.
ENUM data type: http://dev.mysql.com/doc/refman/5.0/en/enum.html
TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT data types: http://dev.mysql.com/doc/refman/5.0/...ric-types.html
and IF you use ENUM, don't use values like y or n or things like that, use 1 and 0 or true and false
Don't bother using ENUM, use tinyint as suggested. There are no real benefits from using ENUM and there are many flaws with it.
If you set up ENUM to use 'T' and 'F' for instance you can insert 'Scribble' if you want. It doesn't get stored as T/F or scribble, nor does it get stored as a NULL.
Thank for the replies, I've set it up as
`Garden` tinyint(1) NOT NULL COMMENT 'Option: YES/No',
`Garage` tinyint(1) NOT NULL COMMENT 'Option: YES/No',
`Driveway` tinyint(1) NOT NULL COMMENT 'Option: YES/No',
Which i assume is correct.
I then converted an access database to MySQL for one of my friends, and as he already had values in these 3 fields, it has come up using 1 and 0 instead of YES or No..does this matter? Obviously Yes/No would be preferred for when i echo these fields onto a php page.
I'm kind of new to MySQL and I'm used to using constraints for Yes/No data types
Last edited by ClammyDoo; 04-11-2007 at 08:43 PM.
Your table definition is a little off. You cannot have a NOT NULL value without setting the DEFAULT.
`Garden` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Option: YES/No',
`Garage` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Option: YES/No',
`Driveway` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Option: YES/No',
I set your DEFAULT = 1 because you have YES in uppercase. Set the DEFAULT = 0 for a NO value.
I am assuming that you are using phpMyAdmin, as it would have set your DEFAULT = '' rather than give you an error.
When converting MSAccess to MySQL, Access sets Yes/No = 1/0. TINYINTs will only store integers, not string values 'Yes' and 'No'.
As to your PHP echo, just use
IF(value == 0) echo 'No'
IF(value == 1) echo 'Yes'
*Not a PHP programmer, please use the correct syntax in your app.
I see what you mean, thanks.
The uppercase YES is only in the comment though so that doesn't affect anything.
Kinda annoying tinyints don't store string values
TINYINT = Tiny Integer, -64 to 64 or UNSIGNED (not negative), 0 to 127.
That's why it's called that. If you want to use 'Yes' and 'No', then you would be better off changing the fields to VARCHAR(3)
VARCHAR = Variable Character, length of 3.
Differences is that TINYINT can have an index, making searches faster, while VARCHAR(3) cannot, as indexes on fields must have a minimum length of 4.
Believe it or not, Access stores YES/NO as TINYINT(1) UNSIGNED, but the front-end client shows you YES/NO. It's just a display thing.
Not thinking. That's where I got it from. I had 127 as the max stuck in my head.
ENUM('Yes', 'No') is a better solution to this problem. MySQL itself uses ENUM('Y', 'N') internally. It's stored numerically internally and takes 1 byte total to store and index, just like TINYINT, and it lets you name the values like the OP seems to want. It's more obviously boolean so its meaning is more discoverable.