SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict nabeel's Avatar
    Join Date
    Nov 2002
    Location
    in westchester county, ny
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Extended fields query

    Hey everyone,

    I have a table, called pilots, with certain number of fields.
    Another table called fields, and then field values, where it's the fieldid, userid, and a value.

    So extended fields can be added, and a user can fill them out.

    I'm looking to optimize some code, right now I select a user, and then select all their field values (30 users = 1 query, + 30, one for each user). Yuck.

    I'm racking my brain, but I'm lost on how to do this, so it returns each "field" as a column, with the value. I'm looking through mysql docs.
    PHP Code:
    SELECT p.*, r.rankimage
    FROM phpvms_pilots p                     
    INNER JOIN phpvms_ranks r 
       ON r
    .rank=p.rank                     
    WHERE p
    .hub='KJFK'                     
    ORDER BY p.pilotid DESC 

    Then I am doing:

    PHP Code:
    SELECT v.value                     
    FROM phpvms_customfields f
    phpvms_fieldvalues v 
    WHERE f
    .fieldid=v.fieldid AND v.pilotid=
    Is my query now, since I'm joining other data, but I know what the column names are, so I can join stuff in. But in this case, the "column name" is variable. I'm sure there's a better way to do this

    Any ideas of what this is called, where to look?
    Hmm, maybe I can mess with Toad in the meantime... heh.

    Thanks!
    Nabeel

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    it sounds a lot like EAV

    make every effort not to use this model -- queries for even the simplest requests are horrendously complicated
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict nabeel's Avatar
    Join Date
    Nov 2002
    Location
    in westchester county, ny
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    D'oh, so I guess I'll have to live with the increasing number of queries? It's alot to tax on a host. Unless there is a better way?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the better way is to redesign your tables so that you don't store attributes and values

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict nabeel's Avatar
    Join Date
    Nov 2002
    Location
    in westchester county, ny
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    the better way is to redesign your tables so that you don't store attributes and values

    Well, how so? The attributes are changing by the client admin, they can be added/removed, changed, different attribute types, etc.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    in that case, you are up da creek... da "horrendously complex queries" creek

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict nabeel's Avatar
    Join Date
    Nov 2002
    Location
    in westchester county, ny
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    in that case, you are up da creek... da "horrendously complex queries" creek

    haha.. well I guess I'll look into some form of caching before touching eav.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    before touching EAV? you're already up to your, um, armpits in it...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •