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") ?>
<?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>
<?php endforeach ?>

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?


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.

| 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
) );

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

| 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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