up vote -1 down vote favorite
1
share [g+] share [fb]

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>";
}
?>
link|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
feedback

1 Answer

up vote 0 down vote

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...

link|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
feedback

Your Answer

 
or
required, but never shown
discard

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