Custom Query Issue

  • Resolved mikejandreau

    (@mikejandreau)


    15 years, 7 months ago

    I’m trying to move forward with moving a bunch of data from outside WordPress into WordPress, and running into some troubles.

    I’m trying to use the instructions here, and having some issues. Everything I’ve tried, comes back with no results.

    The code below, based on what I’m reading, should give me an outputted list of everything post/page that has a custom field like %test%, which exists in my dev database, but I get no results back.

    My WordPress install has table prefixes of wordpress_, so I modified the code in the Codex, but still get nothing.

    <?php

 $querystr = "
    SELECT wordpress_posts.*
    FROM $wpdb->posts posts, $wpdb->postmeta postmeta
    WHERE wordpress_posts.ID = wordpress_postmeta.post_id
    AND wordpress_postmeta.meta_key = 'tags'
    AND wordpress_postmeta.meta_value LIKE '%test%'
    AND wordpress_posts.post_status = 'publish'
    AND wordpress_posts.post_type = 'post'
    ORDER BY wordpress_posts.post_date DESC
 ";

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

 ?>

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

 <div class="post" id="post-<?php the_ID(); ?>">
 <h2><a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title(); ?>">
    <?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">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 endif; ?>

    Also, would it be possible to do the custom query based off of the page title? ie; AND wordpress_postmeta.meta_value LIKE ‘%whatever my page title is%’ ?

    Thanks for any help anyone can offer.

Viewing 5 replies - 1 through 5 (of 5 total)

  • Hi

    What I see is
    1) you are only including posts, not pages (you referred to posts and pages) [ AND wordpress_posts.post_type = ‘post’ ]

    2) you are only including custom fields whose KEY value is ‘tags’
    (You did not refer to that in the query specs you described)
    [ AND wordpress_postmeta.meta_key = ‘tags’ ]

    does this help, or am i missing something?

    I haven’t run your query but it looks like if there is more than one occurrence of a custom field containing ‘test’ within its value, the post is going to be included once for each occurrence. I believe changing it to SELECT DISTINCT wordpress_posts.* …..
    takes care of that possibility, only returning a post once.

    Thread Starter mikejandreau

    (@mikejandreau)

    Ugh. It’s always something so simple.

    post_type = post changed to post_type = page, and

    FROM $wpdb->posts posts, $wpdb->postmeta postmeta changed to
    `FROM $wpdb->posts wordpress_posts, $wpdb->postmeta wordpress_postmeta’ and voila.

    It’s still showing the posts twice though, even with SELECT DISTINCT, any suggestions there?

    Thread Starter mikejandreau

    (@mikejandreau)

    Nevermind, fixed the duplicate.

    Now, onto the other part I can’t figure out.

    How can I change AND wordpress_postmeta.meta_value LIKE '%test%'

    To reflect the current page title of the page someone’s viewing and exclude the current page they’re on from showing in the results?

    Thanks, again.

    Thread Starter mikejandreau

    (@mikejandreau)

    Nevermind, I got it.

    I just had to define what I wanted to use as the meta_value as a variable

    $variable = get_post_meta($post->ID, 'info', $single = true);

    I also used the same code to exclude the current page.

    $excluded = $post->ID;

    <?php

 $querystr = "
    SELECT DISTINCT wordpress_posts.*
    FROM $wpdb->posts wordpress_posts, $wpdb->postmeta wordpress_postmeta
    WHERE wordpress_posts.ID = wordpress_postmeta.post_id
    AND wordpress_postmeta.meta_key = 'tags'
    AND wordpress_postmeta.meta_value LIKE '%$variable%'
    AND wordpress_posts.post_status = 'publish'
    AND wordpress_posts.post_type = 'page'
	AND wordpress_posts.ID !='$excluded'
    ORDER BY wordpress_posts.post_date DESC
 ";

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

 ?>

    And voila, it works! Thanks again for pointing me in the right direction.

    Hi

    Its almost always something that’s right under our noses, which is the advantage of 4 eyes over 2.

    Great, on your getting it working.

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Custom Query Issue’ is closed to new replies.