SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Joining data on fields where one includes an html character and one doesn't

    I'm looking to join three tables. One of the criteria is to join the title of a page name to a table of categories.

    The problem is "wp_posts.post_title" is pure data. For this example, wp_posts.post_title is "John Smith & Gary Anderson". What I am trying to join wp_posts on is the post_title to "wp_terms.name", and wp_terms.name contains data that has been input using htmlentities. For this example, wp_terms.name is "John Smith & Gary Anderson".

    How would it be possible to join wp_posts using wp_posts.post_title=wp_terms.name, where they are both so similar, but special characters f/html are throwing it off.

    My query now is:

    SELECT wp_posts.post_title, wp_posts.guid, wp_terms.name
    FROM wp_terms
    INNER JOIN wp_term_taxonomy ON wp_term_taxonomy.term_id = wp_terms.term_id
    AND wp_term_taxonomy.parent = '247'
    INNER JOIN wp_posts ON wp_posts.post_title=wp_terms.name AND wp_posts.post_parent = '92'
    AND wp_posts.post_status = 'publish'

    I bolded the culprit as to what's throwing the query off.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    ON wp_posts.post_title = REPLACE(wp_terms.name,'&','&')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    ON wp_posts.post_title = REPLACE(wp_terms.name,'&','&')
    Works perfectly, and I really appreciate it. Thank you!

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Function on a join condition, good luck with performance then again… its word press anyway…
    The only code I hate more than my own is everyone else's.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    it is better to get the right answer eventually than the wrong answer with speed

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

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Not any fault of your own word press is a pile of sh*t. Then again this begs the question why the data is being stored encoded in the first place, probably a word press thing…
    The only code I hate more than my own is everyone else's.


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
  •