Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I'm using this code to generate all my custom field values from all posts for custom field named "autor".

<?php $autor = $wpdb->get_results("SELECT meta_value AS autor FROM wp_posts, wp_postmeta WHERE post_status = 'publish' AND meta_key = 'autor' GROUP BY meta_value ORDER BY meta_value") ?>
<ol>
<?php foreach( $autor as $autori ) : ?>
<div class="autor">
<li><a href="http://simplynote.me/?s=<?php echo $autori->autor ?>"><?php echo $autori->autor ?></a></li>
</div>
<?php endforeach ?>
</ol>

It works okey, but the problem is that it takes a couple of seconds more (like, the page is loading for 5-10 seconds) for server to load that particular page. Is there any way to optimize this request?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

Your query is incorrect in the first place because you're not doing the join correctly. What you're actually selecting with that query is just all the postmeta's autor fields, without regards to whether the post is published or not.

Here's the corrected query.

SELECT meta_value AS autor FROM wp_posts 
JOIN wp_postmeta ON (wp_postmeta.post_id =  wp_posts.ID) 
WHERE post_status = 'publish' 
    AND meta_key = 'autor' 
GROUP BY meta_value 
ORDER BY meta_value

This will probably be faster because the proper join will eliminate a lot of the posts. It's not fully optimal, but it will give some improvement.

share|improve this answer

There're are some things to consider:

1st Prepare your query and use the proper prefix.

This prevents most tries to inject something into your DB.

Every built in table can get called simply by using $wpdb->tablename. In your case that would be $wpdb->posts. As you might know, there's the possibility to add a global named $table_prefix to your wp-config.php-file. So in case you got a custom table, you should use {$wpdb->prefix}table_name.

Then use prepare() - like you can read in nearly every Q/A over here. The function works similar to printf/sprintf() in "plain" PHP and uses %s to replace strings and %d to replace digits (int).

$results = $wpdb->get_result( $wpdb->prepare(
    "
        SELECT meta_value AS autor 
        FROM $wpdb->posts, $wpdb->postmeta 
        WHERE post_status = 'publish' 
        AND meta_key = '%s' 
        GROUP BY meta_value 
        ORDER BY meta_value
        DESC
    "
    ,'autor'
) );

2nd Use the proper internal API

On the one hand there's something called (read into this archive).

You can use filters like pre_get_posts to $query->set( 'key', 'value' ) pairs for your main query and other filters like posts_clauses or the "smaller" posts_orderby to intercept the query string.

The last filter you can use to check on the query is the post_results filter do to some manipulation/parsing to the resulting data.

You can read more about it in this answer.

3rd Don't query if you got a template tag to do the job for you

On the other hand there're functions like

share|improve this answer
    
There's no point to using prepare if "autor" is a fixed string. Prepare only makes sense when you're passing unsanitized input to the query. Also, if you do use prepare, you don't put %s in single quote marks in the query. –  Otto Dec 17 '12 at 15:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.