Thread

Hello, newer to using the query module so I apologize if this seems like a rather bland question to all of you EE gurus out there.

I have a need to create a select list of locations from a custom field. Normally we would simple create a weblog and call those custom fields. Normally we would use:

<select name="business_location">
                    <option value="">Select</option>
                    {exp:weblog:entries weblog="business_listings" category="13|14|15|40" dynamic="off"}
                    <option value="{business_location}">{business_location}</option>
                    {/exp:weblog:entries}
                </select>

The bummer deal is using the above gave us the lovely issue of duplicate location entries.

So in doing our due diligence we searched the highest EE mountains and the lowest EE valleys and we figured out that simple solution was to create a custom query using the query module, and we come to this:

{exp:query sql='SELECT DISTINCT field_id_23 AS location FROM exp_weblog_data WHERE field_id_35 <> ""'}

Yay, that worked perfectly with the one exception, the query selects ALL fields from all categories so we need to limit the results to a category or categories.  This would normally take a “join” to make work.

I cannot for the life me me figure out the proper join and am need of some assistance. Our category IDs are 4|13|14|15|40

A HUGE Thank You is in order for anyone who can help us solve this mystery. I would also throw in a $10 Gift Card to your favorite shopping place of choice because I know how it feels to go unappreciated in our lonely world of development.

Oh I forgot to mention, we are using EE 1.7.1.

Not sure about modifying your query (which would be the “correct” way of doing things), but, if time is of the essence, a PHP hack might be in order.

This will store each location that is displayed in an array.  After the first location is displayed, it will check the array of previously displayed locations to see if the current location has been displayed already or not.  If it hasn’t been, it’ll display it; if not, it’ll get skipped over.

<?php $aDisplayedLocations = array(); ?>

<select name="business_location">
    <option value="">Select</option>
    {exp:weblog:entries weblog="business_listings" category="13|14|15|40" dynamic="off"}
        <?php 
            if(count($aDisplayedLocations) == 0){
                 ?><option value="{business_location}">{business_location}</option><?
                $aDisplayedLocations[] = "{business_location}"; 
            }else{
                if(!in_array("{business_location}", $aDisplayedLocations)){
                    ?><option value="{business_location}">{business_location}</option><?
                    $aDisplayedLocations[] = "{business_location}"; 
                }
            }
        ?>
    {/exp:weblog:entries}
</select>

Minor correction made: count($aDisplayedLocations)

That seemed to do the trick for a quick fix. Thank you for the insight.

I would still like to know how to properly join the category IDs using the query, if anyone could assist.