SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jul 2001
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best way to store multiple boolean values in MySQL?

    I was just wondering what the best way to store multiple boolean values in a MySQL table would be... with reguards to searching. Would it be best for each to have it's own column in the table and throw this huge query at MySQL, or perhaps a single column with a value such as '01010111011' where 0 represents false, and 1 true ( of course ) and then query w/ wildcards where it doesnt matter what the value is, like "... where features like '1%%%101%%' "( <-- Is that even legal syntax? )? TIA for any info.

    Justin

  2. #2
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i'm not sure exactly how you'd search this, but when i want to store different on/off values in a numerical column i use a bitmask type thing (like your '01010111011' example). except when you do it like that you're making a lot bigger number than you need. how many values do you need to keep track of? you can go up to 32/64 values w/ my system.

    for example in a TINYINT (1 byte) column you can store 8 values (1 for each bit). you can toggle on the values you want by bitwise ORing your values together. (do you understand how the bitwise operators work?) like from PHP you would do:

    PHP Code:
    $options $val1 $val2 $val3 $val4 $val5 $val6 $val7 $val8
    and then INSERT the value of $options into the DB.

    then for searching i think you could look for the value by ANDing it with the options column, like this:

    Code:
    ...WHERE (options_col & option) = 1
    i think that would work...
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  3. #3
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ... OR you could probably use a SET column... lol


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •