Advanced MySQL query in Wordpress with JOIN

Question

I am new to MySQL and I am trying to write a quite advanced query. But hey! Learning by doing & Stackoverflow!

I could probably get all data in a less advanced query/queries and sort the data using PHP. But I figure it could be done directly in the query.

Below is my code. Please ask question if you don't understand and I will try to explain better. And please help me correct the code if you find any errors.

The code will be used to display different fields on my wordpress pages. Different fildes will have different categories eg. sidebar-blog, sidebar-page, highlight-blog, highlight-page. It will work almost like regular posts.

Here is the database structure of Wordpress:

• http://codex.wordpress.org/images/9/9e/WP3.0-ERD.png
• http://codex.wordpress.org/Database_Description

Questions:

How am I supose to join the tables: wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms AND wp_postmeta?

Is it good practice to make advanced queries or should you use PHP to handle if/else functions?

<?php

$id = $post->ID; // Gets the ID of current page

$query = "
SELECT wp_posts.post_content, wp_posts.ID, wp_terms.slug        # Data from two different tables

FROM wp_posts

# Cant figure out how to join the tables
INNER JOIN wp_postmeta
ON wp_posts.ID = wp_postmeta.post_id

INNER JOIN wp_term_relationships
ON wp_posts.ID = wp_term_relationships.object_id

INNER JOIN wp_term_taxonomy
ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id

INNER JOIN wp_terms
ON wp_term_taxonomy.term_id = wp_terms.term_id




WHERE
wp_posts.post_type = 'my-own-post-type'         # Only get specific post-type
AND
wp_posts.post_status = 'publish'                # Only get published posts


AND

# START - Only get categories specified here
(
wp_terms.slug = 'category-1'
OR
wp_terms.slug = 'category-2'
OR
wp_terms.slug = 'category-3'
OR
wp_terms.slug = 'category-4'
OR
wp_terms.slug = 'category-5'
)
# END - Only get categories specified here


AND

# I want to be able to include or exclude specific pages, even if category is the right one
# Exlude = Don't get data if current page ID is found (ID of current page will be checked in meta_value using %,$id,%)
# Include means: If include value is specifyed, then get data ONLY if ID is found (ID of current page will be checked in meta_value using %,$id,%)
# If not exclude and include are set, it should get the data

# START - Include exclude
(
# exclude is set, so check if page id match anywhere. If it IS found it it should not get the data, otherwise YES
wp_postmeta.meta_key = 'exclude' AND wp_postmeta.meta_value <> '%,$id,%'
OR
# include is set, so check if page id match anywhere. If it IS NOT found it it should not get the data, otherwise YES
wp_postmeta.meta_key = 'include' AND wp_postmeta.meta_value = '%,$id,%'
OR
# If exclude and include aren't set it should get the data
wp_postmeta.meta_key <> 'exkludera' AND wp_postmeta.meta_key <> 'include'
)
# END - Include exclude


";



$result = mysql_query($query);

while($row = mysql_fetch_array($result))
{

// collect all fields in category 1 (eg. sidebar)
if($row['slug'] == 'category-1'){
$all_fields_from_category_1 = $all_fields_from_category_1.'
<div id="field-sidebar">
'.$row['post_content'].'
</div>
';}

// collect all fields in category 1 (eg. highlight)
if($row['slug'] == 'category-2'){
$all_fields_from_category_2 = $all_fields_from_category_2.'
<div id="field-highlight">
'.$row['post_content'].'
</div>
';}



} // end while



// sidebar
echo '<div id="container-sidebar">'.
$all_fields_from_category_1.
'</div>';


// highlight
echo '<div id="container-highlight">'.
$all_fields_from_category_1.
'</div>';

?>

New version, new question:

SELECT DISTINCT wp_posts.post_content, wp_posts.ID, wp_terms.slug
FROM wp_posts
JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
JOIN wp_terms ON wp_term_taxonomy.term_id = wp_terms.term_id
WHERE wp_posts.post_type = 'my-own-fields'

AND wp_posts.post_status = 'publish'
AND wp_terms.slug IN
('field1', 'field2', 'field3', 'field4', 'field5', 'field6')
AND
(
wp_postmeta.meta_key = 'exlude' AND wp_postmeta.meta_value <> '$id'
OR wp_postmeta.meta_key = 'include' AND wp_postmeta.meta_value = '$id'
OR wp_postmeta.meta_key <> 'exlude' AND wp_postmeta.meta_value <> 'include' #LAST ROW
)

The "#LAST ROW" gives me back data even if exclude or include is set. Thats becauce it matches two other rows in the table wp_postmeta.

Example of table wp_postmeta:

meta_id     post_id     meta_key    meta_value
1           30          include     18
2           30          _edit_lock  1322225789:1
3           30          _edit_last  1

If meta_key inklude or exclude cant be found for the post_id I want to return the data...

Example senario when I want to return the data:

meta_id     post_id     meta_key    meta_value
2           30          _edit_lock  1322225789:1
3           30          _edit_last  1

Any idea of how I can solve this? A statement that say: If no rows with exclude or include was found for current id. Return the data.

More examples:

The post_id is 30.

If senario is:

meta_id     post_id     meta_key    meta_value
1           30          include     18
2           30          _edit_lock  1322225789:1
3           30          _edit_last  1

Then I want to get back wp_posts.post_content, wp_posts.ID and wp_terms.slug AS LONG AS $id IS 18.

If senario is:

meta_id     post_id     meta_key    meta_value
1           30          exclude     18
2           30          _edit_lock  1322225789:1
3           30          _edit_last  1

Then I want to get back wp_posts.post_content, wp_posts.ID and wp_terms.slug AS LONG AS $id IS NOT 18.

If senario is:

meta_id     post_id     meta_key    meta_value
2           30          _edit_lock  1322225789:1
3           30          _edit_last  1

Then I want to get back wp_posts.post_content, wp_posts.ID and wp_terms.slug.

Answer 1

Your joins are done correctly. When removing your comments and extra lines, you will see that the query is not really that complicated. The only real change I made was to change your list of OR for wp_terms.slug to an IN statement. This was only done to reduce code duplication, clean up the look, not to change functionality.

SELECT p.post_content, p.ID, t.slug
FROM wp_posts AS p
INNER JOIN wp_postmeta AS pm ON p.ID = pm.post_id
INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id
INNER JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
INNER JOIN wp_terms AS t ON tt.term_id = t.term_id
WHERE p.post_type = 'my-own-post-type'
   AND p.post_status = 'publish'
   AND t.slug IN
      ('category-1', 'category-2', 'category-3', 'category-4', 'category-5')
   AND
   (
      ( pm.meta_key = 'exclude' AND pm.meta_value <> '$id' )
         OR ( pm.meta_key = 'include' AND pm.meta_value = '$id' )
         OR ( pm.meta_key <> 'exclude' AND pm.meta_value <> 'include' )
   )