SQL query for filtering by custom field
-
here is the site I’m working on:
http://speedways.postmodernsublime.com/nsd/tracks/
It’s a directory of race tracks. In the top right corner, next to the map, I want to be able to filter the listings by state. So I’m entering the tracks’ states using a custom field, then adding this to the top of my category.php file:<?php $sortbystate = $_GET['state']; //get the current cat id $cat_obj = $wp_query->get_queried_object(); $cat_id = $cat_obj->cat_ID; $states = $wpdb->get_col("SELECT meta_value FROM $wpdb->postmeta LEFT JOIN $wpdb->posts ON($wpdb->postmeta.post_id = $wpdb->posts.ID) LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id) LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) WHERE $wpdb->term_taxonomy.term_id = $cat_id AND $wpdb->term_taxonomy.taxonomy = 'category' AND $wpdb->postmeta.meta_key = 'state' ORDER BY $wpdb->postmeta.meta_value ASC "); ?>And for the links to click to actually filter the posts:
<ul class="track-filters"> <?php foreach ($surfaces as $surface) { echo '<li><a href="?surface='.$surface.'">'.$surface.'</a></li>';}?> </ul>The problem is, when I have more than one track with the same state, the SQL query is outputting each instance of that custom field. That’s why there are 2 Alabamas right now.
Can anyone tell me how to modify my query so that it will only display one result for each state?
Thanks very much.
The topic ‘SQL query for filtering by custom field’ is closed to new replies.