Remove Duplicate Values in List from Channel Entries

Question

I am creating a dropdown menu for a mailing list sign up form for an events company. I would like to give people the option to select a city where the company has had an event in the past or where there will be an upcoming event. To do this, I'm calling all of the event entries and displaying their cities in the dropdown. However, sometimes the company has had events multiple times in one city. How can I remove the duplicates? I have found some answers to questions similar to this where the solution uses php, however I'm having trouble getting that to work in my specific instance. Here is my code for the select dropdown menu:

<select id="MERGE1" name="MERGE1">
    <!--innitial display field is blank-->
         <option value="" selected></option>
    <!--values show in dropdown--->
         {exp:channel:entries  channel="events" status="next event|open|closed" show_future_entries="yes" show_expired="yes"}
         <option value="{event_city}">{event_city}</option>
         {/exp:channel:entries}
</select>

---

**edit

using an expression engine query solved the innitial problem. however, now i'd like to show cities that appear in 2 different fields and two different channel ID's. Here's what I'm working with so far, which is stripping out the duplicated within the channel ID's but still showing duplicates across multiple channel ID's:

<select id="MERGE1" name="MERGE1">
    <option value="" selected></option>
    {exp:query sql="SELECT exp_channel_data.field_id_1 AS event_city, exp_channel_data.field_id_202 AS event_city2 FROM exp_channel_data 
          INNER JOIN exp_channel_titles ON exp_channel_data.entry_id=exp_channel_titles.entry_id 
          WHERE ( exp_channel_titles.channel_id=1 OR exp_channel_titles.channel_id=29 ) AND (exp_channel_titles.status='next event' OR exp_channel_titles.status='open' OR exp_channel_titles.status='closed') GROUP BY exp_channel_data.field_id_1, exp_channel_data.field_id_202"}
            {if event_city != ''}
               <option value="{event_city}">{event_city}</option>
            {if:else}
               <option value="{event_city2}">{event_city2}</option>
            {/if}
     {/exp:query}
 </select>

Answer 1

You can do it with the query module. Try it:

<select id="MERGE1" name="MERGE1">
    <option value="" selected></option>
    {exp:query sql="SELECT exp_channel_data.field_id_1 AS event_city, exp_channel_data.field_id_202 AS event_city2 FROM exp_channel_data 
        INNER JOIN exp_channel_titles ON exp_channel_data.entry_id=exp_channel_titles.entry_id 
        WHERE ( exp_channel_titles.channel_id=1 OR exp_channel_titles.channel_id=29 ) AND (exp_channel_titles.status='next event' OR exp_channel_titles.status='open' OR exp_channel_titles.status='closed') GROUP BY exp_channel_data.field_id_1, exp_channel_data.field_id_202"}
    <option value="{event_city}">{event_city}</option>
    {/exp:query}
</select>

In "field_id_1", the "1" might be replaced with the field id of Event City and put correct channel_id within WHERE condition.

I can see, you are passing parameters: show_future_entries="yes" and show_expired="yes" in channel entry tag. It wouldn't be needed in SQL.

Answer 2

You could also use Stash and something like the following. You'd set variables in the first part and then output them when making the select options, using the unique="yes" parameter to filter out duplicates.

{exp:stash:set_list name="st_city_list" parse_tags="yes"}

    {exp:channel:entries  channel="events" status="next event|open|closed" show_future_entries="yes" show_expired="yes"}
        {stash:st_event_city}{event_city}{/stash:st_event_city}
    {/exp:channel:entries}

{/exp:stash:set_list}


<select id="MERGE1" name="MERGE1">

    <!--innitial display field is blank-->
         <option value="" selected></option>

    <!--values show in dropdown--->
         {exp:stash:get_list name="st_city_list" unique="yes"}
            <option value="{st_event_city}">{st_event_city}</option>
         {/exp:stash:get_list}

</select>