WordPress Mysql query and Duplicate

Question

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

TheDeadMedic · Accepted Answer · 2013-06-26 12:12:23Z

1

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 />';

edited Jun 26, 2013 at 12:12

answered Jun 26, 2013 at 11:54

TheDeadMedic

36.7k9 gold badges69 silver badges104 bronze badges

Yes, i understand but i wanna have no duplicates "city" value for key "Country", "UK" !

Caponi Elia
– Caponi Elia

2013-06-26 12:08:52 +00:00
Commented Jun 26, 2013 at 12:08
Enter simple PHP!

TheDeadMedic
– TheDeadMedic

2013-06-26 12:12:22 +00:00
Commented Jun 26, 2013 at 12:12
You are my new hero !! Thanks for the code that works very well !!

Caponi Elia
– Caponi Elia

2013-06-26 12:28:07 +00:00
Commented Jun 26, 2013 at 12:28
@CaponiElia If an answer worked, please vote up and accept it as the answer.

Pothi Kalimuthu
– Pothi Kalimuthu

2013-06-26 12:37:04 +00:00
Commented Jun 26, 2013 at 12:37

Add a comment |

Stack Exchange Network

WordPress Mysql query and Duplicate

1 Answer 1

Your Answer

Hot Network Questions

WordPress Mysql query and Duplicate

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions