New to the community? Start here

Custom SQL query with custom fields

I currently have the following code that does exactly what I want it to do. It lists all of the custom post type “programs” and excludes listing the post if the custom field program_flags (multiple checkbox options for this custom field) has the option Gas checked off:

// just return them all

global $wpdb, $post;



$sql = $wpdb->prepare("SELECT ID FROM wp_posts AS p WHERE post_type=%s AND post_status='publish' AND NOT EXISTS ( SELECT post_id FROM wp_postmeta AS pm WHERE meta_key = %s AND meta_value = %s AND p.ID = pm.post_id) ORDER BY p.post_title ASC ", 'program', 'program_flags', 'Gas');



$records = $wpdb->get_results( $sql );



if(!empty($records)){

foreach( $records as $record ) {

$post = get_post( $record->ID );

$l.= "<div class='program-item'>";

$l.= "<div class='program-item-image'><a href='".get_permalink($post->ID)."'>".                 get_the_post_thumbnail($post->ID, 'thumbnail')."</a></div>";

$l.= "<div class='program-item-title'><a href='".get_permalink($post->ID)."'>".get_the_title($post->ID)."</a></div>";

$l.= "<div class='program-item-content'>".get_the_excerpt()."</div>";

$l.= "<div style='clear:both;'></div>";

$l.= "</div>";

}

}

I would like the following code updated to be set up like the code above, but also include the related school options. Please help.

<?php

// no show the results of the search



global $more;    // Declare global $more (before the loop).

$more = 0;       // Set (inside the loop) to display content above the more tag.

$l='';



query_posts(array('posts_per_page'=>'-1', 'post_type'=>'program', 'orderby'=>'title','order'=>'asc', 'meta_key'=>'related_school','meta_value'=>$post->ID ));

if ( have_posts() ) : while ( have_posts() ) : the_post();

$l.= "<div class='program-item'>";

$l.= "<div class='program-item-image'><a href='".get_permalink($post->ID)."'>". get_the_post_thumbnail($post->ID, 'thumbnail')."</a></div>";

$l.= "<div class='program-item-title'><a href='".get_permalink($post->ID)."'>".get_the_title($post->ID)."</a></div>";

$l.= "<div class='program-item-content'>".get_the_excerpt()."</div>";

$l.= "<div style='clear:both;'></div>";

$l.= "</div>";



endwhile; else:

endif;



//Reset Query

wp_reset_query();



if($l){

echo $l;

} else {

echo "<h2>Sorry, no results matched your search criteria. Please check and try again.</h2>";

}



?>

Wed, 24 Jul 2013 14:45:28
  • Paul
    • The Green Incsub

    @brandon_carson,

    Thanks for visiting my live support chat. Sorry I was not able to read through your code snippets. And thanks posting here so I can at least concentrate on the code.

    Question: In the query_posts() for the meta_key and meta_value you have some wrong values I think

    query_posts(array('posts_per_page'=>'-1', 'post_type'=>'program', 'orderby'=>'title','order'=>'asc', 'meta_key'=>'related_school','meta_value'=>$post->ID ));

    Shouldn’t meta_key = ‘program_flags’ and meta_value = ‘Gas’

    And talked to the other chat moderator who helped you last Friday. Again he is stating this cannot be done with query_posts().

  • durhamcollege
    • Flash Drive

    I need to make sure that the program flag DOES NOT EQUAL Gas AND to only return the values for the related school. Basically I need a mixture of what is in both code snippets.

    I do not need this done with a query post. What I am saying is that I want the second block of code supplied to be written like the first block of code I supplied.

    I am not sure if I am being clear enough, so if you need any additional information, please let me know.

  • durhamcollege
    • Flash Drive

    The person who did the code above seemed to have a clear understanding of that I was looking for (I probably did a much better job explaining it to him). If you are able to chat with him, or link him over to this thread, it may help.

    Thanks again for your help with this.

  • Paul
    • The Green Incsub

    @brandon_carson,

    So after reviewing this and chatting with the others. We agree this is not possible with WP_Query or query_posts. The issue is that you have multiple custom fields per post with the same key.

    So what happens is via query_posts it only checks the first key found. You could try some filtering on the posts_where clause but that also gets nasty.

    Sometimes direct SQL is the better option.

  • durhamcollege
    • Flash Drive

    I understand this :slight_smile: I DO NOT WANT TO USE QP_QUERY OR QUERY_POSTS. I am looking for help in writing the second snippet I posted like the direct SQL query (first snippet I posted). It needs to be just like the first snippet, but have the related_schools option that is in the second snippet. Is my question clear?

  • Ignacio
    • HummingBird

    Hey @brandon_carson.

    We’re going to try to build the query slower. Just take the code I gave you in Live Support and change it by this, we’ll forgot about the flasg for the moment.

    SELECT ID FROM wp_posts AS p

    INNER JOIN wp_postmeta pm ON p.ID = pm.post_id

    WHERE post_type=’program’

    AND ( pm.meta_key = ‘related_school’ AND pm.meta_value = p.ID )

    AND post_status=’publish’

    the post_type showed on the posts list should be program if any post found. Could you try?

  • Ignacio
    • HummingBird

    Hi @brandon_carson.

    Here’s the final query. I think it will work for you. I’ll leave the styles and HTML up to you:

    <?php
    

    
$results = $wpdb->get_results("SELECT p.* FROM $wpdb->posts p
    
INNER JOIN $wpdb->postmeta pm ON pm.post_id = p.ID
    
WHERE post_type='program'
    
AND post_status = 'publish'
    
AND ( pm.meta_key = 'related_school' AND pm.meta_value = p.ID )
    
AND p.ID NOT IN (
    
SELECT post_id
    
FROM wp_postmeta AS pm2
    
WHERE pm2.meta_key = 'program_flags' AND pm2.meta_value = 'Gas' AND p.ID = pm2.post_id
    
)
    
");
    

    
if ( ! empty( $results ) ) {
    
global $post;
    
$original_post = $post;
    
foreach ( $results as $post_id ) {
    
$post = $post_id;
    
setup_postdata( $post ); ?>
    

    
<li><?php the_title(); ?></li>
    
<?php
    
}
    
$post = $original_post;
    
}
    
else {
    
echo "no posts found";
    
}
    
?>