SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    359
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Working with a column of data type 'SET'

    I have a table with one column of data type 'SET'. It's easy enough to extract the settings in this column from any row. But I want to be able to compare those settings to the full list contained in the table structure.

    I need first to extract this full list of 'SET' options from the table structure.

    I have tried 'mysql_field_type' but it doesn't yield enough detail (I just get told it's a binary string of a certain length).

    I recognise that the 'SET' data type has many limitations, and perhaps this is one of them, but if anyone knows how to do this, I'd be grateful.

    I could type the full list into my PHP code as an array, but then I'd have to edit it if I changed the DB table, so I'm trying to avoid that.
    Tim Dawson
    Isle of Mull, Scotland

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    this is another great example of the shortcomings of the SET datatype

    i realize that adding another table to your design will require more code, but that's your best bet at this point
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    359
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your reply. Yes, I'm coming round to that view too !
    Tim Dawson
    Isle of Mull, Scotland

  4. #4
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Oooo, I did this. Gimme a mo.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  5. #5
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Here, this thread may help.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  6. #6
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    359
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you, Anthony. I took a look, and I think it just confirmed Rudy's view that I ought to be going for another table. But I'll bear it in mind when the real work starts.
    Tim Dawson
    Isle of Mull, Scotland

  7. #7
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Ha, yeah, I'd be inclined to agree.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  8. #8
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    359
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    In case anyone else stumbles on this thread, as the person who started it, I'd like to add a final comment.

    The SET data type is convenient but (as has been said before) it does have limitations. My advice to anyone who has to attempt any sort of editing, changing, manipulating of data stored as a SET is: don't try. Create a separate table.

    That's what I did, and all the limitations vanished. My thanks to those who pointed me in that direction.
    Tim Dawson
    Isle of Mull, Scotland


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
  •