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 have 294 queries on my wordpress site. I checked using sql monitorand the maximum queries are to display count of a cities list custom field. Lets say there are 200 cities then 200 queries are happening in a loop to count the number of posts about a city. Is a there a way to do this more efficiently.

The code I have used to get counts of countries is

<?php 
global $wpdb;
$countries = tgt_get_countries();
foreach ($countries as $country) {
$testcount = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->postmeta WHERE meta_value = '$country' AND meta_key = 'tgt_job_country'");
if ($testcount !=0)
echo "<li><a href='http://dsdjjhfgd.net/s=jobseeker&usertype=jobseeker&country=".$country."&search=Search'>".$country."(".$testcount.")</a></li>";
}
?>
share|improve this question
    
Also posted here, where it was better suited. This is a localized issue and i'm voting to close on those grounds. –  t31os Dec 21 '11 at 21:33
    
The question has changed very much. Pls have a look properly before you downvote –  holyidiot Dec 21 '11 at 22:53
    
This question feels very localized, do you feel otherwise, if so please make your case(i'm always willing to change my votes). –  t31os Dec 21 '11 at 23:10
    
The question is very generic in nature and defines how to populate a list from custom fields and get a count of the posts based on the list. The approach count come handy with any field or data related to custom fields –  holyidiot Dec 26 '11 at 17:55

1 Answer 1

This query will get all the countries and counts in one query:

global $wpdb;
$countries_count = $wpdb->get_results( "
    SELECT meta_value AS country, COUNT(post_id) AS count
    FROM {$wpdb->postmeta} 
    WHERE  meta_key = 'tgt_job_country'
    GROUP BY country ORDER BY country" );
foreach ($countries_count as $country)
        echo "<li><a href='http://dsdjjhfgd.net/s=jobseeker&usertype=jobseeker&country=".$country->country."&search=Search'>".$country->country."(".$country->count.")</a></li>";

Unless you need to get the country list first - this will just pull every value with that meta_key in the database...

share|improve this answer
    
Hi, I need to generate the country list first. How would I do it then –  holyidiot Dec 21 '11 at 22:46
    
Actually I would still do it this way. You could add a where clause to the SQL query checking if the country is in the list you have pulled, but escaping that data would be a pain and make your query unecessarily complicated. Just check on displaying the list whether each country is in the array of countries you want to display. –  goldenapples Dec 22 '11 at 14:42

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.