SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard wdmny's Avatar
    Join Date
    Jul 2000
    Location
    Here
    Posts
    1,010
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    JOINing DB Columns and Custom Field Values

    This question is more of a "is it possible to.." or "what is the best way to do this.." situation, as I do know of a way to do it in code rather than SQL.

    Here is an easy to follow example:
    Lets say I have an content management program where my database has the fields Title, Description, and Body. I allow users to expand their content by creating their own fields, but I do not let them update the actual table layout. I instead use two tables, one for CustomFieldNames and one for CustomFieldValues.

    Articles
    [A_ID], [A_Title], [A_Desc], [A_Body]
    1, 'Test', '', 'test test test'

    CustomFieldNames
    [CF_ID], [CF_Name]
    1, 'Author'

    CustomFieldValues
    [CustomFieldID], [ArticleID], [CustomValue]
    1, 1, 'Me'

    My situation is, how can I best pull all the data together when SELECTing a list of article values? For instance, if I want to show the Title and Author in my article listings. If I do a JOIN, I do not have FieldNames tied to FieldValues, just to ID, which is an unknown.

    I could do this in code by loading all the needed field values into a dictionary array, then requesting the related data to each Article record. Ex:
    Code:
    Do While Not objArticlesRS.EOF
        intThisID = objArticlesRS("A_ID")
        strThisTitle = objArticlesRS("A_Title")
        arrThisValues = arrValues.Item(intThisID)
        strThisAuthor = arrThisValues.Item("Author")
    
        objArticlesRS.MoveNext
    Loop
    Note: Ignore the fact that I have hard coded the Author variable and could just add an author column . In one case I would be putting all this into XML and using the FieldName as the name of the XML field, therefore I would not be hard coding names like that.

    Whew! If you need any clarification, ask me something more specific and I'll fill you in. DB Gurus, what do you think?

  2. #2
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    try this one:

    Code:
    SELECT A.*, N.CF_Name, V.CustomValue
    FROM Articles AS A
      LEFT JOIN CustomFieldValues AS V
      ON V.ArticleID = A.A_ID 
      INNER JOIN CustomFieldNames AS N
      ON N.CF_ID = V.CustomFieldID
    WHERE A.A_ID = 1;
    This will get you one row per custom field. If you want to put all custom attributes in one row I would guess that you would have to solve that by code.
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  3. #3
    SitePoint Wizard wdmny's Avatar
    Join Date
    Jul 2000
    Location
    Here
    Posts
    1,010
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kleineme
    Hi,
    If you want to put all custom attributes in one row I would guess that you would have to solve that by code.
    Yes, that's what I am referring to. I wasn't sure if it was possible or not.

    Example Listing (from sample case above):
    [A_ID], [A_Title], [CF_Author]
    1, Test, Me


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
  •