Solved

Wordpress WHERE Category ID equals

Posted on 2011-09-21
6
607 Views
Last Modified: 2012-05-12
I am trying to display my wordpress blog posts in another area of my website that is not wordpress. So I am just connecting to the database directly.

I have done the below query:

$query_rs_cwes_wp = "SELECT post_title FROM wp_posts WHERE post_status = 'publish' AND post_type = 'post' ORDER BY post_date DESC";

Which works, but I need to only show posts in category id 4 which has a name of "blogs".

I can't see a column in the "wp_posts" table that links to the category name or id. Can you please help?
0
Comment
Question by:petewinter
  • 4
  • 2
6 Comments
 

Author Comment

by:petewinter
ID: 36574256
Looking into the wordpress database structure it looks like the "wp_posts" table joins to the "wp_term_relationships" table from the "post_parent" column to "object_id" column, then the "term_taxonomy_id" column shows the category id.

I assume that I need to join these tables, then say where the "term_taxonomy_id" equals '4'

Please can you help as my php sql knowledge is not good enough.
0
 

Author Comment

by:petewinter
ID: 36574571
I have attempted to write the sql, but it's not showing any rows so I must have done something wrong...

$query_rs_cwes_wp = "SELECT ter.*, wpp.post_title
                               FROM wp_term_relationships ter
                               JOIN wp_posts wpp
                               ON ter.object_id = wpp.post_parent
                               WHERE ter.term_taxonomy_id = '4'
                               AND wpp.post_status = 'publish'
                               AND wpp.post_type = 'post'
                               ORDER BY post_date DESC";

Please help
0
 
LVL 23

Expert Comment

by:jeremyjared74
ID: 36574927
Here is how you would do it:

1. Open Text Editor

Place this code in the very top of your blank text doc so WordPress recognizes it as a template file:
<?php
/*
Template Name: Blogs Posts
*/
?>

Open in new window


2. Name The Template

Save the file as blogs-posts.php

3. Replicate Current Layout

Open your index.php file, or any template file that has complete code.
NOTE:
Some themes use a loop.php file, if your theme does you might need to use the page.php file. Your going to copy and paste the code from that template file into the blogs-posts.php you just created (just below the custom template code you added earlier). The reason you want a complete template and not was that uses get_template_part is because it's easier to get the complete div structure so the look matches the other pages.
Once you've decided what template to copy, paste it under the new template.

4. Replace The Current Loop

Now remove everything except for the <div> structure.
You should now have a templage files that looks something like this (all loop code is gone):
<?php
/*
Template Name: Blogs Posts
*/
?>

<?php get_header(); ?>
<div id="left-column">
  <div class="panel">



    <?php endwhile; ?>
  </div>
</div>
  <?php get_sidebar(); ?>
<?php get_footer(); ?>

Open in new window


5. Add New Code

Now you'll add the code that calls the posts for your specific category. This is the code that makes it work:
<?php $wpc_post_array = array( 'cat' => 40, 'posts_per_page' => -1, ); ?>
  <?php query_posts( $wpc_post_array ); ?>

Open in new window

NOTE:
This usually goes just after the first div in your theme. It might vary depending on your layout. Once you're finished you'll notice if something is our of order with the div's. The rest I'll just post the code you need and you can figure out the div and css layout.

That alone won't display the posts, it just defines the custom function/variable. Now you'll need to complete it with the loop stuff.

6. The Complete File

Here is the complete code for my sample:
<?php
/*
Template Name: Blogs Posts
*/
?>

<?php get_header(); ?>
<div id="left-column">
  <div class="panel">
  <?php $wpc_post_array = array( 'cat' => 40, 'posts_per_page' => -1, ); ?>
  <?php query_posts( $wpc_post_array ); ?>
    <?php while (have_posts()) : the_post(); ?>
      <div class="custom-cat">
        <h2><a href="<?php the_permalink() ?>"><?php the_title(); ?></a></h2>
        <?php the_content(''); ?>
      </div>
    <?php endwhile; ?>
  </div>
</div>
  <?php get_sidebar(); ?>
<?php get_footer(); ?>

Open in new window


Save the file after correcting the div's and upload it to your themes root folder.

7

Adding A Page To Hold The PostsNow go to your WordPress Admin Panel and add a new page named (Blogs, or whatever it doesn't really matter). You don't need to add any content, just change the template used to the new one by clicking the drop-down list on the lower right of the page editor screen. Save the page and you should now have the posts of only the "Blogs" category.

NOTE:
In the code above, notice where it says:
'cat' => 40, 'posts_per_page' => -1,

Open in new window

You'll need to use your Category ID where I have 40, and the -1 is to display all posts in that category. If you only wanted to show 5, it would be:
'cat' => 40, 'posts_per_page' =>5,

Open in new window


I hope that helps.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:petewinter
ID: 36580362
jeremyjared74: Thanks for your email, but I want to display post blogs outside of the wordpress environment. i.e. I creating page else where, but want the pull in my blogs titles and link to my blog. So it dynamically updates.

Any idea's how I do this?
0
 
LVL 23

Accepted Solution

by:
jeremyjared74 earned 500 total points
ID: 36580593
You should use the RSS feature then:
http://www.feedforall.com/rss2html/index.htm
0
 

Author Closing Comment

by:petewinter
ID: 36580740
Many thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
change password links 7 72
.php tree directory? 5 54
javascript date picker in php while loop 5 20
Checking if varaible is empty 6 25
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
The purpose of this video is to demonstrate how to Import and export files in WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Click on Too…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now