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've a problem with mysql query on my wordpress database.

I've custom fields Country and City.

At moment I have this query that show all the cities of "UK" Country

$querystr = "
SELECT wposts.* 
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.ID = wpostmeta.post_id 
AND wpostmeta.meta_key  = 'Country' 
AND wpostmeta.meta_value  = 'UK' 
AND wposts.post_type = 'post' 
ORDER BY wpostmeta.meta_value DESC
";
$pageposts = $wpdb->get_results($querystr, OBJECT);

This is the query with php foreach that show all the cities

  <?php

 $querystr = "
 SELECT wposts.* 
 FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
 WHERE wposts.ID = wpostmeta.post_id 
 AND wpostmeta.meta_key  = 'Country' 
 AND wpostmeta.meta_value  = 'UK' 
 AND wposts.post_type = 'post' 
 ";

 $pageposts = $wpdb->get_results($querystr, OBJECT);

 ?>
 <?php if ($pageposts): ?>

  <?php global $post; ?>
  <?php foreach ($pageposts as $post): ?>
    <?php setup_postdata($post); ?>

    <?php echo get_post_meta($post->ID, 'city', true) ?> <br />

  <?php endforeach; ?>

 <?php endif; ?>

This query listed all the UK City grabs the data from my WordPress Custom Field "City".

This query returns a list of Cities like:

Aberdeen
Aberdeen
Aberdeen
Aberdeen
Aberdeen
Belfast
Belfast
Belfast
Belfast
Belfast
Birchington
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Birmingham
Blackpool
Bournemouth
Bournemouth
Bournemouth
Bournemouth
Bournemouth
Bournemouth
Bournemouth
Bournemouth
Brighton
    Brighton

etc.!!!

as you can see there are duplicates of "city" field value, I need to show once the city and get a list like

      Aberdeen
      Belfast
      Birchington
      Birmingham
      Blackpool
      Bournemouth
      Brighton
      Bristol

How can I get this? Thnks to everyone !!

share|improve this question

1 Answer 1

up vote 1 down vote accepted

There's no need for custom SQL here, use the power of meta queries:

$posts = get_posts(
    array(
        'posts_per_page' => -1,
        'meta_query'     => array(
            array(
                'key'   => 'Country',
                'value' => 'UK',
            ),
        ),
    )
);

foreach ( $posts as $post )
    $cities[] = get_post_meta( $post->ID, 'city', true );

foreach ( array_unique( $cities ) as $city )
    echo $city . '<br />';
share|improve this answer
    
Yes, i understand but i wanna have no duplicates "city" value for key "Country", "UK" ! –  Caponi Elia Jun 26 '13 at 12:08
    
Enter simple PHP! –  TheDeadMedic Jun 26 '13 at 12:12
    
You are my new hero !! Thanks for the code that works very well !! –  Caponi Elia Jun 26 '13 at 12:28
    
@CaponiElia If an answer worked, please vote up and accept it as the answer. –  Pothi Jun 26 '13 at 12:37

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.