Combine Totals from while loop and foreach loop (Wordpress)

[jarvis]

Hi All,

 

I really hope someone can help. I've a custom post type called 'countries'. Each region can have sub regions. For example; Europe is made up of Uk, France, Germany etc

 

Now, the site is a job site, when you add a job, you can add it to a country/sub country.

 

What I'm trying to do is show a total number of jobs. It works apart from when you have a job in Europe and jobs listed in sub regions, France etc

 

I have 4 jobs listed.

1 in Europe

3 in France

 

It should therefore show 4 but instead, I can't get the totals to combine. I think I'm missing something obvious!! Any help is much appreciated

	<ul>
	<?php	
	#List all the countries
	$countries_query = array( 
		'post_type' => 'countries',
		'post_status' => 'publish',
		'order'    => 'ASC',
		'orderby'	=> 'name',
		'hide_empty'    => 1,
		'post_parent' => 0
	);
								
	$loop = new WP_Query( $countries_query );			
	while ( $loop->have_posts() ) : $loop->the_post();
								
		$countries_key = 'country';
		$countries_value = get_the_ID(); 
													
		$countries_sql = "SELECT count(DISTINCT pm.post_id)
		FROM $wpdb->postmeta pm
		JOIN $wpdb->posts p ON (p.ID = pm.post_id)
		WHERE pm.meta_key = '$countries_key'
		AND pm.meta_value = '$countries_value'
		AND p.post_type = 'jobs'
		AND p.post_status = 'publish'
		";
		$countries_count = $wpdb->get_var($countries_sql);	
		#echo $countries_count; #debug
	?>	
								
		<li><a href="<?php the_permalink(); ?>"><?php the_title(); ?></a>							
		
		<?php 
		#If parent country has jobs, then show the total 
		#if ($countries_count != 0) : echo '('.$countries_count.')'; endif;  
		?>									
								
		<?php
		#List all jobs linked to a country via the Country ID
		$posts = get_posts(array(
			'numberposts' => -1,
			'post_type' => 'countries',
			'post_parent' => get_the_ID()
		));
														 
		if($posts) {
			
			$total = 0;
			foreach($posts as $post) {
								
			$meta_key = 'country';
			$meta_value = get_the_ID();
													
			$sql = "SELECT count(DISTINCT pm.post_id)
			FROM $wpdb->postmeta pm
			JOIN $wpdb->posts p ON (p.ID = pm.post_id)
			WHERE pm.meta_key = '$meta_key'
			AND pm.meta_value = '$meta_value'
			AND p.post_type = 'jobs'
			AND p.post_status = 'publish'
			";
			$count = $wpdb->get_var($sql);			

			#Combine the totals of all the sub country jobs
			$thecount = $count[0];
			$total += $thecount;
													
			} #end foreach $posts						 
		$new_total = $countries_count + $total;	
		echo "($new_total)";
		
		} #endif $posts		
	
		#If parent country has jobs, then show the total 
		if ($countries_count != 0) : echo '('.$countries_count.')'; endif;  
		?>					
										
		</li>				
	<?php endwhile; ?>
	</ul>

Thanks in advanced.

[sKunKbad]

If it were me, I'd probably use a direct query to the db. Many times the wordpress way is the hard way. It seems that you are using a mixed approach. Perhaps you can change that and send each result to the console.

Edited February 10, 2014 by sKunKbad

[jarvis]

Thanks sKunKbad, I did think of that but because I need to loop round twice, thought this would be the better approach. Especially as I need to grab the ID value from the first loop and pass it to the second. Not something I thought could be done by querying the DB direct - unless I've totally missed the obvious!?