Getting a custom query to work in Beautiful-Pro

Community Forums Forums Design Tips and Tricks Getting a custom query to work in Beautiful-Pro

This topic is: resolved

This topic contains 9 replies, has 2 voices, and was last updated by  bmiller 5 months, 3 weeks ago.

Viewing 10 posts - 1 through 10 (of 10 total)
  • Author
    Posts
  • #107857

    bmiller
    Member
    Post count: 5

    So close, but so far.

    I have a new Wp site at http://www.cookevillecameraclub.com using temporary static pages until I figure out how to do what I need. My test site is at http://www.cookevillecameraclub.com/test.

    In the test site, I have posts with currently 3 different categories (meeting-event, education, and qa). When you go to the test site, the front page has 7 posts displayed in the following order as to when they were created: (1) qa, (4) meeting-event, and (2) education.

    On the front page I only want Posts from category (id=4) “meeting-event” listed where a defined and set custom field “date” > today, and I want the posts listed in ”date” order. The codex had almost exactly what I wanted (http://codex.wordpress.org/Displaying_Posts_Using_a_Custom_Select_Query), and I tested a custom query using the procedural style (I do not know the object oriented style) in phpMyadmin.

    SELECT *
    FROM wp_posts
    LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
    LEFT JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
    LEFT JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    WHERE wp_term_taxonomy.term_id = ‘4’ AND
    wp_posts.post_status = ‘publish’ AND
    wp_posts.post_type = ‘post’ AND
    wp_postmeta.meta_key = ‘date’ AND
    wp_postmeta.meta_value > NOW()
    ORDER BY wp_postmeta.meta_value

    This worked exactly as I wanted. Returned were the three meeting-event posts that are in the future!! I was excited. . . for awhile.

    Also from the Codex “that works with the WordPress default theme” is a completed Page Template. I have substituted what I believe to be the equivalent object oriented query to the degree I am able to follow a model example.

    <?php
    /*
    Template Name: Qbased
    */
    ?>

    <?php get_header(); ?>

    <div id=”content” class=”narrowcolumn”>

    <?php

    $querystr = ”
    SELECT $wpdb->posts.*
    FROM $wpdb->posts
    LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
    LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)

    WHERE $wpdb->term_taxonomy.term_id = ‘4’ AND
    $wpdb->posts.post_status = ‘publish’ AND
    $wpdb->posts.post_type = ‘post’ AND
    $wpdb->postmeta.meta_key = ‘date’ AND
    $wpdb->postmeta.meta_value > NOW()
    ORDER BY $wpdb->postmeta.meta_value
    “;

    $pageposts = $wpdb->get_results($querystr, OBJECT);

    ?>
    <?php if ($pageposts): ?>
    <?php global $post; ?>
    <?php foreach ($pageposts as $post): ?>
    <?php setup_postdata($post); ?>

    <div class=”post” id=”post-<?php the_ID(); ?>”>
    <h2>” rel=”bookmark” title=”Permanent Link to <?php the_title_attribute(); ?>”>
    <?php the_title(); ?>
    </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”>Posted in <?php the_category(‘, ‘) ?> | <?php edit_post_link(‘Edit’, ”, ‘ | ‘); ?>
    <?php comments_popup_link(‘No Comments »’, ‘1 Comment »’, ‘% Comments »’); ?></p>
    </div>
    <?php endforeach; ?>

    <?php else : ?>
    <h2 class=”center”>Not Found</h2>
    <p class=”center”>Sorry, but you are looking for something that isn’t here.</p>
    <?php include (TEMPLATEPATH . “/searchform.php”); ?>
    <?php endif; ?>

    </div>

    <?php get_sidebar(); ?>

    <?php get_footer(); ?>

    However, I have the Genesis framework with the Beautiful Pro child theme, and I do not have the slightest idea as to the page to copy, modify and place in the
    public_html/test/wp-content/themes/beautiful-pro folder (I assume this is the way to proceed.?)

    Per the Codex, I was looking for index.php as the “home page” that would display my Posts. There is no index.php in the child theme folder. The index.php file in the genesis folder has one line: genesis();

    I see no home.php or front.php.

    Again, I feel close in terms of knowing a query to execute, but I need help in getting it properly placed.

    http://www.cookevillecameraclub.com/test
    #107861

    braddalton
    Participant
    Post count: 10593

    Please use Github Gists to paste PHP code as your code is now broken and cannot be tested. Thanks.

    On the front page I only want Posts from category (id=4) “meeting-event” listed where a defined and set custom field “date” > today, and I want the posts listed in ”date” order.

    Please shorten your question and clarify these 2 lines.


    #107872

    bmiller
    Member
    Post count: 5

    Oh boy. Followed the link to Github Gists and pasted the example Codex template page with my modified query into the open window. I created a public Gist, but besides that I have never used this tool and I’m not sure what you are requesting me to do.

    I copied from the Gist window and have re-pasted here at the end of this post.

    As to your request to shorten my question and clarify . . .

    Question: I am looking for direction as how to create a custom template using a custom query using Beautiful-Pro so that the the front page (set to display “your latest posts”) displays only posts of a designated category based on the value of a custom field.

    Additional information: I have successfully tested a query using my database and PhpMyadmin that gives me the results I want, and I have attempted to translate the query using WP template tags and object style into an example template file I found in the Codex.

    I am struggling here as I am a brand new WordPress user.

    Thank you, and I appreciate your patience.

    –bmiller

    <?php
    /*
    Template Name: Qbased
    */
    ?>

    <?php get_header(); ?>

    <div id=”content” class=”narrowcolumn”>

    <?php

    $querystr = ”
    SELECT $wpdb->posts.*
    FROM $wpdb->posts
    LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
    LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)

    WHERE $wpdb->term_taxonomy.term_id = ‘4’ AND
    $wpdb->posts.post_status = ‘publish’ AND
    $wpdb->posts.post_type = ‘post’ AND
    $wpdb->postmeta.meta_key = ‘date’ AND
    $wpdb->postmeta.meta_value > NOW()
    ORDER BY $wpdb->postmeta.meta_value
    “;

    $pageposts = $wpdb->get_results($querystr, OBJECT);

    ?>
    <?php if ($pageposts): ?>
    <?php global $post; ?>
    <?php foreach ($pageposts as $post): ?>
    <?php setup_postdata($post); ?>

    <div class=”post” id=”post-<?php the_ID(); ?>”>
    <h2>” rel=”bookmark” title=”Permanent Link to <?php the_title_attribute(); ?>”>
    <?php the_title(); ?></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”>Posted in <?php the_category(‘, ‘) ?> | <?php edit_post_link(‘Edit’, ”, ‘ | ‘); ?>
    <?php comments_popup_link(‘No Comments »’, ’1 Comment »’, ‘% Comments »’); ?></p>
    </div>
    <?php endforeach; ?>

    <?php else : ?>
    <h2 class=”center”>Not Found</h2>
    <p class=”center”>Sorry, but you are looking for something that isn’t here.</p>
    <?php include (TEMPLATEPATH . “/searchform.php”); ?>
    <?php endif; ?>

    #107873

    braddalton
    Participant
    Post count: 10593

    When you post PHP code into a webpage, it breaks so all your code is broken.

    Only paste the link to the Gist here, not the code.


    #107880

    bmiller
    Member
    Post count: 5

    Ok, thanks, braddalton.

    I hope this works as the code was copied from a web page (Codex) originally.

    #107881

    bmiller
    Member
    Post count: 5

    #107918

    braddalton
    Participant
    Post count: 10593

    Please clarify this:

    On the front page I only want Posts from category (id=4) “meeting-event” listed where a defined and set custom field “date” > today, and I want the posts listed in ”date” order.

    You only want posts displaying from 4 categories, in date order and……………….

    Please clarify the part about the custom field.


    #107934

    bmiller
    Member
    Post count: 5

    Reading Settings = Front Page Displays Your latest posts.

    1. I have created a Post category called “meeting-event”; its category ID happens to be “4”. Currently there are two other categories I set for Posts, but they are not important right now to what I need to do.
    2. I have created a custom field called “date” and I am entering values for this custom field in the form “yyyy-mm-dd” . . I only add this custom field for Posts that have the category “meeting-event”.
    3. On the Front Page, I only want to display meetings and events which are scheduled in the future, hence my query logic of selecting all published Posts where category ID = ‘4’ AND date > NOW()
    4. I wrote a MySQL query to test my selection using phpMyAdmin and it worked like a charm.
    5. I want to incorporate this custom query in a copy of the Genesis php file that creates the Front Page, but with my current understanding of Genesis and the Beautiful-Pro child theme, I don’t know which php file to work with. I have attempted to rewrite the custom query in a WordPress format in lines 15 – 26 in the code snippet above. The rest of the code came right out of the Codex as an example in creating a custom template file.
    6. I want the php file that builds the Front Page to work exactly as it does now except for the modified database selection query.

    Thank you for hanging in with me on this one.

    #107947

    braddalton
    Participant
    Post count: 10593

    You can create the custom loop in a file and then either hook it in using a conditional tag with the template tag for the loop and a genesis hook or hard code it into a front-page.php template.


    #107975

    bmiller
    Member
    Post count: 5

    Thank you — will continue to work on this.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic.