jlipinski3 — 2011-08-31T12:56:56-04:00 — #1
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
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.
oddz — 2011-08-31T21:47:17-04:00 — #2
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…
r937 — 2011-08-31T15:54:08-04:00 — #3
ON wp_posts.post_title = REPLACE(wp_terms.name,'&','&')
jlipinski3 — 2011-08-31T16:44:14-04:00 — #4
Works perfectly, and I really appreciate it. Thank you!
oddz — 2011-08-31T17:32:20-04:00 — #5
Function on a join condition, good luck with performance then again… its word press anyway…
r937 — 2011-08-31T17:42:56-04:00 — #6
it is better to get the right answer eventually than the wrong answer with speed