wordpress query trouble

2 Views

EDIT:

heres my file thats running the query maby its something else acting up – http://pastebin.com/4Cw3xMEM

this works as a direct query in SQLyog

Read More
SELECT * FROM wp_posts
WHERE post_status = 'publish' 
AND post_type = 'post'
AND YEAR(post_date)=2010
ORDER BY post_date DESC;

it returns the three posts which are from 2010
but when i try run it via my wordpress it pumps out all the posts, regardless of date

i know theres a few things to be done so it selects the correct tables
and as far as i can gather, the following ‘should’ be alright 

SELECT wposts.* 
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.post_status = 'publish' 
AND wposts.post_type = 'post'
AND YEAR(wposts.post_date)=2010
ORDER BY wposts.post_date DESC;

i really dont understand the ‘ wposts. ‘ at all, but it uses this sort of thing in the examples on wordpress codex

the ultimate goal is to make a query to pull posts froma certain year, and use that to make WP display groups of posts per year

i cant find anything that helps explain how to properly call the querys using the wordpress structure

if someone can explain a few of these things it might help alot, example,
with the following: 

$wpdb->posts

i have no idea what ‘->’ is for, i have only seen it used in php for assigning parts of an array in a foreach

  • how do i get this thing to work?
Post Views: 2

Related posts

Leave a Reply

4 comments

  1. You are making this far too hard on yourself as I know all too well. When I started with WordPress I came to it as an advanced SQL developer so I wanted to code everything in SQL. Turns out though that you almost never need to use SQL with WordPress because it has it’s own API with built-in URL-like query language complete with a caching system (the caching system means you don’t need to optimize nearly as much as you might otherwise thinK.)

    Here’s a standalone file you can drop in the root of your website (call it test.php maybe?) so that you can test this out, fiddle with options and see it work:

    <?php

include "wp-load.php";

$year = 2010;

$query = new WP_Query("post_type=any&posts_per_page=-1&year={$year}");
echo "<h1>Year: {$year}</h1>";
echo '<ul>';
foreach($query->posts as $post) {
    echo "<li>{$post->post_title} [post_type={$post->post_type}]</li>";
}
echo '</ul>';

    The query language I mentioned, extracted from above, looks like this:

    post_type=any&posts_per_page=-1&year=2010

    You could also query in an array form, for example (it really just depends on your use-case and/or your preference):

    $query = new WP_Query(array(
    'post_type' => 'any',
    'posts_per_page' => -1,
    'year' => $year,
));

    There are a lot more query parameters for WP_Query() than the three I used above. You can find the best documentation for those parameters here:

    Note that WP_Query() is just one way to query in WordPress. There’s also query_posts(), get_posts() and you can interact with the query during hooks as well.

    Yes, there are times when you need to drop to SQL but it’s better in WordPress, especially for backward compatibility if you avoid SQL and use the APIs whenever possible.

    Hope this helped.

    -Mike

    P.S. Want more info on the WordPress query? Let me suggest you ask questions over at WordPress Answers when it opens to full public beta in a week (WordPress Answers is from StackExchange, the same folks as StackOverflow.) WordPress Answers is more likely to get you better answers for WordPress than here because that site will be filled with WordPress experts as opposed the generalists (and otherwise extremely knowledgeable folks) here.

  2. If later format works for you, use that instead. As for:

    $wpdb->posts

    The -> is used with an object’s properties and methods. In above case, $wpdb is an object which must have been created with new keyword by wordpress somewhere and posts is a property of $wpdb object that gets you all the post from the database.

    Whether a class member is method or property is recognized with trailing brackets (). For example:

    $wpdb->posts    // property
$wpdb->post()   // method

    You may want to have a look at PHP OOP Basics:

  3. The $wpdb is a global object used in WordPress to contain things relating to the WordPress DataBase. The ->posts part means to fetch the ‘posts’ member of that object (or in other words a public variable). I can only assume it’s the way you are calling the query that is causing the problem. You should be using something along the lines of…

    $querystr = "SELECT wposts.* 
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.post_status = 'publish' 
AND wposts.post_type = 'post'
AND YEAR(wposts.post_date)=2010
ORDER BY wposts.post_date DESC";
$results = $wpdb->get_results($querystr, OBJECT);

foreach ($results as $r) {
    echo $r->title; // echo out variables from table
}

  4. $wpdb->posts

    this means $wpdb is an object and you try to acces the posts properties of the object.

    I don’t know anything about wordpress, but, if you want to use this syntax in a string (delimited by “), you should use

    ${wpdb->posts}

    so try 

    $query = "SELECT wposts.* 
          FROM ${wpdb->posts} wposts, ${wpdb->postmeta} wpostmeta
          WHERE wposts.post_status = 'publish' 
          AND wposts.post_type = 'post'
          AND YEAR(wposts.post_date)=2010
          ORDER BY wposts.post_date DESC;";