Display Data from Custom WordPress Query

  • I’ve been working on a custom query which will give me data based on a specific sort order, meaning I am only displaying results with like votes (vote_type=1)

    Here my current query:

    
        <?php
            $querystr = "SELECT postid, SUM(<code>total_votes</code>) AS tv
            FROM <code>toptr_bpvm_summary</code>,<code>toptr_posts</code>
            WHERE 1
            AND toptr_bpvm_summary.postid = toptr_posts.ID
            AND toptr_posts.post_status = 'publish'
            AND toptr_bpvm_summary.post_type = 'cars'
            AND toptr_bpvm_summary.vote_type=1
            AND toptr_bpvm_summary.vote_date BETWEEN now() - interval 1 day
            AND now()
            GROUP BY <code>postid</code>
            ORDER BY tv DESC limit 0, 3";
            $pageposts = $wpdb->get_results($querystr, OBJECT);
        ?>

    Until here all seems correct looking at a quick var_dump it gives me 3 results sorted by the highest vote numbers being 8000, 5000 and 22

    array(3) { [0]=> object(stdClass)#3147 (2) { ["postid"]=> string(3) "311" ["tv"]=> string(4) "8000" } [1]=> object(stdClass)#3149 (2) { ["postid"]=> string(3) "314" ["tv"]=> string(3) "500" } [2]=> object(stdClass)#3150 (2) { ["postid"]=> string(3) "171" ["tv"]=> string(2) "22" } }

    Here is how I am displaying the results:

           <?php if ($pageposts): ?>
        <?php global $post; ?>
        <?php foreach ($pageposts as $post): ?>
            <?php setup_postdata($post); ?>
            <div class="post" id="post-<?php the_ID(); ?>">
            <h2><a>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>">
            <?php the_title(); ?></a></h2>
            <small><?php the_time('F jS, Y') ?> <!-- by <?php the_author() ?></small>
            <div class="entry">
                <?php the_content('Read the rest of this entry ?'); ?>
            </div>
            <p class="postmetadata"> VOTE NUMBERS SHOULD BE HERE </p>
            </div>
        <?php endforeach; ?>

        <?php else : ?>
            <h2 class="center">Not Found</h2>
        <?php endif; ?>

    However I am not able to figure out HOW to display the actual numbers / values (8000, 5000 and 22 respectively). In the database itself, this data would be in the table toptr_bpvm_summary in the column total_votes

    Some expert help would be greatly appreciated, thank you.

    • This topic was modified 2 years, 6 months ago by Jan Dembowski.
Viewing 1 replies (of 1 total)

  • You are close, but not quite there with a coupel of things. Firt, when yu get $post from your query, that’s a data row, not the ID. You need ot get the values from that to make it work.

    Something like this would work better.

    foreach ($page_posts as $row) {
    $post = get_post ($row->postid);
    
   echo "<p>".$post->post_title." - '".$row->tv."' votes</p>";
}
Viewing 1 replies (of 1 total)
  • The topic ‘Display Data from Custom WordPress Query’ is closed to new replies.